CODEWITHSUNDEEP
delphidelphi-7adoms-access-2003
user805528
user805528

Reputation: 301

Delphi ADO with MS Access - query times reduce after running specific query

I have a Delphi application with 3 forms, I'm using Access 2003 and Microsoft.Jet.OLEDB.4.0, I had an ADOconnection in the main form and use it in all forms.

I use 2 .mdb files, where my.mdb has links to org.mdb tables.

Everything works, but very slowly. So after long hours of searching I came to this.

I don't know why, but after I run this query all other queries increase in speed dramatically (From 10 seconds under 1 second). (Even queries that don't unclude linked tables).

Table tb_odsotnost is in my.mdb
Table Userinfo is linked.

  with rQueries.ADOQuery1 do
    begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT DISTINCT tb_odsotnost.UserID, Userinfo.Name FROM tb_odsotnost');
    SQL.Add('LEFT JOIN Userinfo ON Userinfo.UserID = tb_odsotnost.UserID');
    SQL.Add('WHERE datum BETWEEN '+startDate+' AND'+endDate);
    SQL.Add('ORDER BY Userinfo.Name ASC');
    Open;
  end;

I tried to run my app on another computer with win7 and MS Access 2007 and the result was the same.

Ok, for now I just run this query onFormActivate but this is not a permanent solution.

Upvotes: 1

Views: 1472

Answers (1)

Sam M
Sam M

Reputation: 4166

When you run a query against a linked table, Access (or Jet or ADO) acquires a lock on the database for the ldb file. If you close the query, that lock has to be reacquired the next time you query the linked table. The recommended method to get around this is to always keep a background dataset open so that the lock doesn't have to be obtained each time (forcing the lock to remain in effect).

See http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx and look at the "Improve performance of linked tables" section.

If that doesn't help, look at your table definitions in Access to see if you have subdatasheets defined for your table fields in one-to-many relationships.

Upvotes: 3

Related Questions