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

Reply via email to