June 10, 2010 at 11:17 am
Hi,
I am trying to tune a query that has the below listed syntax. The goal is to JOIN into "mytable" only once instead of the three separate subqueries and display a different date based on the three flag codes.
SELECT
DateFlag_A.orderdate,
DateFlag_B.orderdate,
DateFlag_C.orderdate
FROM myMasTable AS b
LEFT JOIN
(SELECT id, CONVERT(VARCHAR (10),MAX(orderdate),110) AS orderdate
FROM myTable
WHERE flag_A = 'y'
GROUP BY id) AS DateFlag_A
ON DateFlag_A.id = b.id
LEFT JOIN
(SELECT id, CONVERT(VARCHAR (10),MAX(business_date),110) AS orderdate
FROM myTable
WHERE flag_B = 'y'
GROUP BY id) AS DateFlag_B
ON DateFlag_B.id = b.id
LEFT JOIN
(SELECT bu_id, CONVERT(VARCHAR (10),MAX(business_date),110) business_date AS orderdate
FROM myTable
WHERE flag_C = 'y'
GROUP BY id) AS DateFlag_C
ON DateFlag_C.id = b.id;
GO
Thanks for your help.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
June 10, 2010 at 11:22 am
Are you able to post the table structure and some sample data?
June 10, 2010 at 12:04 pm
SELECT MAX(CASE WHEN Flag_a = 'y' then DateA ELSE NULL END) AS Datea
, MAX(CASE WHEN Flag_b = 'y' then Dateb ELSE NULL END) AS Dateb
, MAX(CASE WHEN Flag_c = 'y' then Datec ELSE NULL END) AS Datec
FROM ...
tho it will always force a table scan that way.
It might be worth testing if adding 3 indexes improves the query enough for all that trouble. All indexes would be in the form of flag_?, Date.
That way you may get only 3 seeks rather than a full scan.
June 10, 2010 at 2:36 pm
Ninja I just discovered that flag_? is indexed but not Date...also the sulotion you suggested would work but again without a (flag_?, date) index I'm not sure how much gain I will get. I am trying to get access to the environment and add the index -- will keep you posted.
Thanks for your help.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
June 10, 2010 at 3:21 pm
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply