June 13, 2012 at 3:56 am
Hello Guys,
Plase maybe you help me with this query whish last more than 2 hours and starved the tempdb.
SELECT 2036 AS ANNEE, '2020_05' AS SCENARIO, REGION,
SUM(T1.NUM_OPS * COALESCE(T15.PCT_JETS, 100.0) / 100.0 * T6.DISTRIB_PCT / 100.0) * 8.69 AS OPERATIONS,
SUM((ARR.FUEL * COALESCE(T10.APP_FUEL_ADJUSTMENT_FACTOR_LOW, 1.0) + DEP.FUEL * COALESCE(T10.DEP_FUEL_ADJUSTMENT_FACTOR_LOW, 1.0)) * COALESCE(T11.GHG_FUEL_ADJUSTMENT, 1.0) * T1.NUM_OPS * COALESCE(T15.PCT_JETS, 100.0) / 100.0 * T6.DISTRIB_PCT / 100.0 * 8.69 * (0.999 + 1.703 * POWER(GRT_CIRC_DIST, - 0.522))) / 1000000000.0 AS FUELBURN_LOW,
SUM((ARR.NOX * COALESCE(T10.APP_FUEL_ADJUSTMENT_FACTOR_LOW, 1.0) + DEP.NOX * COALESCE(T10.DEP_FUEL_ADJUSTMENT_FACTOR_LOW, 1.0)) * COALESCE(T11.GHG_NOX_ADJUSTMENT, 1.0) * T1.NUM_OPS * COALESCE(T15.PCT_JETS, 100.0) / 100.0 * T6.DISTRIB_PCT / 100.0 * 8.69 * (0.999 + 1.703 * POWER(GRT_CIRC_DIST, - 0.522))) / 1000000000.0 AS NOX_LOW,
SUM((ARR.FUEL * COALESCE(T10.APP_FUEL_ADJUSTMENT_FACTOR_HIGH, 1.0) + DEP.FUEL * COALESCE(T10.DEP_FUEL_ADJUSTMENT_FACTOR_HIGH, 1.0)) * COALESCE(T11.GHG_FUEL_ADJUSTMENT, 1.0) * T1.NUM_OPS * COALESCE(T15.PCT_JETS, 100.0) / 100.0 * T6.DISTRIB_PCT / 100.0 * 8.69 * (0.999 + 1.703 * POWER(GRT_CIRC_DIST, - 0.522))) / 1000000000.0 AS FUELBURN_HIGH,
SUM((ARR.NOX * COALESCE(T10.APP_FUEL_ADJUSTMENT_FACTOR_HIGH, 1.0) + DEP.NOX * COALESCE(T10.DEP_FUEL_ADJUSTMENT_FACTOR_HIGH, 1.0)) * COALESCE(T11.GHG_NOX_ADJUSTMENT, 1.0) * T1.NUM_OPS * COALESCE(T15.PCT_JETS, 100.0) / 100.0 * T6.DISTRIB_PCT / 100.0 * 8.69 * (0.999 + 1.703 * POWER(GRT_CIRC_DIST, - 0.522))) / 1000000000.0 AS NOX_HIGH
FROM CAEP9_NOISE_STRINGENCY_ROUND2_S5.dbo.OPERATION_6WKS_2036_2020_05 T1
INNER JOIN OP_KEY_bis T2 ON T1.OP_KEY_ID = T2.OP_KEY_ID
INNER JOIN CAEP9_NOISE_STRINGENCY_ROUND2_S5.dbo.AIRCRAFT_REPLACEMENT_2020_05 T6 ON T1.SEAT_CLASS_ID = T6.SEAT_CLASS_ID AND T2.STAGE_LENGTH_ID = T6.STAGE_LENGTH_ID AND T1.REC_CODE = T6.REC_CODE AND T2.CIS_MARKET_SHARE = T6.CIS_MARKET_SHARE AND T2.USAGE_TYPE = T6.USAGE_TYPE
INNER JOIN AEM_out_DEP2 DEP ON T2.DEP_APT_ID = DEP.DEP_APT_ID AND T2.ARR_APT_ID = DEP.ARR_APT_ID AND T6.ACCODE = DEP.ACCODE AND T6.ENG_CODE = DEP.ENG_CODE AND T6.ENG_MOD = DEP.ENG_MOD
INNER JOIN AEM_out_ARR2 ARR ON T2.DEP_APT_ID = ARR.DEP_APT_ID AND T2.ARR_APT_ID = ARR.ARR_APT_ID AND T6.ACCODE = ARR.ACCODE AND T6.ENG_CODE = ARR.ENG_CODE AND T6.ENG_MOD = ARR.ENG_MOD
LEFT JOIN DATUM_JETS_PROPS_DISTRIBUTION T15 ON T1.SEAT_CLASS_ID = T15.SEAT_CLASS_ID AND T2.STAGE_LENGTH_ID = T15.STAGE_LENGTH_ID AND T1.REC_CODE = T15.REC_CODE AND T2.CIS_MARKET_SHARE = T15.CIS_MARKET_SHARE AND T2.USAGE_TYPE = T15.USAGE_TYPE
LEFT JOIN EMISSIONS_ADJUSTMENT T10 ON T6.ACCODE = T10.ACCODE AND T6.ENG_CODE = T10.ENG_CODE AND T6.ENG_MOD = T10.ENG_MOD AND T6.TECH_LEVEL = T10.TECH_LEVEL AND T6.STRINGENCY_ID = T10.STRINGENCY_ID
LEFT JOIN PROJECT_AIRCRAFT_FUEL_NOX_ADJUSTMENT T11 ON T6.ACCODE = T11.ACCODE --AND T6.ENG_CODE = T11.ENG_CODE AND T6.ENG_MOD = T11.ENG_MOD
WHERE T1.REC_CODE <> 'O' AND T6.ENGINE_TYPE = 'J'
GROUP BY REGION
I have created filtered indexes,covering idexes but no improve.
What I have to change inside the query to speed it?
June 13, 2012 at 3:58 am
Can you post table definitions, index definitions and sample data for all tables involved? Also if you have it the execution plan.
Please see the second and forth links in my signature should you need any help with it.
June 13, 2012 at 5:56 am
Yeah, we'd at least need the execution plan to understand what's going on. Nothing jumped out as horribly egregious. The GROUP BY will put more load on tempdb depending on what was needed to satisfy it. But I'd just be guessing without the execution plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2012 at 6:10 am
Hi,
Actually the query run more than 1,5 H and it not g=fiished due the tempdb allocations ,I could isert only estimated exec plan.
Best Regards,
hadrian
June 13, 2012 at 6:31 am
Whoa! That's cool. You have 9.9 billion rows being aggregated down to 12. I think you need to put filters in place, a WHERE clause, to limit the number of rows you're returning. If you look at the plan, you've got multiple clustered index scans and a table scan (heap, not generally recommend) where these tables are returning all their rows. You've also got three different SORT operations reording the data over & over so that the server can use MERGE joins. All of that is going to take place in the tempdb. I looked at one of them and it's sorting 800 million rows. Oh, and since these are all estimates, it's possible that the actuals are even bigger. It depends on how up to date your statistics are.
In short, this stuff is killing you. You need to filter the data down. If you only need 12 rows of data, there must be a different way to gather it. Or maybe you need to pre-aggregate it in some fashion (although, depending on how you do the pre-aggregation, you could still be looking at the same issue). Another approach is to break the query down into smaller chunks and load them into table variables. Anything but all these scans & sorts against billions of rows all at the same time. That's the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2012 at 6:42 am
Grant Fritchey (6/13/2012)
Yeah, we'd at least need the execution plan to understand what's going on. Nothing jumped out as horribly egregious. The GROUP BY will put more load on tempdb depending on what was needed to satisfy it. But I'd just be guessing without the execution plan.
Does this query generate correct results?
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
June 14, 2012 at 1:41 am
Hi,
It's a query developped by another and it told me that it returns something.
June 14, 2012 at 1:55 am
Hadrian (6/14/2012)
Hi,It's a query developped by another and it told me that it returns something.
It returns 6 or 12 rows, I forget which - but are the results correct?
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
June 14, 2012 at 4:16 am
12 rows are returned after 5 hours run.
June 14, 2012 at 4:24 am
Hadrian (6/14/2012)
12 rows are returned after 5 hours run.
Are the results correct or not?
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
June 14, 2012 at 6:13 am
the results are correct
June 14, 2012 at 6:26 am
actually I'm interested to rewrite the query avoiding the huge sorts which destroy the performance.
June 14, 2012 at 6:40 am
It's not just the sorts though. You're scanning hundreds of millions of rows. The sorts are making things worse, yes, but the sorts are setting you up for merge joins, which are actually some of the most efficient joins. If you didn't have merge joins you'd be seeing hash match joins and these would be hitting the tempdb just as hard, if not harder, than your sorts. The primary issue here is the volume of data that you're trying to filter down to 12 rows.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2012 at 7:47 am
may you want to see some sample data?
June 14, 2012 at 9:38 am
10 billion rows... very impressive.
So, let's take a look at your where clause:
WHERE T1.REC_CODE <> 'O' AND T6.ENGINE_TYPE = 'J'
T6 = CAEP9_NOISE_STRINGENCY_ROUND2_S5.dbo.AIRCRAFT_REPLACEMENT_2020_05, and you're doing a table scan on in. This means you don't have a clustered index on it either (it's a heap), and I'd be willing to bet that you don't have any other indexes. So, I suggest putting a CLUSTERED INDEX on the ENGINE_TYPE column.
T1 = CAEP9_NOISE_STRINGENCY_ROUND2_S5.dbo.OPERATION_6WKS_2036_2020_05. An inequality search is usually inefficient. Do you have a list of values that this can be? If so, substitute the <> 'O' for an IN(<list of values>).
T10 = EMISSIONS_ADJUSTMENT, and you're doing a table scan here also. This table is also being joined to T6 (T6.ACCODE = T10.ACCODE AND T6.ENG_CODE = T10.ENG_CODE AND T6.ENG_MOD = T10.ENG_MOD AND T6.TECH_LEVEL = T10.TECH_LEVEL AND T6.STRINGENCY_ID = T10.STRINGENCY_ID). Additionally, most of your SUMS include columns in this table. Ensure that you have a proper index on the columns being joined, and that the columns included in the SUMS are an INCLUDED column in that index.
You have just one seek, on T1 (CAEP9_NOISE_STRINGENCY_ROUND2_S5.dbo.OPERATION_6WKS_2036_2020_05). You need to check all of the other tables for proper indexes.
Edit: The only scan going on that I would accept is on the PROJECT_AIRCRAFT_FUEL_NOX_ADJUSTMENT table - it's only returning 24 rows, and a scan could very well be the best way to load those records in. If the table is small enough, it just might fit on one page.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply