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

[Michael Van Canneyt]

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.