Locate is slow
Posted: Sun 05 Dec 2010 13:45
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
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