By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Selecting MAX (Date) - Performance Question

P: n/a
I have the following function(s) that each joins an active record with
it's most recent record in an audit trail table, to show differences.
I've perused the execution plans for 2 versions of this function in QA
and it seems to me that the two versions execute identically - that is,
the addressing the sub-query of the WHERE clause with either IN or =
results in the same execution plan.

Does this seem plausible or did I misinterpret something in QA?

---

ALTER FUNCTION Function9()
RETURNS TABLE
AS RETURN
(
SELECT ID, Numb, Name, Q.Diff_ID, Q.Diff_Numb, Q.Diff_Name,
FROM tblQuestion INNER JOIN
(
SELECT ID AS Diff_ID, Numb AS Diff_Numb, Name as Diff_Name

FROM tblQuestion_Audit
WHERE AuditDate IN (SELECT MAX(AuditDate) FROM tblQuestion_Audit)
) AS Q
ON dbo.tblItem.ID = Q.Diff_ID

)

ALTER FUNCTION Function10()
RETURNS TABLE
AS RETURN
(
SELECT ID, Numb, Name, Q.Diff_ID, Q.Diff_Numb, Q.Diff_Name,
FROM tblQuestion INNER JOIN
(
SELECT ID AS Diff_ID, Numb AS Diff_Numb, Name as Diff_Name

FROM tblQuestion_Audit
WHERE AuditDate = (SELECT MAX(AuditDate) FROM tblQuestion_Audit)
) AS Q
ON dbo.tblItem.ID = Q.Diff_ID

)

May 3 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Beowulf (be*****************@hotmail.com) writes:
I have the following function(s) that each joins an active record with
it's most recent record in an audit trail table, to show differences.
I've perused the execution plans for 2 versions of this function in QA
and it seems to me that the two versions execute identically - that is,
the addressing the sub-query of the WHERE clause with either IN or =
results in the same execution plan.

Does this seem plausible or did I misinterpret something in QA?


It seems very plausible.

I nevertheless prefer the version with =. But that is merely because
it's easier to read and understand.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 3 '06 #2

P: n/a
Erland Sommarskog wrote:
Beowulf (be*****************@hotmail.com) writes:
I have the following function(s) that each joins an active record with
it's most recent record in an audit trail table, to show differences.
I've perused the execution plans for 2 versions of this function in QA
and it seems to me that the two versions execute identically - that is,
the addressing the sub-query of the WHERE clause with either IN or =
results in the same execution plan.

Does this seem plausible or did I misinterpret something in QA?
It seems very plausible.


So, the SQL Server query optimizer is smart enough to figure out that
the sub-query in the WHERE clause will return only a single value and is
able to figure out that WHERE x IN (a) is the same as WHERE x = a.
That's pretty cool.
I nevertheless prefer the version with =. But that is merely because
it's easier to read and understand.


Thanks for the input. I certainly agree.
May 5 '06 #3

P: n/a
Beowulf (be*****************@hotmail.com) writes:
So, the SQL Server query optimizer is smart enough to figure out that
the sub-query in the WHERE clause will return only a single value and is
able to figure out that WHERE x IN (a) is the same as WHERE x = a.
That's pretty cool.


That's a fairly trivial exercise for the optimizer, I'd say. :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 5 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.