Which query is more efficient?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Which of the following is more efficient or is there no difference?

SELECT tblSSRData.*, tblPriority.Desc AS PriDesc, tblPriority.Target AS
PriTarg
FROM tblSSRData, qryMaxThru, tblPriority
WHERE (((tblSSRData.[SSR_#])=[qryMaxThru].[SSR_#]) AND
((tblSSRData.[TASK_#])=[QryMaxThru].[Task_#]) AND
((qryMaxThru.Max_Thru_Date)=[tblSSRData].[REPORT_THRU_DATE]) AND
((tblSSRData.PRI)=[tblPriority].
Code: 
));

SELECT tblSSRData.*, tblPriority.Desc AS PriDesc, tblPriority.Target AS
PriTarg
FROM (tblSSRData INNER JOIN tblPriority ON tblSSRData.PRI =
tblPriority.Code) INNER JOIN qryMaxThru ON (tblSSRData.[SSR_#] =
qryMaxThru.[SSR_#]) AND (tblSSRData.[TASK_#] = qryMaxThru.[Task_#]) AND
(tblSSRData.REPORT_THRU_DATE = qryMaxThru.Max_Thru_Date);
 
Try them and see, but I think that the second is very probably the more efficient.
 
What does your stopwatch say when you run them?
Which is easier to maintain?

You could speed them both up if you remove the * and replace with only the
needed field names.
 
I generally use the second syntax.

BUT I have come across a situation where this dropped records in a
complex query. Unfortunately I lost the example but it was consistently
reproducable and lost different records at different times (and no,
there were no NULL values).

The first syntax fixed the issue.

Regards,
Andreas
 
I found that timing thay way is not consistent or reliable.
Also, there are over 200 columns and I don't feel like listing them all!

[MVP] S.Clark said:
What does your stopwatch say when you run them?
Which is easier to maintain?

You could speed them both up if you remove the * and replace with only the
needed field names.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

mscertified said:
Which of the following is more efficient or is there no difference?

SELECT tblSSRData.*, tblPriority.Desc AS PriDesc, tblPriority.Target AS
PriTarg
FROM tblSSRData, qryMaxThru, tblPriority
WHERE (((tblSSRData.[SSR_#])=[qryMaxThru].[SSR_#]) AND
((tblSSRData.[TASK_#])=[QryMaxThru].[Task_#]) AND
((qryMaxThru.Max_Thru_Date)=[tblSSRData].[REPORT_THRU_DATE]) AND
((tblSSRData.PRI)=[tblPriority].
Code: 
));

SELECT tblSSRData.*, tblPriority.Desc AS PriDesc, tblPriority.Target AS
PriTarg
FROM (tblSSRData INNER JOIN tblPriority ON tblSSRData.PRI =
tblPriority.Code) INNER JOIN qryMaxThru ON (tblSSRData.[SSR_#] =
qryMaxThru.[SSR_#]) AND (tblSSRData.[TASK_#] = qryMaxThru.[Task_#]) AND
(tblSSRData.REPORT_THRU_DATE = qryMaxThru.Max_Thru_Date);[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
Back
Top