On Fri, 21 Oct 2011, Felipe Monteiro de Carvalho wrote:
On Fri, Oct 21, 2011 at 5:22 PM, Ludo Brands <ludo.bra...@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.
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal