October 15, 2009 at 7:42 am
Ok, I didn't see the earlier posting of the view. Nothing worrying there, I just wanted to check what exactly it was doing.
Slick84 (10/15/2009)
As far as your recommendation. I did not add the tblProduct one because when I checked the table, the non-clustered index was already there. Below is the script for the index on tblProduct.
CREATE NONCLUSTERED INDEX [IX_tblProduct_1] ON [dbo].[tblProduct]
(
[fintGenderTypeId] ASC,
[fstrShortSKU] ASC,
[fintSiteContentId_Published] ASC,
[fintStyleId] ASC
)
That's not the same as the index I suggested you add. I suggested an index on fstrShortSKU. The above index has fstrShortSKU as the second column, meaning the index is useless for seek operations only on the fstrShortSKU column, which is what you have in this query. You need an index with fstrShortSKU as the leading column. Order of columns in an index matters, a lot.
Please consider adding an index on tblProduct (fstrShortSKU) INCLUDE (fintProductID)
If fintProductID is the clustered index key then leave it out of the include.
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
October 15, 2009 at 8:09 am
Got it. fintProductId is the primary key of table product and the clustered index. I will go ahead and create a non-clustered index on tblProduct for the fstrShortSku column.
Also, is it possible for me to "alter" my non clustered index on tblregularitems and add the 'Include' clause in there?
EDIT:
Okay, I dropped the tblRegularItems index and re-created it with the INCLUDE EDPNO column. I also created a unique non-clustered index on tblProduct.fstrShortSku.
Thanks,
S
--
:hehe:
October 15, 2009 at 8:18 am
Slick84 (10/15/2009)
Also, is it possible for me to "alter" my non clustered index on tblregularitems and add the 'Include' clause in there?
Drop Index
Create Index
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
October 15, 2009 at 8:21 am
Okay Gail, here is my execution plan after making the above changes. Unique non-clustered index on fstrShortsku and EDPno included in index for tblRegularItems.
I also noticed that when my SELECT statement runs, the CPU spikes at a 100%. I'm thinking this could also be the IN clause in my query which seems inefficient to me and thats why I kind of just posted my SELECT statement first here on this board to find out if someone would maybe point that IN clause out and that would kind of confirm my suspicion. However, we obviously went a totally different way and well it turns out it was for the benefit anyway.
Anyhow, back to my post number # 1.. I'm going to post my select statement here, do you think the IN clause could be causing the inefficiency of the server CPU hitting a 100%?
SELECT a.EdpNo,
a.ItemNo,
b.fintProductId,
c.fintSizeId,
d.fintWidthId,
e.fintColorId,
a.ItemStatus,
f.CountryOrigin,
'0'
FROM[BMBStaging].[dbo].[vwRegularItems]a
LEFT JOIN[DB_FOOTSMART_PROD].[dbo].[tblProduct]bON a.StyleCd = b.fstrShortSku
--LEFT JOIN[DB_FOOTSMART_PROD].[dbo].[ltblSize]cON a.Level1DescConv = c.fstrSizeValue
LEFT JOIN[DB_FOOTSMART_PROD].[dbo].[ltblSize]cON a.Level1Desc = c.fstrSizeValue
LEFT JOIN[DB_FOOTSMART_PROD].[dbo].[ltblWidth]dON a.Level2Desc = d.fstrWidthValue
LEFT JOIN[DB_FOOTSMART_PROD].[dbo].[ltblcolor]eON a.Level3Desc = e.fstrColorName
LEFT JOIN[BMBStaging].[dbo].[vwCountryOfOrigin]fON a.EdpNo = f.Edpno
WHEREa.EdpNo IN
(
SELECTri.EdpNo
FROM[BMBStaging].[dbo].[vwRegularItems] ri
LEFT JOIN[DB_FOOTSMART_PROD].[dbo].[tblproductsizewidthcolor] pswc
ONri.EdpNo=pswc.fintEcometryId
WHEREpswc.fstrLongSku IS NULL AND pswc.fintEcometryId IS NULL AND Level1Type = 'SZ'
AND Level2Type = 'WD' AND Level3Type = 'CO'
)
And could I write the above more efficiently without using my IN clause subquery?
Thanks,
S
--
:hehe:
October 15, 2009 at 9:41 am
Slick84 (10/15/2009)
Okay Gail, here is my execution plan after making the above changes. Unique non-clustered index on fstrShortsku and EDPno included in index for tblRegularItems.
Excellent. This looks about as good as it can get. Grant, you agree?
I also noticed that when my SELECT statement runs, the CPU spikes at a 100%.
Single proc machine?
That's kinda expected. SQL's trying to process the query as fast as possible. There's no sense in SQL leaving the processor idle half the time when it has a query to process. It'll spike to 100 and then drop down as soon as the query's finished.
How long does this run now?
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
October 15, 2009 at 11:23 am
It runs almost instantly.. However, I have the same environment setup on my QA server (I did what you advised on our Staging environment) and the query has been running for almost 1 hour and 30 minutes even with the indexes. I'm waiting for this to complete so maybe I can post the execution plan here (if it completes).
And to clarify, the spike to a 100% is for the QA environment where its been running for so long before I created your recommended indexes on QA. However, after the creation of indexes, its been around 50~60% but still running. The QA server is a virtual server so its naturally slow.
Thanks,
S
--
:hehe:
October 15, 2009 at 1:33 pm
GilaMonster (10/15/2009)
Slick84 (10/15/2009)
Okay Gail, here is my execution plan after making the above changes. Unique non-clustered index on fstrShortsku and EDPno included in index for tblRegularItems.Excellent. This looks about as good as it can get. Grant, you agree?
I also noticed that when my SELECT statement runs, the CPU spikes at a 100%.
Single proc machine?
That's kinda expected. SQL's trying to process the query as fast as possible. There's no sense in SQL leaving the processor idle half the time when it has a query to process. It'll spike to 100 and then drop down as soon as the query's finished.
How long does this run now?
Yeah, that's a sweet looking execution plan now. It'd be hard to squeeze more out of it.
Sorry I've been away. Lots of meetings today.
"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
October 15, 2009 at 3:19 pm
I'll take a stab at your question regarding the subquery, although I have to admit that I'm not as experienced or as talented as Gail and Grant.
I believe that you can save yourself a little bit by refactoring your code to remove the subquery. Particularly the distinct operation may be made faster, and you might save some time spent seeking on the tblProductSizeWidthColor.
Just to make sure that i'm 100% clear what you're attempting to do with the subquery, let me attempt to put it in english, and see if that matches with what you thought you were doing:
Ignoring all the tables but tblRegularItems and tblProductSizeWidthColor,
You are selecting the regularItems that do not have an entry in tblProductSizeWidthColor, or that have an entry in tblProductSizeWidthColor with a Null fstrLongSku, and that do have the constant values that you specified for the columns levelsXtype in the
If that is a correct interpretation, we can rewrite your where clause as:
WHERE a.Level1Type = 'SZ'
AND a.Level2Type = 'WD'
AND a.Level3Type = 'CO'
AND a.EdpNo NOT IN
(
SELECT pswc.fintEcometryId
FROM [2-Database-2].[dbo].[tblproductsizewidthcolor] pswc
WHERE pswc.fstrLongSku IS NOT NULL
)
The benefit that this gives you is the optimizer should be able to move the distinct operation above the join between regularitems and productsizewidthColor, hopefully speeding up both operations.
Now it is possible (based on the name of tblProductSizeWidthColor) that the results you're getting are not exactly what you meant to be querying, which I think is what Grant was getting at in his comment about the possible design issue.
It's also possible that you didn't need the check pswc.fstrLongSku IS NOT NULL, which would allow you to remove that column from your index, speeding up inserts and updates (maybe).
I hope my analysis is correct and helpful.
-a.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
October 15, 2009 at 3:27 pm
Weitzera,
Thanks for the input. You definately are making sense and yes the requirement you described is how I want it. I'm basically inserting records from tblRegularItems into tblProductSizeWidthColor which do not already exist in tblProductSizeWidthColor. And yes, I commented out the line for the fstrLongSku IS NOT NULL.. as we don't require that here as that's already being compared in the "larger' query above.
Im going to try this now and get back to you on it.
Thanks,
S
--
:hehe:
October 15, 2009 at 4:09 pm
No Problem.
One other thing I noticed: You've hard-coded which size, width and color you want to use, yet you then proceed to look up the ids for those values. Why don't you drop the joins to the size, width, and color tables, and simply hard code the ids? If this is part of a larger procedure, you should still be able to look up the ids for those values once, instead of doing it for each row.
-a.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
October 16, 2009 at 1:20 am
Hi Slick84,
can u replace the IN with some inner join? I hope this will give a better performance.
October 16, 2009 at 6:42 am
I'm not sure how I could implement the inner join when I'm only looking for items which are available in my table on the left or my first table compared to the right table or my second table..
--
:hehe:
October 16, 2009 at 6:45 am
Slick84 (10/15/2009)
Weitzera,Thanks for the input. You definately are making sense and yes the requirement you described is how I want it. I'm basically inserting records from tblRegularItems into tblProductSizeWidthColor which do not already exist in tblProductSizeWidthColor. And yes, I commented out the line for the fstrLongSku IS NOT NULL.. as we don't require that here as that's already being compared in the "larger' query above.
Im going to try this now and get back to you on it.
Thanks,
S
Actually its not hard-coded... I'm getting those values using a left join comparing the values from the lookup tables to my tblregularitems if you notice. In the where clause, all I'm doing is applying the filter specifying the "types" as they can change sometimes because our legacy system where we pull data from has no standards in place which makes us implement too many work-arounds. Pretty annoying if you ask me, but I guess thats why the DML exists. To manipulate things of this sort...
Anyhow, I tried running your query and it ran endlessly for 2~3 hours with no results. I am not sure what's going on even with the indexes created. This same thing runs pretty fast on the Staging environment but not on the Qa environment.
I also have this already running in production and it runs successfully everyday. I guess i'll keep troubleshooting this.
Thanks,
S
--
:hehe:
October 16, 2009 at 7:24 am
Slick84 (10/16/2009)
Slick84 (10/15/2009)
Weitzera,Thanks for the input. You definately are making sense and yes the requirement you described is how I want it. I'm basically inserting records from tblRegularItems into tblProductSizeWidthColor which do not already exist in tblProductSizeWidthColor. And yes, I commented out the line for the fstrLongSku IS NOT NULL.. as we don't require that here as that's already being compared in the "larger' query above.
Im going to try this now and get back to you on it.
Thanks,
S
Actually its not hard-coded... I'm getting those values using a left join comparing the values from the lookup tables to my tblregularitems if you notice. In the where clause, all I'm doing is applying the filter specifying the "types" as they can change sometimes because our legacy system where we pull data from has no standards in place which makes us implement too many work-arounds. Pretty annoying if you ask me, but I guess thats why the DML exists. To manipulate things of this sort...
Anyhow, I tried running your query and it ran endlessly for 2~3 hours with no results. I am not sure what's going on even with the indexes created. This same thing runs pretty fast on the Staging environment but not on the Qa environment.
I also have this already running in production and it runs successfully everyday. I guess i'll keep troubleshooting this.
Thanks,
S
Have you updated statistics in the QA environment?
"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
October 16, 2009 at 7:30 am
I havent done that Grant...let me try that.
--
:hehe:
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply