Re: Ado SQL Concept question.

From: RPM (rick_at_tariffnet.com)
Date: 03/05/04 

Date: Fri, 5 Mar 2004 11:48:45 -0500

Hi Richard,

>"Richard Speiss" <rspeiss@mtxinc.com> wrote in message
news:40477b0b$1@newsgroups.borland.com...
> 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

This looks interesting... just a bit worried about memory useage. I'm going
to try some test. Good idea.

> 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.
I'm just 'stress' testing the lookup. If I use database pools to serve web
browsers, each request (lookup) will be independent of any other... so I'm
trying to get an idea of what I can do to speed up that lookup. My
application has been alotted a time of 50ms for each call.... kinda tight.
:)

> 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

The user only reads the data, and the data only changes once a month... so
no problem there. :)

Thanks for the ideas,
Rick

Relevant Pages

  • Re: Basic Question for Lookups.
    ... However, especially in the early phases of setting up your Tables, you will probably need at times to look at their contents, and if so, you probably will want to define lookup properties on the foreign keys. ... The Query that I would suggest is similar to what the Lookup Wizard defines, except that I give mine a name, and I limit it to 2 fields instead of several. ... 1801550689 Michael Zachary Jackson ... I'm properly storing Prospect ID pk then FirstName then Middle then Lastname fields.etc. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Tip: Table design and query for bracketed lookup
    ... WHERE [Enter Bracket:] ... No rows would be returned by the query! ... If the "lookup" value is in a column of your query called Lookup, ... FROM RateTable RT1 ...
    (microsoft.public.access.queries)
  • Re: WHERE statement
    ... with basically all the info going to one table, except now the suppliers, ... "Jeff Boyce" wrote: ... and you can still use a combobox to list the lookup values. ... I know I get the ID field if I query against a 'lookup' ...
    (microsoft.public.access.gettingstarted)
  • Re: Understanding what makes Access tick
    ... > Query, but which is the subject of a table's lookup relationship. ... allow any "main" record..and "maybe" a child record. ... want here is a simply list of tasknumbers. ...
    (microsoft.public.access.gettingstarted)
  • Re: WHERE statement
    ... "Jeff Boyce" wrote: ... You can change the table's field from a lookup type to a (?whatever the ... I know I get the ID field if I query against a 'lookup' value. ... NOT USE Lookup data type fields, for just that reasons. ...
    (microsoft.public.access.gettingstarted)