Re: Ado SQL Concept question.

• Previous message: Pavel: "Re: How to use COLLATE to SORT ADO table by international sorting rules?"
• In reply to: RPM: "Ado SQL Concept question."
• Next in thread: RPM: "Re: Ado SQL Concept question."
• Reply: RPM: "Re: Ado SQL Concept question."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Thu, 4 Mar 2004 11:52:34 -0700

Hi Rick,

Here is something you can do that will speed up your lookups

At the beginning of your routine, create a query that returns those values
for all rows in the lookup table

qryLookup.Close;
qryLookup.SQL.Clear;
qryLookup.SQL.Append('SELECT W, TI, WV, WH FROM PointInformation ORDER BY
W');
qryLookup.Open;

for iIdx := 1 to LotsOfRecords do begin
  // Instead of opening the lookup query, now you just use Locate
  if qryLookup.Locate('W', sCLLI, []) = True then begin
    // do some processing here
  end
end

qryLookup.Close

The slow part of using SQL is the act of opening the query. This will cause
your program to slow down to a crawl if you are doing it for every record.

The downside to the above method is that the lookup list is fixed for the
duration of your loop. If you can live with that then you should be okay

I hope this helps

Richard Speiss

"RPM" <rick@tariffnet.com> wrote in message
news:404512a0$1@newsgroups.borland.com...
> Hi everybody,
>
> I am an 'old dBase' programmer, been using Apollo since Delphi 1. Well I
> have finally started writting some programs using ADO and Access/MSDE
> (debugging on Access 97). I have learned alot about SQL and seem to be
able
> to get my queries to work. However I have a question about the overall
> process.
>
> I need to do single record lookups - thousands of times - now my SELECT
> statement works great and only returns the fields that my program
requires.
> So I have tried to keep it simple. However it is just increadibly SLOW,
> when compared to the old dBase index file seeks. Is there anyway to
> optimize this query?
>
> Here is my "seek" routine. (Using KAADO VCL tools for D5 Pro):
>
> Please note: The ADO connection remains open for the entire time. The file
> only contains about 250,000 records. And is on my local machine (during
> testing).
>
> function Tform1.AdoSeek( sCLLI: string ): boolean;
> var
> lResult: boolean;
> begin
> lResult := false;
> AdoTable.Active := false;
> with AdoTable.SQL do begin
> Clear;
> Add('SELECT W, TI, WV, WH');
> Add('FROM PointInformation');
> Add('WHERE W="'+sCLLI+'"');
> end;
> AdoTable.Active := true;
> if AdoTable.Reccount > 0 then lResult := true;
> AdoSeek := lResult;
> end;
>
> Thanks in advance,
> Rick
>
>

• Previous message: Pavel: "Re: How to use COLLATE to SORT ADO table by international sorting rules?"
• In reply to: RPM: "Ado SQL Concept question."
• Next in thread: RPM: "Re: Ado SQL Concept question."
• Reply: RPM: "Re: Ado SQL Concept question."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]