SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Performance Issue


Query Performance Issue

Author
Message
Slick84
Slick84
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 1163
Hello,

I have a query below which used to run in less than a few seconds before but runs for forever (max I watched it run was for 50 or so minutes).

Can anyone look at this and tell me if they see something wrong? I honestly dont see anything wrong in it and have only made a simple change which shouldnt effect the performance in such a huge way.

Please see below:

SELECT a.EdpNo,
a.ItemNo,
b.fintProductId,
c.fintSizeId,
d.fintWidthId,
e.fintColorId,
a.ItemStatus,
f.CountryOrigin,
'0'
FROM [1-Database-1].[dbo].[vwRegularItems] a
LEFT JOIN [2-Database-2].[dbo].[tblProduct] b ON a.StyleCd = b.fstrShortSku
--LEFT JOIN [2-Database-2].[dbo].[ltblSize] c ON a.Level1DescConv = c.fstrSizeValue
LEFT JOIN [2-Database-2].[dbo].[ltblSize] c ON a.Level1Desc = c.fstrSizeValue
LEFT JOIN [2-Database-2].[dbo].[ltblWidth] d ON a.Level2Desc = d.fstrWidthValue
LEFT JOIN [2-Database-2].[dbo].[ltblcolor] e ON a.Level3Desc = e.fstrColorName
LEFT JOIN [1-Database-1].[dbo].[vwCountryOfOrigin] f ON a.EdpNo = f.Edpno
WHERE a.EdpNo IN
(
SELECT ri.EdpNo
FROM [1-Database-1].[dbo].[vwRegularItems] ri
LEFT JOIN [2-Database-2].[dbo].[tblproductsizewidthcolor] pswc
ON ri.EdpNo=pswc.fintEcometryId
WHERE pswc.fstrLongSku IS NULL AND pswc.fintEcometryId IS NULL AND Level1Type = 'SZ'
AND Level2Type = 'WD' AND Level3Type = 'CO'
)



The change I made was adding another LEFT JOIN and commenting one out. Any input would be appreciated.

Thanks,
S

--
Hehe
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2952 Visits: 8370
Please see the links in my signature below



Clear Sky SQL
My Blog
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40731 Visits: 32666
Can you post the execution plans?

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88667 Visits: 45284
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Slick84
Slick84
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 1163
The execution plan is attached. Thanks for the replies and any help will be appreciated. The query ran for 40 minutes before it provided me the execution plan and 622 results. The table it looks into (tblRegularItems) has a total of 600K+ row to which I've created a view called VwRegularItems which shows only the "active" records through a flag I have put in there. The vwRegularItems view returns about 126k rows from the tblRegularItems.

DDL for view below:


CREATE VIEW [dbo].[vwRegularItems]
AS
SELECT Item_Id,
MinorCatCd,
SubMinorCatCd,
StyleCd,
ItemNo,
EDPNo,
ItemStatus,
StyleDesc,
OfferPrice,
ItemDesc,
Price,
Level1Type,
Level1Desc,
Level1DescCONV,
Level2Type,
Level2Desc,
Level3Type,
Level3Desc,
InvQty,
Date,
IsArchived,
ArchiveDate,
DateCreated
FROM [BMBStaging].[dbo].[tblRegularItems]
WHERE isArchived = 0



DDL for index on tblRegularItems below:

ALTER TABLE [dbo].[tblRegularItems] ADD CONSTRAINT [IX_tblRegularItems_EdpNo] UNIQUE NONCLUSTERED
(
[EDPNo] ASC,
[ArchiveDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



Thanks again!
-s

--
Hehe
Attachments
query_exec_plan.sqlplan (32 views, 75.00 KB)
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40731 Visits: 32666
You're getting a scan on the tblProductSizeWidthColor table, from the name alone, I think you've violated some design principals, which is reading, if I counted the digits correctly 332 million rows which gets filtered down to 600 for the result set. I suspect that is one of the largest problems you're dealing with.

You're also getting a scan on tblRegularItems.

But the biggest problem, I think, is statistics. The estimated number of rows for tblRegularItems is 1,but the actual is 127000+. That's a huge disparity. There's also an index seek against tblRegularItems that is showing estimated 19 rows, but actual 2.5 million. I'd suggest updating your stats on all the tables, and I'd suggest using FULL SCAN to do it.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Slick84
Slick84
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 1163
Grant,

Thank you for the information. Can you possibly point me to the right direction for updating stats on the tables as well as more information for "Full Scans". I will look this up on Google myself as well.

Also, can I update the statistics on these tables in Production during regular business hours? How much effect would updating the statistics have on the database load wise?

Thanks,
S

--
Hehe
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40731 Visits: 32666
Slick84 (10/14/2009)
Grant,

Thank you for the information. Can you possibly point me to the right direction for updating stats on the tables as well as more information for "Full Scans". I will look this up on Google myself as well.

Also, can I update the statistics on these tables in Production during regular business hours? How much effect would updating the statistics have on the database load wise?

Thanks,
S


It could cause issues running it during the day. Normally you can update stats with sp_updatestats, but I think you might need a complete scan done on these based on the wide disparity between the values. To use UPDATE STATISTICS, you just have to run the following for each table:

UPDATE STATISTICS schema.table WITH FULLSCAN



----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Slick84
Slick84
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 1163
So another question, and please let me know if I'm asking too much or just point me to the right direction but...

Would you be able to explain what the wide disparity between the two numbers signifies?

--
Hehe
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88667 Visits: 45284
Slick84 (10/14/2009)
Would you be able to explain what the wide disparity between the two numbers signifies?


The optimiser estimates one row and hence generates a plan that's optimal for a very small number of rows. 125000 is not a small number of rows and hence the plan is very, very sub-optimal.

You have table scans everywhere! And as the inner sources for nested loop joins!!!!! No wonder this is slow. In fact, I'm surprised it only takes 40 min.

Quick suggestions (as in, I looked over it quickly, not as in they're quick for you to implement)

Index on tblRegularItems (IsArchived, Level1Type, Level2Type, Level3Type, EDPNo)
Index on tblProductSizeWidthColour (fstrLongSKU, fintEcometryID) (btw, what does this table store?)
Index on tblProduct (fstrShortSKU)
Index on ltblSize (fstrSizeValue)
Index on ltblWidth (fstrWidthValue)
Index on ltblColour (fstrColourName)

Once you've added all those, run the query again and post the revised exec plan.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search