Which query is more efficient?

[ZABADAK]

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

 

[r937]

as far as efficiency is concerned, there should be no difference

but the second one will be a lot easier to maintain if you needed to change from inner join to left outer



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[BNPMike]

It's not impoossible that the first could be more efficient with Access and Jet. Access prefers the newer syntax and may well therefore not optimise the second query as thoroughly

 

[alvechurchdata]

Whilst we're theorising can Zabadak tell us which query runs more quickly?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[BNPMike]

That's cheating...

 

[alvechurchdata]

That's cheating...

Well I thought that if he'd tell us which was quicker then we'd all be able to give really interesting reasons explaining why it was obviously going to be faster that way.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ZABADAK]

The so-called Access guru that sits next to me sat work said the second one would run faster. He composes his queries with the Access tool whereas I always code in raw SQL. I can't really tell which is faster. Since Access has no profiler tool, I don't know how to tell.

 

[r937]

the adjective "so-called" is appropriate

the access tool often generates cr@p

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[alvechurchdata]

Since Access has no profiler tool, I don't know how to tell.

You could write a short bit of code which reads the system clock into one variable, runs the query, then reads the system clock again and subtracts one from the other:

timStart = Time()
DoCmd RunSQL strSQL
timEnd = Time()
?DateDiff("s", timStart, timEnd)

It's not perfect but it'll give an idea of the timing to the nearest second. If the queries take a fraction of a second to run then you might have to time them over a hundred times in a loop.

On the other hand, does it really matter if the query takes a tenth of a second or a hundredth of a second. Computer time is cheap - developer time is expensive.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ZABADAK]

It does matter to me because I am handling huge volumes of data. This is a table that accumulates data over the months and gets bigger and bigger. It should really be in SQL Server but they don't have enough servers here. By the way computer time is not cheap. We are charged for each cpu second.

 

[alvechurchdata]

We are charged for each cpu second

Ouch. My apologies. I've not been charged for cpu time since we had a TeleType linked to the corporate mainframe in the earky 80s.

Geoff Franklin

http://www.alvechurchdata.co.uk