Locate is slow

[sandy771]

I have an sqlite table with about 750,000 records with a number of sorted columns. Ref is the primary key and all of the columns (col1 – to col3) are indexed

I can perform :
“select ref, col1, col2, col3 from table order by col1”
and the select query returns in about 0.5 of a second.

If I do
Table->Locate("Ref",Ref, TLocateOptions());
The call returns in about 3 minutes or more.

Any ideas why this may be, or what I can do to optimise it?

Essentially what i want to do is sort the database on a given column but then place the cursor back on the original row, i.e am doing:

temp = ref;
select ref, col1, col2, col3 from table order by col1
Table->Locate("Ref",temp, TLocateOptions());

this takes usually 3 plus seconds

[bork]

Hello

You should understand the difference between operations on the server side and on the client side. If you execute the following query:

Code: Select all

  select * from table where col1 = :value

in this case database uses index to find this record. But if you use the Locate method then ALL data from your query will be loaded to client (750,000 records), and index cannot accelerate data loading. When all data is loaded, then client searches the required record in the loaded data.

The filter behaves the same way when filtering is performed on the client and on the server side. If you use filter in the SQL query like:

Code: Select all

  select * from table where name = :name

then server returns only records that correspond to the condition. If you use the DataSet.Filter property, then ALL records will be loaded to the client side, and after this data will be filtered.

All these methods are applicable to any database: SQLite, Oracle, MS SQL Server, etc. If you want to get the best performance, you should write SQL queries that return only required data.