SQL Server Performance Forum – Threads Archive
Query Optimization
Can anyone look at this query which is computer generqated and see any possible enhancements to improve the speed. The majot table has 1.9 million rows. This query takes 11 minutes to execute. select distinct isnull(D1.c6 , 0) as c1, D1.c3 as c2, D1.c5 as c3, D1.c7 as c4, isnull(D1.c2 , 0) as c5, isnull(D1.c1 , 0) as c6, isnull(D1.c4 , 0) as c7 from (select D4.c3 as c1, D3.c4 as c2, D1.c3 as c3, D2.c5 as c4, D1.c4 as c5, D1.c1 as c6, D1.c2 as c7 from (select count(distinct case when T31284."LAUNCH_DT_WID" > 0 then T31284."CONTACT_WID" end ) as c1, T31328."PER_NAME_MONTH" as c2, T43873."MKTPLN_NAME" as c3, T43873."MKTPLN_TYPE" as c4 from "W_CAMP_HIST_F" T31284 /* Fact_W_CAMP_HIST_F */ , "W_DAY_D" T31328 /* Dim_W_DAY_D_Common */ , "W_SOURCE_D" T43873 /* Dim_W_SOURCE_D_Campaign */ where ( T31284."CREATED_DT_WID" = T31328."ROW_WID" and T31284."CAMPAIGN_WID" = T43873."ROW_WID" and T31328."PER_NAME_YEAR" = N’2007′ ) group by T31328."PER_NAME_MONTH", T43873."MKTPLN_NAME", T43873."MKTPLN_TYPE" ) D1, (select count(distinct case when T31284."LAUNCH_DT_WID" > 0 then T31284."CONTACT_WID" end ) as c5, T43873."MKTPLN_NAME" as c6, T43873."MKTPLN_TYPE" as c7 from "W_CAMP_HIST_F" T31284 /* Fact_W_CAMP_HIST_F */ , "W_DAY_D" T31328 /* Dim_W_DAY_D_Common */ , "W_SOURCE_D" T43873 /* Dim_W_SOURCE_D_Campaign */ where ( T31284."CREATED_DT_WID" = T31328."ROW_WID" and T31284."CAMPAIGN_WID" = T43873."ROW_WID" and T31328."PER_NAME_YEAR" = N’2007′ ) group by T43873."MKTPLN_NAME", T43873."MKTPLN_TYPE" ) D2, (select count(distinct case when T31284."LAUNCH_DT_WID" > 0 then T31284."CONTACT_WID" end ) as c4, T43873."MKTPLN_NAME" as c5 from "W_CAMP_HIST_F" T31284 /* Fact_W_CAMP_HIST_F */ , "W_DAY_D" T31328 /* Dim_W_DAY_D_Common */ , "W_SOURCE_D" T43873 /* Dim_W_SOURCE_D_Campaign */ where ( T31284."CREATED_DT_WID" = T31328."ROW_WID" and T31284."CAMPAIGN_WID" = T43873."ROW_WID" and T31328."PER_NAME_YEAR" = N’2007′ ) group by T43873."MKTPLN_NAME" ) D3, (select count(distinct case when T31284."LAUNCH_DT_WID" > 0 then T31284."CONTACT_WID" end ) as c3 from "W_CAMP_HIST_F" T31284 /* Fact_W_CAMP_HIST_F */ , "W_DAY_D" T31328 /* Dim_W_DAY_D_Common */ where ( T31284."CREATED_DT_WID" = T31328."ROW_WID" and T31328."PER_NAME_YEAR" = N’2007′ ) ) D4 where ( (D1.c4 = D2.c7 or (D1.c4 is null and D2.c7 is null)) and (D1.c3 = D2.c6 or (D1.c3 is null and D2.c6 is null)) and (D1.c3 = D3.c5 or (D1.c3 is null and D3.c5 is null)) ) ) D1if this is computer generated query, you can’t change this.
SO you have to check the indexes. First of all can you try index tunning wizard —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
Yes I can change the computer generated query, I own the code. I am not an expert at generating SQL statements so I am asking if anyone can see a better way of writing this query to get the same results. Thank you
Rather than attempting to change the query why don’t you identify the same using PROFILER & Data Tuning Advisor for a better recommdation of indexes.
You can take help of KBAhttp://support.microsoft.com/kb/243589 &http://sqlserver-qa.net/blogs/perft…shoot-slow-running-queries-in-sql-server.aspx blog for further actions. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>