November 30, 2011 at 3:37 pm
I have a query which is referencing queries which are referencing queries etc.
I am using execution plan to find out where it is taking the most time. Someone said to me you could use this to then create an index.
Could any one explain in simple terms what is the best way of doing this.
November 30, 2011 at 3:49 pm
Post the actual (not estimated) execution plan for the query you want to optimize along with the table/index definitions for the referenced tables, and we'll be glad to walk you through the tuning process.
December 1, 2011 at 5:11 am
I'm sorry to say, there's no easy way to explain this. I've written two different books, one on query tuning and one on reading execution plans. There's that much material there.
The shortest possible way to explain this, and it will be inadequate, is that you get the execution plan, and look for the most costly operation. Determine why that operation is being used and figure out if an index can help. But that doesn't cover hardly any of the issues such as, what if the most costly operation is a table spool? Will an index help? Possibly, or more probably, you need to rearchitect your code.
Query tuning is one of the harder parts of SQL Server. That's why so much has been written about it. My execution plans book can be downloaded for free. Just do a search.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 1, 2011 at 5:39 am
Google for the book or hit this download page 😉
December 2, 2011 at 1:44 am
Sorry to be a while coming back. I have had a problem with my web.
Execution plan attached. If you walk me through this one then it will give me a starting block. I can then swot up in the book mentioned.
Thanks
December 2, 2011 at 1:55 am
Not able to view the plan attached. May be you need to attach it again.
December 2, 2011 at 2:47 am
Divine Flame (12/2/2011)
Not able to view the plan attached. May be you need to attach it again.
Sorry, had to comment. L, awesome avatar. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 2, 2011 at 2:50 am
Evil Kraig F (12/2/2011)
Divine Flame (12/2/2011)
Not able to view the plan attached. May be you need to attach it again.Sorry, had to comment. L, awesome avatar. 🙂
Thanks Kraig 🙂
December 2, 2011 at 3:32 am
kyle.doouss (12/2/2011)
Sorry to be a while coming back. I have had a problem with my web.Execution plan attached. If you walk me through this one then it will give me a starting block. I can then swot up in the book mentioned.
Thanks
Thanks for posting the plan. The query itself is pretty straightforward, then a glance at the plan shows what's really going on - there are views on views on views, with the same tables referenced many many times.
Since the query is so simple - it's only a sales report - why not start from scratch with it? Here's the original query with table source aliases to make it a little easier on the eye:
SELECT --TOP (100)PERCENT
'CPR' AS RecordType,
'' AS DeleteFlag,
pug.STP_PROD AS ProductCode,
cug.CUCODE AS CustomerCode,
'' AS AlfaDebtors,
'' AS AddressPricegroupCode,
spmd.STPD_FROM AS FromQuantity,
'' AS DoNotUse1,
round(CASE
WHEN (CASE WHEN spma.STP_DISC > 0 THEN spma.STP_DISC WHEN spmd.STPD_DISC > 0 THEN spmd.STPD_DISC ELSE 0 END) > 0
THEN s.STK_SELLPRICE1 - (s.STK_SELLPRICE1 * ((CASE WHEN spma.STP_DISC > 0 THEN spma.STP_DISC WHEN spmd.STPD_DISC > 0 THEN spmd.STPD_DISC ELSE 0 END) / 100))
WHEN (CASE WHEN spma.STP_FIXED > 0 THEN spma.STP_FIXED WHEN spmd.STPD_FIXED > 0 THEN spmd.STPD_FIXED ELSE 0 END) > 0
THEN (CASE WHEN spma.STP_FIXED > 0 THEN spma.STP_FIXED WHEN spmd.STPD_FIXED > 0 THEN spmd.STPD_FIXED ELSE 0 END)
ELSE s.STK_SELLPRICE1 END,2) AS Price,
'' AS CurrencyCode,
'' AS ContractPrice,
'' AS CatalogCode,
'' AS Supplier,
'' AS OrderUOM,
'' AS DoNotUse2,
'' AS Reserved,
'' AS ProductIDSupplierLong,
'' AS CustomerCodeAlfa,
'' AS AddressPricegroupCode_Long,
'' AS FreeForUse
FROM UDEF_WEB_PRODUCTS_ATTACHED_TO_PRICELISTS_UNION_INCLUDE_GROUPED pug
INNER JOIN UDEF_WEB_PRODUCTS_UPLOADED pu
ON pug.STP_PROD = pu.ProductCode
INNER JOIN STK_PRICE_MATRIX_ADV spma
ON pug.STP_PRIMARY = spma.STP_PRIMARY
INNER JOIN STK_PRICE_MATRIX_DET spmd
ON spma.STP_PRIMARY = spmd.STPD_HEADER
INNER JOIN UDEF_WEB_CUSTOMERS_ATTACHED_TO_PRICELISTS_UNION_GROUPED cug
ON pug.STP_PARENT = cug.STH_PRIMARY
INNER JOIN UDEF_WEB_CUSTOMERS_UPLOADED cu
ON cug.CUCODE = cu.CustomerCode
INNER JOIN STK_STOCK_2 s
ON pu.ProductCode = s.STKCODE2
INNER JOIN UDEF_WEB_PRICELISTS_ACTIVE pla
ON spma.STP_PARENT = pla.STH_PRIMARY
LEFT OUTER JOIN UDEF_WEB_PRODUCTS_ATTACHED_TO_PRICELISTS_UNION_EXCLUDE_GROUPED peg
ON pug.INCLUDE_CODE = peg.EXCLUDE_CODE
WHERE
--CASE WHEN peg.EXCLUDE_CODE <> '' THEN 'EXCLUDE' ELSE 'INCLUDE' END = 'INCLUDE' -- silly
peg.EXCLUDE_CODE = ''
and pla.STH_NAME not in('COMMERCIAL & EDUCATIONAL WHITE PAPER', 'EDU CAT (Excl. Wht Pap & Kas) Educ & Com',
'EDU CAT KASKAD (Educ & Comercial)')
and cug.CUCODE ='EFIRS004'
ORDER BY spmd.STPD_DISC DESC
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
December 2, 2011 at 3:56 am
Thanks for that. Something you might be able to help me with is if you look at a view in design it messes up the format.
Is there a quick and easy way to reformat it so it is easier on the eye?
December 2, 2011 at 4:30 am
Don't use the GUI, use scripts in SSMS..
December 2, 2011 at 4:43 am
Sorry that's all new to me. How do I know what I am running in?
December 2, 2011 at 4:51 am
kyle.doouss (12/2/2011)
Thanks for that. Something you might be able to help me with is if you look at a view in design it messes up the format.Is there a quick and easy way to reformat it so it is easier on the eye?
Sorry, I've no idea, never used it.
Are you relying quite heavily on the view designer for creating your SQL? If so, I'd recommend against it. If this query was rewritten by hand referencing the base tables rather than views, it would be super-simple and fast too. In the majority of cases, views are created (sparingly) to reduce duplication and introduce standards. There aren't many tables involved here, and, assuming the mega-view-query generates the correct results, you have a result set as a defined target as well as the table sources for the columns and the joins between the tables - why not give it a shot and write the query in SSMS freehand?
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
December 2, 2011 at 4:56 am
kyle.doouss (12/2/2011)
Sorry that's all new to me. How do I know what I am running in?
SSMS= management studio... scripting window
GUI = graphical user interface, like the edit view GUI.
December 2, 2011 at 5:14 am
That is a particularly hairy execution plan.
There a few issues. First, it looks like your statistics might be off. Down in the really nasty part of the plan estimated row counts are for 87,000 and actuaion are 500,000. That's not a wild disparity, but it's clearly off. This is causing the optimizer to make the choice of a loop join and then it's hitting an index seek 500,000 times (22% of the estimated cost down there on sluaccounts.cucode). I'm not sure you've got good indexes on STK_STOCK. I can't see the structure, but it looks like the three indexes, STK_SORT_KEY, 1, 2, all seem to have the same predicate, HBSLIVE].[dbo].[STK_PRICE_MATRIX_ADV].[STP_PROD and output. Then, they have a key lookup operation for one column, [HBSLIVE].[dbo].[STK_STOCK].STKCODE. I'd make that an include index and a HUGE chunk of this would be solved. You still need to address the nested views. That's a VERY notoriously bad way to run things.
On that note, I've saved the worst news for last. Your query is timing out in the optimizer. That means this entire plan is nothing more than a vague guess at what might be the right execution plan. The timeout is probably occurring due to the excessive complexity brought on by the nested views. To fix this, indexing can help, but you really need to rearchitect away from the pattern you're in.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply