Which is faster?

Question

Which is faster?

fhtapia

SSC Eights!

Points: 994
More actions
March 4, 2003 at 4:25 pm

#106154

Quick question... which one of these two look as if they run faster? JOINs are generally faster than IN's but how about in this case?
Select B.CCid, B.ccDateTime, A.StatusID From tbl_ccStatus A
INNER JOIN (Select ccID, MAX(ccDateTime) as ccDateTime
From tbl_ccStatus Group by ccID) B
ON (A.ccID = B.ccID and A.ccDateTime = B.ccDateTime)
Where B.ccID = 1518
OR...
Select A.CCid, A.ccDateTime, A.StatusID From tbl_ccStatus AS A
WHERE ccDateTime IN (Select Max(ccDateTime) From tbl_ccStatus AS B Where A.ccID = B.ccID) AND ccID = 1518
-Francisco

-Francisco

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

kay SSCommitted Points: 1518 More actions · Answer 1

It really depends on the Query plan and the query plan may depend on table statistics, indexes etc.

One thing I noticed is the constant 1518 in your query. If this is more or less a constant you are after, put it in a @variable and avoid

...ON(A.ccID = B.ccID) where B.ccID = @variable

but use this:

...ON(A.ccID = @variable) where B.ccID = @variable

fhtapia SSC Eights! Points: 994 More actions · Answer 2

In this partiqular query there is a a single ccID variable, I explicilty called it 1518 because it was the last one I was working with... thanks for the tip I will go ahead look into adding that solution.

Here are the results of the FIRST subquery

StmtText

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Select B.CCid, B.ccDateTime, A.StatusID From tbl_ccStatus A

INNER JOIN (Select ccID, MAX(ccDateTime) as ccDateTime

From tbl_ccStatus Group by ccID) B

ON (A.ccID = B.ccID and A.ccDateTime = B.ccDateTime)

Where B.ccID = 1518

(1 row(s) affected)

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------

|--Nested Loops(Inner Join, WHERE:([Expr1002]=[A].[ccDateTime]))

|--Stream Aggregate(GROUP BY:([tbl_ccStatus].[ccID]) DEFINE:([Expr1002]=MAX([tbl_ccStatus].[ccDateTime])))

| |--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID]), SEEK:([tbl_ccStatus].[ccID]=1518) ORDERED)

|--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS [A]), SEEK:([A].[ccID]=1518) ORDERED)

(4 row(s) affected)

STATISTICS-----------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 16 ms, elapsed time = 16 ms.

CCid ccDateTime StatusID

----------- ------------------------------------------------------ -----------

1518 2002-07-29 12:20:56.000 7

(1 row(s) affected)

Table 'tbl_ccStatus'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

-Francisco

fhtapia SSC Eights! Points: 994 More actions · Answer 3

This is the results of the 2nd Query using the IN clause... looking at both I think the IN is faster, but I've always thought that the INNER JOIN could be faster...

StmtText

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Select A.CCid, A.ccDateTime, A.StatusID From tbl_ccStatus AS A

WHERE ccDateTime IN (Select Max(ccDateTime) From tbl_ccStatus AS B Where A.ccID = B.ccID) AND ccID = 1518

(1 row(s) affected)

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------

|--Filter(WHERE:([A].[ccDateTime]=[Expr1002]))

|--Nested Loops(Inner Join)

|--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS [A]), SEEK:([A].[CCid]=1518) ORDERED)

|--Table Spool

|--Stream Aggregate(DEFINE:([Expr1002]=MAX(.[ccDateTime])))

|--Clustered Index Seek(OBJECT:([HAAScc].[dbo].[tbl_ccStatus].[IX_tbl_ccStatus_ccID] AS ), SEEK:(.[ccID]=[A].[CCid]) ORDERED)

(6 row(s) affected)

and the STATISTICS ARE:......

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 5 ms.

CCid ccDateTime StatusID

----------- ------------------------------------------------------ -----------

1518 2002-07-29 12:20:56.000 7

(1 row(s) affected)

Table 'tbl_ccStatus'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

-Francisco

guarddata SSCertifiable Points: 6583 More actions · Answer 4

Francisco

Really good question and shows that there is not always a single answer for every situation.

If there were no aggregation involved, or if the IN statement produced a number of results, the INNER JOIN would be my preference. However, in this case, the IN is using a single value (MAX) and is probably the best method.

Just goes to confirm that each set of choices needs to be considered in its own light. Kudos for recognizing this one as a valid exception to a psuedo standard.

Guarddata-