Pass Thru Query Performance

[Evette]

I have a program that runs Access 2000 on the front end SQL Server on the back end. When the program first opens it opens fairly quick but when I need to filter for a specific locationname it slow dramatically. Here is an example of the Code without filters:

SELECT TR_REMOTE_RECEIVE.RECEIVETIME,
TR_REMOTE_RECEIVE.RECEIVEDURATION,
TR_REMOTE_RECEIVE.PATIENTNAME,
TR_REMOTE_RECEIVE.PATIENTID, TR_REMOTE_RECEIVE.STUDYID,
TR_REMOTE_RECEIVE.STUDYDESCRIPTION,
TR_REMOTE_RECEIVE.STUDYMODALITY, TR_PROCESSSTATUS.FAX,
TR_PROCESSSTATUS.FAXTIME, TR_PROCESSSTATUS.DIAGNOSIS,
TR_PROCESSSTATUS.DIAGTIME,
TR_PROCESSSTATUS.DIANOSISDESCRIPTION,
TR_PROCESSSTATUS.RESPONCEMEDIA,
TR_PROCESSSTATUS.RESPONCETIME,
TR_REMOTE_RECEIVE.LOCATIONNAME,
TR_REMOTE_COMPLETEDTRANS.SUCCESSFULCOMPLETIONTIME,
TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE,
TR_REMOTE_RECEIVE.M_ID
FROM TR_REMOTE_COMPLETEDTRANS, TR_REMOTE_RECEIVE,
TR_PROCESSSTATUS
WHERE TR_REMOTE_COMPLETEDTRANS.M_ID = TR_REMOTE_RECEIVE.M_ID
AND
TR_REMOTE_COMPLETEDTRANS.M_ID = TR_PROCESSSTATUS.M_ID
(+) AND (TR_REMOTE_COMPLETEDTRANS.SUBMISSIONTIME BETWEEN TO_DATE('03/14/2005', 'MM/DD/YYYY') AND TO_DATE('03/15/2005', 'MM/DD/YYYY'))AND ((TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE = 'Station4') OR (TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE = 'Station3') OR (TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE = 'Station2') OR (TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE = 'Station1')) AND ((TR_processstatus.RESPONCETIME IS NULL)) order by TR_REMOTE_COMPLETEDTRANS.SUBMISSIONTIME desc

With the filter it is:

SELECT TR_REMOTE_RECEIVE.RECEIVETIME,
TR_REMOTE_RECEIVE.RECEIVEDURATION,
TR_REMOTE_RECEIVE.PATIENTNAME,
TR_REMOTE_RECEIVE.PATIENTID, TR_REMOTE_RECEIVE.STUDYID,
TR_REMOTE_RECEIVE.STUDYDESCRIPTION,
TR_REMOTE_RECEIVE.STUDYMODALITY, TR_PROCESSSTATUS.FAX,
TR_PROCESSSTATUS.FAXTIME, TR_PROCESSSTATUS.DIAGNOSIS,
TR_PROCESSSTATUS.DIAGTIME,
TR_PROCESSSTATUS.DIANOSISDESCRIPTION,
TR_PROCESSSTATUS.RESPONCEMEDIA,
TR_PROCESSSTATUS.RESPONCETIME,
TR_REMOTE_RECEIVE.LOCATIONNAME,
TR_REMOTE_COMPLETEDTRANS.SUCCESSFULCOMPLETIONTIME,
TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE,
TR_REMOTE_RECEIVE.M_ID
FROM TR_REMOTE_COMPLETEDTRANS, TR_REMOTE_RECEIVE,
TR_PROCESSSTATUS
WHERE TR_REMOTE_COMPLETEDTRANS.M_ID = TR_REMOTE_RECEIVE.M_ID
AND
TR_REMOTE_COMPLETEDTRANS.M_ID = TR_PROCESSSTATUS.M_ID
(+) AND (TR_REMOTE_COMPLETEDTRANS.SUBMISSIONTIME BETWEEN TO_DATE('03/14/2005', 'MM/DD/YYYY') AND TO_DATE('03/15/2005', 'MM/DD/YYYY')) and ((TR_REMOTE_RECEIVE.LOCATIONNAME = 'Location Name')) AND ((TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE = 'station4') OR (TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE = 'Station3') OR (TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE = 'Station2') OR (TR_REMOTE_COMPLETEDTRANS.DICOMAETITLE = 'Station1')) AND ((TR_processstatus.RESPONCETIME IS NULL)) order by TR_REMOTE_COMPLETEDTRANS.SUBMISSIONTIME desc

It takes about 15 to 30 seconds to refresh when filters are applied. Any help would be appreciated.

Thanks
Evette

 

[PNC]

Some ideas:

1) Try removing the ORDER BY clause...
2) Create indexes on the tables
3) Apply the filters after the data is downloaded to Access, i.e directly on the form or report

Hope this helps.
- PNC.