Which query is more efficient?

[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].

));

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);

 

[John Spencer (MVP)]

Try them and see, but I think that the second is very probably the more efficient.

 

[[MVP] S.Clark]

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.

 

[Andreas]

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

 

[Guest]

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!

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

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].

));

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]