Jack Corbett (7/25/2008)
Your biggest problem is that you are using Functions against columns in the where of the subquery which will cause a table scan. You should first look at how you can eliminate that. Can you give us the definition of the table, some test data, and what you are trying to accomplish with the query? Can you convert it to a stored procedure where we can use table variables/temp tables?This may be what you want:
[font="Courier New"]SELECTF.*
FROM
xxxxflow F JOIN
(SELECT
MAX(mstr_ordid) AS max_ord_id,
Ord_Num,
CONVERT(VARCHAR(10),b.date,101) AS date_string
FROM
xxxflow) B ON
f.Ord_Num = B.Ord_Num AND
CONVERT(VARCHAR(10),f.date_time,101) = B.date_string AND
F.mstr_ordid = B.max_ord_id
[/font]
Jack - so the function against columns is causing the table scan in the WHERE, but not in the JOIN in your re-write? Does SQL not have to scan to evaluate the date from F to compare to B.date_string?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."