August 19, 2009 at 3:43 am
Hi all
I have a sp which simply runs a query that has 5 joins between 5 table and a view. I have required Indexes on tables (not on the view).
when I run the query it takes only 1 sec to return the result but when I run it in the SP it take 22 second!!!
I dont know where the problem is. please help!
thankx in advance.
August 19, 2009 at 3:49 am
it seems like this is a case of parameter sniffing where the optimiser wrongly guesses the parameters for the stored procedure.
http://www.sqlservercentral.com/blogs/ken_kaufman/archive/2007/11/28/3289.aspx"> http://www.sqlservercentral.com/blogs/ken_kaufman/archive/2007/11/28/3289.aspx
August 19, 2009 at 4:14 am
thanx for ur reply. I read the article.
I think that there is something wrong with parameter, because when I run the query with parameter it takes 22 secs to run.
here is my query:
declare @ID1 as uniqueIdentifier
Set @ID1 = '3f082c39-c69f-472d-9e56-0f23f82e2326'
SELECT DISTINCT FL.* FROM tb_FirstLayer FL
LEFT JOIN tb_SecondLayer SL on SL.FirstLayerID = FL.ID
LEFT JOIN tb_PatternJob PJ on PJ.SecondLayerID = SL.ID
LEFT JOIN tb_Matrix M on M.PatternJobID = PJ.ID
LEFT JOIN tb_JobCategoryMaster JCM on JCM.JobCategoryPatternID = M.JobCategoryPatternID
LEFT JOIN JobCategoryRecord JCR on JCR.MasterID = JCM.ID
Where JCR.ID = @ID1
OrderByFL.Title
August 19, 2009 at 4:19 am
Please post query plans as per this article
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
It will take the guess work out of it.
August 19, 2009 at 4:49 am
here is the execution plan
August 19, 2009 at 4:57 am
That will be for the Query version , not the stored procedure , correct ?
August 19, 2009 at 5:02 am
yes. Stored Procedure didnt cause the problem, in my opinion. the Parameter that i pass to the query cause the problem.
August 19, 2009 at 5:29 am
What Does
declare @ID1 as uniqueIdentifier
Set @ID1 = '3f082c39-c69f-472d-9e56-0f23f82e2326'
SELECT *
from JobCategoryRecord JCR
Where JCR.ID = @ID1
do in terms of a query plan?
JobCategoryRecord is a view on tb_JobCategory where deleted = 0 , but it would seem to be filtering the result of rows that match that rather than seeking on the ID.
Im assuming that there is an index on ID.
Also check that statistics are up to date and use sp_recompile to ensure you are not using a cached plan
August 19, 2009 at 6:01 am
sorry, I didn't get u. view JobCategroryRecord doesn't have any Index. but All tables have a primary key (ID). and tables which are participate in joins have nonClustered index on the related feild.
August 19, 2009 at 6:36 am
What dont you get ?
I was just pointing out that the query plan does look strange .
More often than not this is caused but out of date stats, and that is certainly the first thing to do, ie update them.
There are no actual row counts inside it , was it an estimated plan ?
Can you post the 'actual' plan for the simple query i gave you ?
August 22, 2009 at 2:47 am
sorry for late reply. this is the actual plan for the second query. the previous plan was an estimated plan.
August 24, 2009 at 1:48 am
That plan looks pretty much ok , index seeks all the way through , Actual row counts near estimated.
Can you post the actual plan for the full query now ?
August 24, 2009 at 4:42 am
Thank u for following up.
problem solved! Ichange query to below form and it runs in less than a second.
SELECT * FROM tb_FirstLayer AS FL
WHERE EXISTS(SELECT * FROM tb_SecondLayer AS SL
WHERE SL.FirstLayerID = FL.ID AND
EXISTS(SELECT * FROM tb_PatternJob AS PJ
WHERE PJ.SecondLayerID = SL.ID AND
EXISTS (SELECT * FROM tb_Matrix AS M
WHERE M.PatternJobID = PJ.ID AND
EXISTS (SELECT * FROM tb_JobCategoryMaster AS JCM
WHERE JCM.JobCategoryPatternID = M.JobCategoryPatternID AND
EXISTS (SELECT * FROM JobCategoryRecord AS JCR
WHERE JCR.MasterID = JCM.ID AND
JCR.ID=@ID)
)
)
)
)
Order By FL.Title
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply