sqlite - SQLQuery.locate is too slow
lazarus / 16-Databases

----------------------------------------------------------------------
bambamns · 2013-02-10 16:05

Hi ,

If I use sqlquery.locate, it is few hundred times slower then the sql
select statment.
Table fields I use to locate are primary indexes.

So, if I use :
Code: [Select]if DM.RabatiQ.Locate('komitent;poslovnica;brend',VarArra
yOf([DM.PoslovniceQ.FieldByName('komitent').AsString,
                                                           
 DM.PoslovniceQ.FieldByName('poslovnica').AsString,
                                                           
 DM.ArtikliQ.FieldByName('brend').AsInteger]),[]) then
      begin
       Result := DM.RabatiQ.FieldByName('rabat').AsFloat;
      end;                                                           
                                                
it takes over 5 sec to do it.

If I use :
Code: [Select]With DM.SQLQ do
     begin
      close;
      sql.Text := 'select rabat from rabati';
      sql.Add('where komitent =
'+QuotedStr(DM.PoslovniceQ.FieldByName('komitent').AsString));
      sql.Add('and poslovnica =
'+QuotedStr(DM.PoslovniceQ.FieldByName('poslovnica').AsString));
      sql.Add('and brend =
'+DM.ArtikliQ.FieldByName('brend').AsString);
      open;                   
      Result := FieldByName('rabat').AsFloat;
      close;                                
    end;
it takes 0.25 sec to be done with same database and values.

Application is working on WinCE (WM 6.5.3) and sqlite 3.7.15.2.
From Lazarus I use SQLite3Connection , SQLTransaction and SQLQuery.

Does anyone know why locate is so much slower then SQL ?

Thx

----------------------------------------------------------------------
goodname · 2013-02-10 17:36

This is just a guess. Maybe the full dataset is not in memory. That
means locate has to fetch 10 records at a time and review them. The
select method just has one fetch and returns only relevant data.

----------------------------------------------------------------------
jshand2010 · 2013-02-11 03:43

in my application, i have avoided using locate, because i don't know
how it works.  didn't realise it was that slow.

with the select statement you can call only the fields you wish to use
in the part of the program you wish to extract certain data.  if you
have a table called person, and data with firstname, middlename,
lastname, address, homephone etc.. and you only want to select
firstname, lastname and homephone for example, you would call:

TSQLQuery1.close;
TSQLQuery1.SQL.clear;
TSQLQuery1.SQL.add('select firstname, lastname, homephone from
person;');
//note this selection is in memory for you to utilise.
TSQLQuery1.execSQL;
TSQLQuery1.open;
TSQLQuery1.first //make sure you are pointing to the first record.

to call information from memory you would use:

TSQLQuery1.close;
TSQLQuery1.SQL.clear;
TSQLQuery1.SQL.add('select * from person or select firstname,
lastname, homephone from person;');
TSQLQuery1.execsql;
TSQLQuery1.open;
TSQLQuery1.first;

you can use text boxes for this:

edxxxxx.text:=query.Fields[0].asstring; //firstname
edxxxxx1.text:=query.fields[1].asstring; //lastname
edxxxxx2.text:=query.fields[2].asstring; //homephone
even memo boxes with the text property.

i hope this helps.  please let me know how you get on

----------------------------------------------------------------------
jshand2010 · 2013-02-11 03:47

Quote from: bambamns on February 10, 2013, 05:05:45 pmHi ,

If I use sqlquery.locate, it is few hundred times slower then the sql
select statment.
Table fields I use to locate are primary indexes.

So, if I use :
Code: [Select]if DM.RabatiQ.Locate('komitent;poslovnica;brend',VarArra
yOf([DM.PoslovniceQ.FieldByName('komitent').AsString,
                                                           
 DM.PoslovniceQ.FieldByName('poslovnica').AsString,
                                                           
 DM.ArtikliQ.FieldByName('brend').AsInteger]),[]) then
      begin
       Result := DM.RabatiQ.FieldByName('rabat').AsFloat;
      end;                                                           
                                                
it takes over 5 sec to do it.

If I use :
Code: [Select]With DM.SQLQ do
     begin
      close;
      sql.Text := 'select rabat from rabati';
      sql.Add('where komitent =
'+QuotedStr(DM.PoslovniceQ.FieldByName('komitent').AsString));
      sql.Add('and poslovnica =
'+QuotedStr(DM.PoslovniceQ.FieldByName('poslovnica').AsString));
      sql.Add('and brend =
'+DM.ArtikliQ.FieldByName('brend').AsString);
      open;                   
      Result := FieldByName('rabat').AsFloat;
      close;                                
    end;
it takes 0.25 sec to be done with same database and values.

Application is working on WinCE (WM 6.5.3) and sqlite 3.7.15.2.
From Lazarus I use SQLite3Connection , SQLTransaction and SQLQuery.
//you are missing TDatasource.  this must be included to gather
information from the database.  this is found in data access

Does anyone know why locate is so much slower then SQL ?

Thx

----------------------------------------------------------------------
bambamns · 2013-02-11 04:53

As I can see on lot of Delphi forums, Locate is slow because it has to
fetch all record's to find one.
Alternative is to use seek or findkey, but TQuery does not have them.

Locate is very useful because it is boolean and it return false if it
can't find the record, and true if it can , so you can write a single
line to check if some values exist in table or not.
Another useful thing is that locate position a cursor in table if it
find an value you are searching for.

However it seems that on large tables, or if you need a loop of
locates, is to write an SQL (close, sql.text,open ....) to speed up
searching for a single value to edit it.

Thx for the answers.

----------------------------------------------------------------------
taazz · 2013-02-11 05:53

I personally use a second TDataset to retrieve a single row for
editing this has the advandage that on multi user systems it will
retrieve the latest commited values instead of the ones in memory from
a query that might be there for hours depending on the user of course
that solves a number of problems and creates one you need to find a
way to refresh the selected row only after the user commits his
changes.