[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:
> 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
> 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
SQL.Text:='SELECT * FROM MyTable where KeyField=:MyKey';
ParamByName('MyKey').AsString:=Mykey; // MyKey you got through the request.
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
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.
More information about the fpc-pascal