Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Query Performance Issue Expand / Collapse
Author
Message
Posted Wednesday, October 14, 2009 8:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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


--
Post #802799
Posted Wednesday, October 14, 2009 8:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,949, Visits: 8,318
Please see the links in my signature below



Clear Sky SQL
My Blog
Kent user group
Post #802803
Posted Wednesday, October 14, 2009 9:52 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #802884
Posted Wednesday, October 14, 2009 10:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #802905
Posted Wednesday, October 14, 2009 12:33 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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


--


  Post Attachments 
query_exec_plan.sqlplan (27 views, 75.34 KB)
Post #803021
Posted Wednesday, October 14, 2009 1:03 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #803037
Posted Wednesday, October 14, 2009 1:11 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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


--
Post #803046
Posted Wednesday, October 14, 2009 1:23 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #803058
Posted Wednesday, October 14, 2009 1:32 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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?


--
Post #803060
Posted Wednesday, October 14, 2009 1:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #803071
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse