How to deal with indexes in TSQLQuery?

[Vodnik]

Hello,
I'm fighting the problem in my application that arise after Lazarus upgrade from 2.0.4 to 2.0.10.
I'm using this code to sort TSQLQuery with indexes:

https://forum.lazarus.freepascal.org/index.php/topic,20142.0.html
Sorting tsqlquery with indexes working example(Yes it works now)

I set SQLQuery1.SQL.text, then call SQLQuery1.Open.
When data appears in DBGrid1, I apply sorting.

In Lazarus 2.0.4 it was possible to close SQLQuery1, set new SQL.text and open it again - data in DBGrid1 appeared already sorted.

In Lazarus 2.0.10 second attempt to open SQLQuery1 causes error: "DBQuery1: Index 'ASC_callid' not found".

I didn't find any description about TSQLQuery indexes, so I'm doing blind troubleshooting 
I see, that DBQuery1.IndexName still equals 'ASC_callid' after DBQuery1 is closed.
I have tried to call DBquery1.ClearIndexes or DBQuery1.IndexDefs.Clear after closing DBQuery1,
but next open still produce the same error.

Please share an idea how to deal with indexes in this case.

[rvk]

I set SQLQuery1.SQL.text, then call SQLQuery1.Open.
When data appears in DBGrid1, I apply sorting.

You should first determine on what you want to sort and then open the table.

In Lazarus 2.0.4 it was possible to close SQLQuery1, set new SQL.text and open it again - data in DBGrid1 appeared already sorted.

That should still be possible.
What SQL query are you using?
What database are you using?

You mentioned a topic. But that topic is only for memory databases. For real databases the index should be create during database creation (or afterwards). But it's not possible to just come up with a indexname and use it.

You can use
SELECT * FROM TABLE ORDER BY FIELD
Without a index, the database will automatically sort on FIELD but it will be slow because there isn't an index
But if there is an index the database will automatically use that index.

So with physical databases you don't need to worry about which index to use (it will be taken automatically) and if the query is slow you need to add real indexes (with CREATE INDEX command).

[Vodnik]

Well, I saw discussions here concerning which sorting method to prefer:
with DB engine, applying ORDER BY in SQL command (server side)
or with indexes in TSQLQuery, which works in memory (client side).
It was also mentioned here in the forum, that TSQLQuery indexes are completely different to the indexes that exist within DB.
In my application I selected second approach, because user may quickly do numerous sorting during his session, clicking on different column headers in DBGrid.
I work with Informix DB via ODBC driver.
All necessary indexes are already created in the DB and I have no right to modify the structure.
My SQL query looks like SELECT FIELD1, FIELD2, ... FIELD3 FROM TABLE1 - without ORDER BY.
But you are right: I should create a very simple working example, which should demonstrate the issue.

[rvk]

with DB engine, applying ORDER BY in SQL command (server side)
or with indexes in TSQLQuery, which works in memory (client side).

I don't think the second method will work with physical databases.
Lazarus can't hold millions of records in memory, just for a table in a physical database table.
For memory databases the data is already in memory and that won't be a problem.

All necessary indexes are already created in the DB and I have no right to modify the structure.
My SQL query looks like SELECT FIELD1, FIELD2, ... FIELD3 FROM TABLE1 - without ORDER BY.

If all indexes are already create you don't have to do anything with indexes. They will be automatic.

Just do a ORDER BY 1 (or 2, 3 etc) or ORDER BY FIELD1, FIELD2 etc. on which column you want to sort.

I thought you could look at the PLAN to see if the index is really used.
But I can't find the correct way to show the PLAN in FPC for ODBC.

Anyway... if the index doesn't exist on that field, the database will just sort it anyway, in memory (of the database server itself).

[Vodnik]

rvk, here is the discussion of the indexes, that gives me some understanding: https://forum.lazarus.freepascal.org/index.php/topic,52166.0.html
Citation from ludob:

You can do this also by adding indices to TSQLQuery without re-querying the database.
Suppose the fields you want to sort on are called 'name' and 'number'. Before opening the query, add the following lines:
Code: [Select]
  SQLQuery1.AddIndex('name','name',[]);
  SQLQuery1.AddIndex('number','number',[]);
  SQLQuery1.Active:=true;
The 3rd parameter is a set of TIndexOption. Useful values in this context are ixDescending and ixCaseInsensitive.
When the query is active, changing the indexname will change the order of the data displayed in the dbgrid:
Code: [Select]
  SQLQuery1.IndexName:='name';
Note that the index of the table is not used at all. AddIndex is only an in memory sorting index and unrelated to the table index.
There are also a few problems when using index and adding data (http://bugs.freepascal.org/view.php?id=20514). When you only want to display data it works fine though.

So I'm using this method. I have tried to load 0,7 million records into DBGrid, this took about 1 minute. Sorting it in memory takes about 20 seconds. So I'm 100% happy with it. Only looking how to make it working again in Lazarus 2.0.10

[rvk]

Why would you want to wait 20 seconds while closing, ORDER BY and opening the table would be almost instant? (And location and dbgrid state can be restored)

I don't know why SQLQuery1.AddIndex doesn't work. I've never used it other then on in-memory db. But you risk getting out of memory on really big tables and it takes (a long) time while there is already an index present in the database (which is instant). All records need to be read for addindex. Really (really) not efficient.

[Vodnik]

Well, my problem is solved in https://forum.lazarus.freepascal.org/index.php/topic,20315.0.html
The idea is to clear indexes on closed dataset.