July 28, 2008 at 12:10 pm
Attached is a zip file with teh execution plan, Scripts to create the tables, and teh actual query.
I have tried everything I can to make this query finish. It runs for 8 hours and then fails with a message stating out of resources.
Anyone have any ideas?
select distinct D1.c4 as c1,
D1.c4 as c2,
D1.c5 as c3,
D1.c6 as c4,
D1.c7 as c5,
D1.c1 as c6,
case when D1.c2 * 1.0 / nullif( D1.c3, 0) * 1.0 >= 1.5 then 1 else 0 end as c7,
case when D1.c2 * 1.0 / nullif( D1.c3, 0) * 1.0 between 1.25 and 1.499 then 1 else 0 end as c8,
case when D1.c2 * 1.0 / nullif( D1.c3, 0) * 1.0 < 1.25 then 1 else 0 end as c9,
case when D1.c2 * 1.0 / nullif( D1.c3, 0) * 1.0 >= 1.5 then 1 else 0 end as c10,
case when D1.c2 * 1.0 / nullif( D1.c3, 0) * 1.0 between 1.25 and 1.499 then 1 else 0 end as c11,
case when D1.c2 * 1.0 / nullif( D1.c3, 0) * 1.0 < 1.25 then 1 else 0 end as c12
from
(select D1.c1 as c1,
D2.c6 as c2,
D2.c7 as c3,
D1.c2 as c4,
D1.c3 as c5,
D1.c4 as c6,
D1.c5 as c7
from
(select count(T278171.ROW_WID) as c1,
T238114.X_AREA as c2,
T238114.ORG_TERR_NAME as c3,
T258637.TOP_LVL_NAME as c4,
T238114.NAME as c5
from
WC_EPCR_PROD_CAT_D T238378,
WC_EPCR_MKTROLLUP_D T238377,
WC_ASSET_ATTR_D T236762,
WC_INT_ORG_DH T258637,
W_INT_ORG_D T238114 /* Var Organization (W_INT_ORG_D) */ ,
W_PRODUCT_D T32069 /* Dim_W_PRODUCT_D */ ,
WC_ACTIVATION_F T278171
where ( T238377.S_GROUP_ROLLUP = N'C' and T236762.ATTR1_CHAR_VAL = N'Standard' and T32069.X_PROD_CD = N'Single' and T238378.PROD_ROLLUP = N'COREPRODUCT' and T238378.ROW_WID = T278171.PROD_CAT_WID and T238377.ROW_WID = T278171.MKT_WID and T236762.ROW_WID = T278171.ASSET_ATTR_WID and T238114.ROW_WID = T258637.ROW_WID and T32069.ROW_WID = T278171.PROD_WID and T238114.ROW_WID = T278171.VAR_WID and (T238378.PROD_CATEGORY_NAME = N'SolidWorks' or T238378.PROD_CATEGORY_NAME = N'SolidWorksOffice' or T238378.PROD_CATEGORY_NAME = N'SolidWorksOfficePremium' or T238378.PROD_CATEGORY_NAME = N'SolidWorksOfficePro') )
group by T238114.NAME, T238114.ORG_TERR_NAME, T238114.X_AREA, T258637.TOP_LVL_NAME
) D1,
(select sum(T278171.ACTIVATIONCOUNT) as c6,
count(distinct T31087.X_SEAT_ID) as c7,
T238114.NAME as c8,
T258637.TOP_LVL_NAME as c9,
T238114.ORG_TERR_NAME as c10
from
WC_EPCR_PROD_CAT_D T238378,
WC_EPCR_MKTROLLUP_D T238377,
WC_ASSET_ATTR_D T236762,
WC_INT_ORG_DH T258637,
W_INT_ORG_D T238114 /* Var Organization (W_INT_ORG_D) */ ,
W_PRODUCT_D T32069 /* Dim_W_PRODUCT_D */ ,
W_ASSET_D T31087,
WC_ACTIVATION_F T278171
where ( T238377.S_GROUP_ROLLUP = N'C' and T236762.ATTR1_CHAR_VAL = N'Standard' and T32069.X_PROD_CD = N'Single' and T238378.PROD_ROLLUP = N'COREPRODUCT' and T238378.ROW_WID = T278171.PROD_CAT_WID and T238377.ROW_WID = T278171.MKT_WID and T236762.ROW_WID = T278171.ASSET_ATTR_WID and T238114.ROW_WID = T258637.ROW_WID and T238114.ROW_WID = T278171.VAR_WID and T31087.ROW_WID = T278171.ASSET_WID and T32069.ROW_WID = T278171.PROD_WID and (T238378.PROD_CATEGORY_NAME = N'SolidWorks' or T238378.PROD_CATEGORY_NAME = N'SolidWorksOffice' or T238378.PROD_CATEGORY_NAME = N'SolidWorksOfficePremium' or T238378.PROD_CATEGORY_NAME = N'SolidWorksOfficePro') )
group by T238114.NAME, T238114.ORG_TERR_NAME, T258637.TOP_LVL_NAME
) D2
where ( (D1.c3 = D2.c10 or (D1.c3 is null and D2.c10 is null)) and D1.c4 = D2.c9 and D1.c5 = D2.c8 )
) D1
July 28, 2008 at 12:25 pm
The first thing I'd do is break it apart a little bit. Try running the derived table queries separately.
The one thing I see in the execution plan that might be helpful would be an index on W_Asset_D on columns Row_WID and X_Seat_ID. They might already be included in an index, but putting them in their own might be helpful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 12:45 pm
Hi Pino-daddy
This is untested (no data).
I've made two main changes to the query
1. Replaced old-style joins
2. Renamed the outermost derived table from D2 to D3
select distinct D3.c4 as c1,
D3.c4 as c2,
D3.c5 as c3,
D3.c6 as c4,
D3.c7 as c5,
D3.c1 as c6,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 >= 1.5 then 1 else 0 end as c7,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 between 1.25 and 1.499 then 1 else 0 end as c8,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 < 1.25 then 1 else 0 end as c9,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 >= 1.5 then 1 else 0 end as c10,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 between 1.25 and 1.499 then 1 else 0 end as c11,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 < 1.25 then 1 else 0 end as c12
from
(select D1.c1 as c1,
D2.c6 as c2,
D2.c7 as c3,
D1.c2 as c4,
D1.c3 as c5,
D1.c4 as c6,
D1.c5 as c7
from
(select count(T278171.ROW_WID) as c1,
T238114.X_AREA as c2,
T238114.ORG_TERR_NAME as c3,
T258637.TOP_LVL_NAME as c4,
T238114.NAME as c5
FROM WC_ACTIVATION_F T278171
INNER JOIN WC_EPCR_PROD_CAT_D T238378 ON T238378.ROW_WID = T278171.PROD_CAT_WID
INNER JOIN WC_EPCR_MKTROLLUP_D T238377 ON T238377.ROW_WID = T278171.MKT_WID
INNER JOIN WC_ASSET_ATTR_D T236762 ON T236762.ROW_WID = T278171.ASSET_ATTR_WID
INNER JOIN W_INT_ORG_D T238114 ON T238114.ROW_WID = T278171.VAR_WID /* Var Organization (W_INT_ORG_D) */
INNER JOIN WC_INT_ORG_DH T258637 ON T238114.ROW_WID = T258637.ROW_WID
INNER JOIN W_PRODUCT_D T32069 ON T32069.ROW_WID = T278171.PROD_WID /* Dim_W_PRODUCT_D */
where T238377.S_GROUP_ROLLUP = N'C'
and T236762.ATTR1_CHAR_VAL = N'Standard'
and T32069.X_PROD_CD = N'Single'
and T238378.PROD_ROLLUP = N'COREPRODUCT'
and T238378.PROD_CATEGORY_NAME IN ('SolidWorks', 'SolidWorksOffice', 'SolidWorksOfficePremium', 'SolidWorksOfficePro')
group by T238114.NAME, T238114.ORG_TERR_NAME, T238114.X_AREA, T258637.TOP_LVL_NAME
) D1,
(select sum(T278171.ACTIVATIONCOUNT) as c6,
count(distinct T31087.X_SEAT_ID) as c7,
T238114.NAME as c8,
T258637.TOP_LVL_NAME as c9,
T238114.ORG_TERR_NAME as c10
FROM WC_ACTIVATION_F T278171
INNER JOIN WC_EPCR_PROD_CAT_D T238378 ON T238378.ROW_WID = T278171.PROD_CAT_WID
INNER JOIN WC_EPCR_MKTROLLUP_D T238377 ON T238377.ROW_WID = T278171.MKT_WID
INNER JOIN WC_ASSET_ATTR_D T236762 ON T236762.ROW_WID = T278171.ASSET_ATTR_WID
INNER JOIN W_INT_ORG_D T238114 ON T238114.ROW_WID = T278171.VAR_WID /* Var Organization (W_INT_ORG_D) */
INNER JOIN WC_INT_ORG_DH T258637 ON T238114.ROW_WID = T258637.ROW_WID
INNER JOIN W_PRODUCT_D T32069 ON T32069.ROW_WID = T278171.PROD_WID /* Dim_W_PRODUCT_D */
INNER JOIN W_ASSET_D T31087 ON T31087.ROW_WID = T278171.ASSET_WID
where T238377.S_GROUP_ROLLUP = N'C'
and T236762.ATTR1_CHAR_VAL = N'Standard'
and T32069.X_PROD_CD = N'Single'
and T238378.PROD_ROLLUP = N'COREPRODUCT'
and T238378.PROD_CATEGORY_NAME IN ('SolidWorks', 'SolidWorksOffice', 'SolidWorksOfficePremium', 'SolidWorksOfficePro')
group by T238114.NAME, T238114.ORG_TERR_NAME, T258637.TOP_LVL_NAME
) D2
where ( (D1.c3 = D2.c10 or (D1.c3 is null and D2.c10 is null)) and D1.c4 = D2.c9 and D1.c5 = D2.c8 )
) D3
Check that D1 returns the same as D1 in your original query. Do the same with D2. If everything checks out, attempt to run the whole query.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 28, 2008 at 2:44 pm
SS,
Yes I did that already, made a query like you said. I should have given a list of indexes that are already present. But yes, that index was already there.
One other thing I did not mention. The query is generated by Siebel Analytics, I cannot change the query.
I will get a list of indexes that already exist.
I am thinking I will need to create a view that will be used but I have no clue or idea how to do that or what to put in the view. And then have an index on the view.
Just me thinking out loud
July 29, 2008 at 7:29 am
Have you taken a look at index/table fragmentation and at how current the statistics are?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 7:51 am
We rebuild all indexes every night and also update statsistics with full scan every night.
I tried creating two individual views with schemabinding so I could add indexes on the views, but because of teh select count (Distinct...... ) I cannot add indexes.
The two inner most select statements run in 4 seconds and 13 seconds, respectively.
But the whole query put to gether takes over 8 hours before it fills up TEMPDB and fails.
My Tempdb has 16 datafiles (one for each CPU) and each file is 5,000KB each. Yes that is not a typo. 5GB for each of the 16 tempdb datafiles.
July 29, 2008 at 7:57 am
I wonder if it's the peculiar join between D1 and D2? Using a proper INNER JOIN might help...
select distinct D3.c4 as c1,
D3.c4 as c2,
D3.c5 as c3,
D3.c6 as c4,
D3.c7 as c5,
D3.c1 as c6,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 >= 1.5 then 1 else 0 end as c7,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 between 1.25 and 1.499 then 1 else 0 end as c8,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 < 1.25 then 1 else 0 end as c9,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 >= 1.5 then 1 else 0 end as c10,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 between 1.25 and 1.499 then 1 else 0 end as c11,
case when D3.c2 * 1.0 / nullif( D3.c3, 0) * 1.0 < 1.25 then 1 else 0 end as c12
from
(select D1.c1 as c1,
D2.c6 as c2,
D2.c7 as c3,
D1.X_AREA2 as c4,
D1.ORG_TERR_NAME as c5,
D1.TOP_LVL_NAME as c6,
D1.[NAME] as c7
from
(select count(T278171.ROW_WID) as c1,
T238114.X_AREA,
T238114.ORG_TERR_NAME,
T258637.TOP_LVL_NAME,
T238114.[NAME]
FROM WC_ACTIVATION_F T278171
INNER JOIN WC_EPCR_PROD_CAT_D T238378 ON T238378.ROW_WID = T278171.PROD_CAT_WID
INNER JOIN WC_EPCR_MKTROLLUP_D T238377 ON T238377.ROW_WID = T278171.MKT_WID
INNER JOIN WC_ASSET_ATTR_D T236762 ON T236762.ROW_WID = T278171.ASSET_ATTR_WID
INNER JOIN W_INT_ORG_D T238114 ON T238114.ROW_WID = T278171.VAR_WID /* Var Organization (W_INT_ORG_D) */
INNER JOIN WC_INT_ORG_DH T258637 ON T238114.ROW_WID = T258637.ROW_WID
INNER JOIN W_PRODUCT_D T32069 ON T32069.ROW_WID = T278171.PROD_WID /* Dim_W_PRODUCT_D */
where T238377.S_GROUP_ROLLUP = N'C'
and T236762.ATTR1_CHAR_VAL = N'Standard'
and T32069.X_PROD_CD = N'Single'
and T238378.PROD_ROLLUP = N'COREPRODUCT'
and T238378.PROD_CATEGORY_NAME IN ('SolidWorks', 'SolidWorksOffice', 'SolidWorksOfficePremium', 'SolidWorksOfficePro')
group by T238114.[NAME], T238114.ORG_TERR_NAME, T238114.X_AREA, T258637.TOP_LVL_NAME
) D1
INNER JOIN
(select sum(T278171.ACTIVATIONCOUNT) as c6,
count(distinct T31087.X_SEAT_ID) as c7,
T238114.[NAME],
T258637.TOP_LVL_NAME,
T238114.ORG_TERR_NAME
FROM WC_ACTIVATION_F T278171
INNER JOIN WC_EPCR_PROD_CAT_D T238378 ON T238378.ROW_WID = T278171.PROD_CAT_WID
INNER JOIN WC_EPCR_MKTROLLUP_D T238377 ON T238377.ROW_WID = T278171.MKT_WID
INNER JOIN WC_ASSET_ATTR_D T236762 ON T236762.ROW_WID = T278171.ASSET_ATTR_WID
INNER JOIN W_INT_ORG_D T238114 ON T238114.ROW_WID = T278171.VAR_WID /* Var Organization (W_INT_ORG_D) */
INNER JOIN WC_INT_ORG_DH T258637 ON T238114.ROW_WID = T258637.ROW_WID
INNER JOIN W_PRODUCT_D T32069 ON T32069.ROW_WID = T278171.PROD_WID /* Dim_W_PRODUCT_D */
INNER JOIN W_ASSET_D T31087 ON T31087.ROW_WID = T278171.ASSET_WID
where T238377.S_GROUP_ROLLUP = N'C'
and T236762.ATTR1_CHAR_VAL = N'Standard'
and T32069.X_PROD_CD = N'Single'
and T238378.PROD_ROLLUP = N'COREPRODUCT'
and T238378.PROD_CATEGORY_NAME IN ('SolidWorks', 'SolidWorksOffice', 'SolidWorksOfficePremium', 'SolidWorksOfficePro')
group by T238114.[NAME], T238114.ORG_TERR_NAME, T258637.TOP_LVL_NAME
) D2
ON D1.ORG_TERR_NAME = D2.ORG_TERR_NAME
and D1.TOP_LVL_NAME = D2.TOP_LVL_NAME
and D1.[NAME] = D2.[NAME]
) D3
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2008 at 9:40 am
I tried your new query.
Got an error on line 20 saying:
Invalid column name 'X_AREA2'
July 29, 2008 at 9:51 am
Oops sorry, that's always the danger of not having test data to...well, test.
D1.X_AREA2 as c4,
should read
D1.X_AREA as c4,
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2008 at 6:38 am
Yes that did correct the problem of teh syntax error. But after letting it run for 3+ hours I ended up canceling it.
Still trying to determine hoe to create a couple of views and then add indexes to the views to make this query run faster.
I realize the views must be schemabinding, which I was able to do, but because of the count, I cannot add indexes.
I am close to giving up on this query. 🙂
July 31, 2008 at 6:49 am
What about taking each inner query and dumping the results into a table, even if it's a temp table, and then indexing the resulting tables, then running that plus an outer query as some kind of scheduled job? Despite this being a "generated" query, would this particular query change much in the future? If not, then maybe just making it available to it's users another way would work? I'm just thinking that if this one just won't work any other way, then maybe an alternative would be acceptable?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 1, 2008 at 2:55 am
1. You don't seem to have given volumes for the tables involved.
2. There seems to be a couple of table-scans/index-scans in your execution plan. Getting rid of these must be your first priority. You either have no indices supporting those joins (or no indices suitable to be used)
3. We know you have said you can't amend the queries....but given you have the executing SQL code....can you not copy it and break it up into 2 smaller parts - where one pre-calculates and saves into a formal temp the raw working data and then the 2nd part summarises the formal temp table? Are these queries always going to be totally dynamic?
4. Also I note one small transcription error "PROD_CATEGORY_NAME IN ('SolidWorks', 'SolidWorksOffice', 'SolidWorksOfficePremium', 'SolidWorksOfficePro') "...PROD_CATEGORY_NAME according to the table spec is NVARCHAR....yet the code is not looking at N'x' type variables. (This minor difference seems to have been introduced in the re-working of the code above)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply