SQL Server Performance Forum – Threads Archive

Execution Plan Incorrect? (cte)

HI, I have two queries that return the same result. The execution plan says they are equal in cost, however one takes 2 seconds and the other takes 6. I understand why one is faster, but am loosing faith in the execution planner. Any ideas on why they show up equal cost in the plan? Query 1, 2 seconds:
WITH cteBusinessDate (BusinessDate)
AS
(
SELECT [AFTRAescherrer].fnGetCurrentBusinessDate()
)
SELECT A.MemberID,
COALESCE(B.DisburseDateElect, CASE
WHEN DateDiff(dd, BusinessDate, B.LatestDisburseDate) < 0
THEN B.LatestDisburseDate
ELSE BusinessDate
END) AS DisburseDateElect
FROM dbo.tblMember A LEFT JOIN dbo.tblMemberPension B ON A.MemberID = B.MemberID, cteBusinessDate
Query 2, 6 seconds:
SELECT A.MemberID,
COALESCE(B.DisburseDateElect, CASE
WHEN DateDiff(dd, [AFTRAescherrer].fnGetCurrentBusinessDate(), B.LatestDisburseDate) < 0
THEN B.LatestDisburseDate
ELSE [AFTRAescherrer].fnGetCurrentBusinessDate()
END) AS DisburseDateElect
FROM dbo.tblMember A LEFT JOIN dbo.tblMemberPension B ON A.MemberID = B.MemberID

---

What is the index criteria on these tables? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

---

]]>