Another way to search the database (without using SELECT)

[Jiří Huňáček]

Hi,

I have a database and a table in it (about 120,000 rows - the entire product database from another application). My goal is to immediately display the relevant rows when entering (e.g. material number).

I am currently using the SELECT clause, which works well for me and selects the given data so that when I enter "3" in the edit box, all rows where the material begins with "3" are selected. Then I enter a second number (e.g. "0") and the data that begins with "30" is selected. And so on.

Code: Pascal  [Select][+][-]

1. procedure TfrmOverwiev.editSearchChange(Sender: TObject);
2. var
3.   sqlText: String;
4. begin
5.   sqlText := 'select * from mat where (';
6.   if rbMaterial.Checked then sqlText := sqlText + 'material like ''';
7.   if rbDescription.Checked then sqlText := sqlText + 'description like ''%';
8.         sqlText := sqlText + editSearch.Text + '%'')';
9.  
10.   // The sqlText variable contains, for example -- select * from mat where material like '30%'
11.  
12.   Test_Data.SQLQuery1.Active := False;
13.   Test_Data.SQLQuery1.SQL.Text := sqlText;
14.   Test_Data.SQLQuery1.ExecSQL;
15.   Test_Data.SQLQuery1.Active := True;
16.  
17.   editSearch.SetFocus;
18. end;

However, I am looking for a way where the entire database remains displayed in the grid and when a number is entered, only the cursor in the table moves to the position of the first occurrence. This can be achieved by executing a loop, but with this many rows it is very tedious.

Code: Pascal  [Select][+][-]

1. procedure TfrmOverwiev.editSearchChange(Sender: TObject);
2. begin
3.   with Test_Data.SQLQuery1 do
4.   begin
5.     First;
6.     while not EOF do
7.     begin
8.         if Copy(FieldByName('material').AsString,1,Length(Trim(editSearch.Text))) = Trim(editSearch.Text) then Exit;
9.       Next;
10.     end;
11.   end;
12.  
13.   editSearch.SetFocus;
14. end;

Is there any way to speed it up?

Addendum:
We used to have an application from a former company owner where this worked, but we were a smaller company back then and I'm not sure if there was that much data in the database back then. It's possible we had less, maybe around 30k active rows.

Due to the larger zip size I'm attaching a link to Google Drive
https://drive.google.com/file/d/1zCMPcnZcAXyCAdNT8Wiyu6H0kA5VfS66/view?usp=sharing

[dseligo]

You have Locate method (https://www.freepascal.org/docs-html/fcl/db/tdataset.locate.html).

[Handoko]

I haven't done it on databases, but I managed to write my own listbox component that can do live search.

The performance is good, you will start to feel the sluggishness when it reaches 300k of items. But still acceptable until 500k, tested on old Core 2 Duo machines. The key for the optimization is, you need to know: string operations are expensive. My component internally uses 2 list containers for caching the search result. These containers are dynamic array of longint. There is no string moving, copying or deleting. All those operations are performed on the list containers, which actually are the index that points to the 'real' string list. You can see demo on the attached video.

Now back to your question. In your case, how to speed it up.

Load the primary key and the searchable fields in to memory. Don't reload the data from database every time user doing keypress, but perform those operations on computer's memory. Nowadays a 8 GB DDR costs less than $3. And avoid copying, moving, deleting nor trimming strings because string operation are expensive.

[Wesbat]

What Handoko said, using an in-memory index is the way to go.

In addition I propose that you sort the index alphabetically so that you may implement a binary search algorithm instead of iterating for each item. This will reduce the computation considerably.

Of course you need to tweak the search matching instead of equality test, to only compare N-characters (where N is the length of the search term).

Attached is a demo of such a binary search. You can play with the

Code: Pascal  [Select][+][-]

1. const
2.   NumberOfRecords: integer = 10000;

- try increasing it to 100,000 or more to test performance of the search. I wrote a sorting routine for demo purposes, but you can let the SQL engine handle the sorting for you.

[Sieben]

What Handoko said, using an in-memory index is the way to go.

I beg to differ. If this is what TS wants to achieve:

However, I am looking for a way where the entire database remains displayed in the grid and when a number is entered, only the cursor in the table moves to the position of the first occurrence.

then Locate() as suggested by dseligo does exacty that. I would, however, further suggest to first sort the table on the column in question by using IndexFieldNames. This not only speeds things up by creating an in-memory index directly on the dataset without any additonal coding but also provides the appropriate order of entries the user would expect.

[rvk]

However, I am looking for a way where the entire database remains displayed in the grid and when a number is entered, only the cursor in the table moves to the position of the first occurrence. This can be achieved by executing a loop, but with this many rows it is very tedious.

The while/next method is correct. You can also use Locate but that does the same thing.

The problem however, is the screen handling. You don't disable screen updates so every Next will need to be displayed and repainted.

You need to wrap the while/next loop in a TDataset.DisableControls/EnableControls.

Something like this. That will speed things up.

Code: Pascal  [Select][+][-]

1.   DBGrid1.DataSource.DataSet.DisableControls;
2.   try
3.     // do your thing, i.e. loop
4.   finally
5.     DBGrid1.DataSource.DataSet.EnableControls;
6.   end;

(You can also do this directory on Test_Data of course)

[Sieben]

You can also use Locate but that does the same thing.

Locate uses the internal structures of the dataset much more efficiently. Apart from that it's funny how people are seemingly trying to prevent TS from using the simple single line solution that is right there at his fingertipps.

[silvercoder70]

+1 to using Locate(...)

See https://wiki.freepascal.org/locate

[Zvoni]

For locate to work properly, he would have to load all records into his Grid (or somewhere else into memory)
a 120K records in the Grid/Memory to search with locate?

Hell no!

Leave it with the SELECT, because that works with initially displaying only a partial amount of records

EDIT: Another option might be "Filter"-property

EDIT2: Just saw it: His two "rbXXXX"-Controls seem to be Checkboxes.
That will blow up, if a user checks both, since there is an "AND" (or an "OR") missing.

The SELECT-Statement itself looks pretty vulnerable.
Would need more details to advise better

NEXT: He builds his SELECT-Statement, but then uses ExecSQL *sigh*... which in that case does nothing.
Setting Active to True is what actually executes the Statement, and then populates the Dataset

I really, really hate those TSQLxxx-Controls....

[silvercoder70]

@Zvoni,

Agree with most of what you said ... though in the initial post I thought he wanted every row displayed. Whether that is smart or not.  I also would not do "select *". Instead, I would specify what columns to return.

I would also hope that a locate() would be faster than the equivalent of a table scan!

As for the busted SQL statement and the where clauses if both checkboxes ticked .... no comment.

[Zvoni]

I also would not do "select *". Instead, I would specify what columns to return.

Eh? Didn't even pay attention to that one.
Agreed.

As i said: Locate only makes sense with the full load of all records.
Now let's introduce TS to "PacketRecords".....

If TS really has all records "somewhere", then i'd rather go with "Filter"-Property.
Think about the consequences of "locating" a Record, which is 100K records down from the top

[Thaddy]

65 years of SQl and there are still, well, real idiots.
Improve Select plz.,
You obviously know nothing. Select is not bad. It is simply idiom. You can't blame 'select' if you do not have a clue how it works..

[silvercoder70]

I thought SQL started in the 1970s?!

That aside....  In my experience a user would not want to look at say 100,000 records in a grid. They might be interested in a subset of records based on a date/time range, re-running a query with some sort of filters (where clause?) in place, can work quite well if the appropriate fields are indexed.

Attempting to display 100,000 records at once can be time consuming also.

I also looked at the original code and appears to be radio button based on the component/variable names.

But then suppose you get past that point, the next query to run would be to run a query that returned the list of keys based on the input criteria, and use that result set to go to prior/next records in the grid based on that query.

Lastly, noticed the program was using Exec vs Open on the query.