Really Slow Query

  • Thread starter Thread starter XMan
  • Start date Start date
X

XMan

My backend database is SQL-Server. The query is in Access via ODBC.
It's running fast when I test it on SQL Query Analyzer but run really slow
in Access.
Please help if you can, TIA.

Here's the SQL:

SELECT
DH.DIALOG_UI AS DIALOG_UI,
DH.UPDATED_DATE AS UPDATED_DATE,
DH.NOTES AS NOTES,
T.NAME AS ACCOUNTABLE_TEAM,
P.NAME AS ACCOUNTABLE_PERSON
FROM
(DIALOG_HISTORIES AS DH LEFT JOIN TEAMS AS T ON DH.TEAM_UI=T.TEAM_UI)
LEFT JOIN PERSONNEL AS P ON DH.PERSONNEL_ID=P.PERSONNEL_UI
WHERE
DH.DIALOG_HISTORY_UI IN
(SELECT TOP 3 DH1.DIALOG_HISTORY_UI
FROM DIALOG_HISTORIES AS DH1
WHERE DH.DIALOG_UI=DH1.DIALOG_UI
ORDER BY DH1.UPDATED_DATE DESC)
ORDER BY
DH.DIALOG_UI,
DH.UPDATED_DATE DESC;
 
Can you use a pass-through query? This would run at about the same speed as
the Query Analyzer.
 
I've made the query into a view on SQL-Server and that made a whole world of
difference.
Now it's running really fast!
Thanks for the help.
 
Hi,

Access queries using ODBC linked tables can slow down very quickly as the
number of tables in the query grows. I generally use pass thru queries if I
do not need the result to be updateable, or I use a linked view or an Access
query that executes a procedure on the backend if the query does need to be
updateable. Of course, as with any query, it is always good to make sure
that your join fields and fields with criteria are indexed (unless the
criteria field only has a few different entry values, in which case it may
not help). But, I'm guessing that indexing is not the problem since you said
that the query ran quickly in the query analyzer.

HTH, Ted Allen
 
Back
Top