asked
Proc running longer for different spoc_setcontextinfo
link text [link text][1] Hi! I have the following proc with different inputs for the spoc_setcontextinfo. With User1 the result is returned in no more than 5 sec. With User2 the result is returned in approx 24 sec. Looking at the Execution plan I see that the index seeks/scans for User2 actual number of rows is almost 17000 but for User 1 the actual number of rows is less than 100. If I remove the TOP 21 to select all records both return results in 2 secs. User2 returns 113 rows and User1 returns 5274. The indexes on the tables have been rebuilt and reorganized. Any suggestions on the disparity and how to resolve it? Thanks in Advance! G exec spoc_setcontextinfo 'User1', 5 go SELECT TOP 21 [OC_HROpCo SU Hybrid_PEOPLE_READ].PEOPLE_ID, [OC_HROpCo SU Hybrid_PEOPLE_READ].REFERENCENUMBER, [OC_HROpCo SU Hybrid_PEOPLE_READ].FIRSTNAME, [OC_HROpCo SU Hybrid_PEOPLE_READ].LASTNAME, [OC_HROpCo SU Hybrid_PEOPLE_READ].STATUS FROM [OC_HROpCo SU Hybrid_PEOPLE_READ] ORDER BY [OC_HROpCo SU Hybrid_PEOPLE_READ].REFERENCENUMBER ASC --- I have attached files with execution plan information for the 2 users and the views that are used in the query in the answers section.
Other than presumably setting the context info, what is your stored procedure doing? Just looking at the code you provided, I can't see a reason the 2 users would get different results. Can you share more with us?
[link text][1]Grant, thank you for your comments. Unfortunately, I was in a place where I couldn't get on the internet yesterday. The queries are the same with the only difference being the user in the contextinfo. The problem is that with one user the results are returned in approx 2-4 sec. and with the other user the results are returned in approx 22-32 sec. I have attached the execution plans for both users which shows the large discrepancy in the number of rows being processed between the two users. [1]: /storage/temp/2773-ralbertocontextinfo.sqlplan
Looks like both bad parameter sniffing and out of date statistics. Add in that user defined table valued function too. Also the index on the People table could use an INCLUDE for the three columns that it's going back to the clustered index for. And the optimizer timed out. Focus first on the statistics. Make sure they're getting updated.
Those execution plans are not helpful as shown. They simply list the operators and costs. We can't see the details behind them. If you can, save them as .sqlplan file. If you're concerned with security of your object names, track down a copy of the free tool from SQL Sentry called Plan Explorer. It will allow you to hide the object names before you post the .sqlplan file. Regardless, from the sounds of things, you're looking at bad parameter sniffing. One plan is for 5000+ rows. The other plan is for 200+ rows. One of the two plans runs extremely slowly when called by the other. To determine if this is the case, when you have the two different plans in front of you (again, use the .sqlplan file so you can look at it through SSMS or Plan Explorer), look at the first operator. It will show a Compile Time value for the plan, if you're using parameters. With the compile time value, look to your statistics to see that the compile settings are accurate. The trick is identifying which of the plans runs fast most of the time and then using the appropriate fix to ensure that plan is generated. Fixes usually involve using OPTIMIZE FOR and either specifying a specific value, or using UNKNOWN to get an average value.
These are the views that are part of the query. - USE [TestDBLive] GO /****** Object: View [dbo].[OC_HROpCo SU Hybrid_PEOPLE_READ] Script Date: 09/02/2015 18:27:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER VIEW [dbo].[OC_HROpCo SU Hybrid_PEOPLE_READ] AS SELECT dbo.PEOPLE.* FROM dbo.PEOPLE WHERE dbo.PEOPLE.PEOPLE_ID IN (SELECT DISTINCT dbo.PEOPLE.PEOPLE_ID FROM dbo.PEOPLE INNER JOIN dbo.PEOPLE PEOPLE_1 ON PEOPLE_1.PEOPLE_ID = (SELECT * FROM dbo.FNOC_SECURITY_GETCURRENTUSERRECORDID()) LEFT OUTER JOIN dbo.VWTOPJOBANDSALARY ON dbo.VWTOPJOBANDSALARY.PEOPLE_ID = dbo.PEOPLE.PEOPLE_ID LEFT OUTER JOIN dbo.SECURITYPROFILE ON dbo.SECURITYPROFILE.PEOPLE_ID = PEOPLE_1.PEOPLE_ID LEFT OUTER JOIN dbo.SECURITYOPCO ON dbo.SECURITYOPCO.SECURITYPROFILE_ID = dbo.SECURITYPROFILE.SECURITYPROFILE_ID LEFT OUTER JOIN dbo.SECURITYREGIONALCOMPANY ON dbo.SECURITYREGIONALCOMPANY.SECURITYPROFILE_ID = dbo.SECURITYPROFILE.SECURITYPROFILE_ID LEFT OUTER JOIN dbo.SECURITYOPCO1 ON dbo.SECURITYOPCO1.SECURITYPROFILE_ID = dbo.SECURITYPROFILE.SECURITYPROFILE_ID WHERE ((dbo.VWTOPJOBANDSALARY.OPERATINGCOMPANY = dbo.SECURITYOPCO.OPERATINGCOMPANY) OR ((dbo.VWTOPJOBANDSALARY.REGIONALCOMPANY = dbo.SECURITYREGIONALCOMPANY.REGIONALCOMPANY) OR (dbo.VWTOPJOBANDSALARY.OPCOSTRUCTURE1 = dbo.SECURITYOPCO1.OPCOSTRUCTURE1))) AND ((dbo.VWTOPJOBANDSALARY.CURRENTRECORD = N'YES') AND ((dbo.VWTOPJOBANDSALARY.CURRENTRECORD = dbo.SECURITYPROFILE.ISCURRENT) AND (dbo.PEOPLE.ARCHIVEDRECORD <> 'T')))) GO --- USE [TestDBLive] GO /****** Object: View [dbo].[VWTOPJOBANDSALARY] Script Date: 09/02/2015 18:35:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /****** Object: View [dbo].[VWTOPJOBANDSALARY] Script Date: 06/02/2014 20:10:13 ******/ /* ****************************************** */ ALTER VIEW [dbo].[VWTOPJOBANDSALARY] AS SELECT P.PEOPLE_ID AS VWTOPJOBANDSALARY_ID, P.PEOPLE_ID, p. PEOPLE_ID AS EMPLOYEE, P. DATECREATED, P. DATELASTMODIFIED, P. CREATEDBY, P. LASTMODIFIEDBY, P.EMAILPRIMARY as PrimaryEmail, J.jobtitle as Jobtitle, J.ORGLEVEL1 As Groups, J.ORGLEVEL2 As ParentHoldingCompany, J.ORGLEVEL4 AS OperatingCompany, J. LEVEL6 AS OpCoStructure1, J.ORGLEVEL3 As RegionalCompany, J.LEVEL7 AS OpCoStructure2, J.LEVEL8 As OpCoStructure3, J.LEVEL9 As OpCoStructure4, CURRENTRECORD='YES' FROM PEOPLE P OUTER APPLY --JOBDETAIL J ON P.PEOPLE_ID=J.PEOPLE_ID AND J.CURRENTRECORD ='YES' ( SELECT TOP 1 J.ORGLEVEL1, J.ORGLEVEL2 , J.ORGLEVEL3, J.ORGLEVEL4, J.JOBTITLE, J.LEVEL6, J.LEVEL7, J.LEVEL8, J.LEVEL9, J.PEOPLE_ID FROM dbo.JOBDETAIL J WHERE J.PEOPLE_ID = P.PEOPLE_ID ORDER BY CURRENTRECORD DESC, EFFECTIVEDATE DESC ) J LEFT OUTER JOIN SALARYHISTORY SH ON P.PEOPLE_ID=SH.PEOPLE_ID AND ISCURRENT='T' GO
Grant, the application and code is the controlled by 3rd party vendors. They said the adding the INCLUDE lastname, Firstname, Status to index OC_IX_PEOPLE_REFERENCENUMBER is not supported in the application. They altered the view VWTOPJOBANDSALARY which acheived a execution time of 4 seconds or less for all 3 users.The altered view follows. This ooption is being investigated to see if it affects any other parts of the application. USE [TalentTreeUAT] GO /****** Object: View [dbo].[VWTOPJOBANDSALARY] Script Date: 09/08/2015 12:23:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --USE [TalentTreeUAT] --GO --/****** Object: View [dbo].[VWTOPJOBANDSALARY] Script Date: 08/20/2015 17:31:39 ******/ --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --/****** Object: View [dbo].[VWTOPJOBANDSALARY] Script Date: 06/02/2014 20:10:13 ******/ --/* ****************************************** */ ALTER VIEW [dbo].[VWTOPJOBANDSALARY] AS SELECT P.PEOPLE_ID AS VWTOPJOBANDSALARY_ID, P.PEOPLE_ID, p. PEOPLE_ID AS EMPLOYEE, P. DATECREATED, P. DATELASTMODIFIED, P. CREATEDBY, P. LASTMODIFIEDBY, P.EMAILPRIMARY as PrimaryEmail, J.jobtitle as Jobtitle, J.ORGLEVEL1 As Groups, J.ORGLEVEL2 As ParentHoldingCompany, J.ORGLEVEL4 AS OperatingCompany, J. LEVEL6 AS OpCoStructure1, J.ORGLEVEL3 As RegionalCompany, J.LEVEL7 AS OpCoStructure2, J.LEVEL8 As OpCoStructure3, J.LEVEL9 As OpCoStructure4, CURRENTRECORD='YES' FROM PEOPLE P OUTER APPLY --JOBDETAIL J ON P.PEOPLE_ID=J.PEOPLE_ID AND J.CURRENTRECORD ='YES' ( SELECT J.ORGLEVEL1, --TOP 1 removed from SELECT J.ORGLEVEL2 , J.ORGLEVEL3, J.ORGLEVEL4, J.JOBTITLE, J.LEVEL6, J.LEVEL7, J.LEVEL8, J.LEVEL9, J.PEOPLE_ID, ROW_NUMBER() OVER(ORDER BY CURRENTRECORD DESC, EFFECTIVEDATE DESC) JOB_ROWNUMBER --Above line added & order by commented out FROM dbo.JOBDETAIL J WHERE J.PEOPLE_ID = P.PEOPLE_ID --ORDER BY CURRENTRECORD DESC, EFFECTIVEDATE DESC ) J LEFT OUTER JOIN SALARYHISTORY SH ON P.PEOPLE_ID=SH.PEOPLE_ID AND ISCURRENT='T' WHERE ISNULL(JOB_ROWNUMBER,1) = 1 GO
That won't address the out of date or skewed statistics. Also this: ISNULL(JOB_ROWNUMBER,1). Is likely to lead to scans.
Grant, thanks for your help. I have updated the statistics but that has not caused any improvements. Unfortunately, what happens internally isn't considered if the outward results are achieved. They are testing the changes to the view that was suggested since the query results were returned much faster to see if any other parts of the application would be affected. If the change to the view does not show any ill affects on other parts of the application then the altered view will be placed in production.
19 People are following this question.
