September 25, 2008 at 9:21 am
I have tuned the fiollowing query the best I can. I have added indexes, added views, and even spread Extra Virgin Oil all over it to make it go faster than 1 minutes and 30 seconds. I have used the performance tuning tools of SQL Server 2005 and they have NO reccommendations. The list is empty. I have included teh Execution Plan as an attachment.
My last resort is to rewrite the query, but I am not good at that. What the heck, I am not even poor at it. SO I am asking all you experts out there for some help.
select D2.c5 as c1,
D2.c6 as c2,
D2.c7 as c3,
D1.c1 as c4,
D1.c2 as c5,
D1.c3 as c6,
D1.c4 as c7
from
(select count(distinct case when not T32319."X_CS_RUN_ID" is null then T32353."ROW_WID" end ) as c1,
count(case when T32319."RESP_TYPE" = N'Requested Unsubscribe' then T32353."ROW_WID" end ) as c2,
count(distinct case when T32319."RESP_TYPE" = N'Clicked On URL' then T32353."ROW_WID" end ) as c3,
T255596."TOP_LVL_SOURCE_NAME" as c4
from
"WC_SOURCE_DH" T255596,
"W_RESPONSE_D" T32319 /* Dim_W_RESPONSE_D */ ,
"W_RESPONSE_F" T32353 /* Fact_W_RESPONSE_F */ ,
"W_SOURCE_D" T43873 /* Dim_W_SOURCE_D_Campaign */
where ( T43873."ROW_WID" = T255596."ROW_WID" and T32319."ROW_WID" = T32353."RESPONSE_WID" and T32353."SOURCE_WID" = T43873."ROW_WID" and T43873."X_CAMP_TYPE" <> N'SR Survey' and T255596."TOP_LVL_SOURCE_NAME" <> N'Unspecified' )
group by T255596."TOP_LVL_SOURCE_NAME"
) D1,
(select count(distinct case when not T32319."X_CS_RUN_ID" is null then T32353."ROW_WID" end ) as c5,
count(case when T32319."RESP_TYPE" = N'Requested Unsubscribe' then T32353."ROW_WID" end ) as c6,
count(distinct case when T32319."RESP_TYPE" = N'Clicked On URL' then T32353."ROW_WID" end ) as c7
from
"WC_SOURCE_DH" T255596,
"W_RESPONSE_D" T32319 /* Dim_W_RESPONSE_D */ ,
"W_RESPONSE_F" T32353 /* Fact_W_RESPONSE_F */ ,
"W_SOURCE_D" T43873 /* Dim_W_SOURCE_D_Campaign */
where ( T43873."ROW_WID" = T255596."ROW_WID" and T32319."ROW_WID" = T32353."RESPONSE_WID" and T32353."SOURCE_WID" = T43873."ROW_WID" and T43873."X_CAMP_TYPE" <> N'SR Survey' and T255596."TOP_LVL_SOURCE_NAME" <> N'Unspecified' )
) D2
order by c7
September 25, 2008 at 10:00 am
What's that query supposed to do?
How many rows are in those tables?
Can you post the table and index structures and some sample data please?
Why all the count distincts? They're very likely the problem as doing both aggregations and distinct aggregations can result in multiple scans.
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
September 25, 2008 at 10:13 am
Gila,
I have no idea what this query is suppose to do. It is generated by Siebel for a report that is used by the marketing team based on Campaign History records. There are 70 million rows in the table.
If you review the execution plan you will see exactly what indexes are there, exactly how many rows in each table, and you can also see what the query is doing at each point.
I have exhausted the idea of creating indexes to improve performance. As you will notice from the Execution plan, there are no table scans.
I am sure this did not help you any, but they are answers to your questions.
What I am asking for is someone who knows SQL so well that they can see a better way of writing this SQL code.
Maybe I am asking for too much???
September 25, 2008 at 10:35 am
If you want someone to rewrite the query, you're going to have to help them.
Please give us the table structure (as create table statements), the index definitions (all of them, not just the ones currently been used by the query) as create index statements and some sample data that ca be used for testing the query.
The tables that have an index seek aren't showing the total number or rows in the table, just the number of rows retrieved by the query. Hence the reason I'm asking for the row counts.
You do have table scans. Two of them. A clustered index scan is a table scan. You'll only see the table scan operator if the table doesn't have a clustered index
From what I can see, this query is pulling over 400MB of data. It's not going to be quick if it's using that much data.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply