[fpc-pascal] sqldb how to find a record fast with primary key

Michael Van Canneyt michael at freepascal.org
Fri Oct 21 18:03:53 CEST 2011



On Fri, 21 Oct 2011, Felipe Monteiro de Carvalho wrote:

> On Fri, Oct 21, 2011 at 5:22 PM, Ludo Brands <ludo.brands at free.fr> wrote:
>> You can use indices and locate with TSQLQuery as follows:
>>  SQLQuery1.AddIndex('idx_no_art','no_art',[]);
>>  SQLQuery1.IndexName:='idx_no_art';
>>  SQLQuery1.Open;
>>  ...
>>  SQLQuery1.Locate('no_art','200295',[]);
>
> Thanks, that's really interresting, but it does not seam to be useful
> in my case. Basically my application is a cgi app which receives a
> request, does some modifications in the database and then sends back
> some other data based on the request and on the database and then it
> just quits.
>
> I get the value of the primary key of the table from the request, so I
> though that because it is the primary key I would be able to quickly
> jump to it. But it seams that not? From what I understood the
> solutions are first going through the entire table and indexing it to
> make future lookups faster. But this does not seam to use the fact
> that my field is the primary key.
>
>> From my experience doing a loop
>
> while not SQLQuery.EOF do
>  compare
>  SQLQuery.Next
>
> is *really* slow.

Yes, but of course this is the complete wrong way. Of course this is slow.

You must do a

With TSQLQuery do
   begin
   SQL.Text:='SELECT * FROM MyTable where KeyField=:MyKey';
   ParamByName('MyKey').AsString:=Mykey; // MyKey you got through the request.
   Open;
   try
     if (EOF and BOF) then
       // no data, raise an exception
       Raise Exception.CreateFmt('Value "%s" is not an existing key value',[MyKey]);
     // Do some stuff with the record

   finally
     Close;
   end;
   end;


Then you will get only the record you need, and it will be lightning fast 
if you have used the primary key as the field to select on.

Michael.


More information about the fpc-pascal mailing list