Mega Code Archive

 

Categories / Delphi / ADO Database

 

Performance with two or more queries

Question: Using MSSQL, or Sybase for that matter, why does a second simple select query run more slowly than a preceding query. Answer: MS SQL has a "pending result sets" behavior where the 1st query will only retrieve a small set of records (usually enough to fill a grid or any other data aware controls), and there will be a pending result set. The second query causes that pending result set to be retrieved. This can be tested by closing the first query before running the second or connecting each query to a TDatabase. The BDE uses a "special" connection type that is restricted to one per database connection. When a second query is run within the same database connection, the BDE must fetch and cache the previous query's records before starting the next query. Suggestions: Close the first query before opening the second Use multiple databases, one per query Use a more restrictive where clause to limit the number of records Use a more restrictive domain result to retrieve only the fields you need