SQL timeout issue - Stored procedure run from client VS enterprise manager.

SQL 2000
Key Symptoms :
Stored procedure runs fine in under a second on the server.
Same parameters in the .NET application cause a timeout however more peculiarly, the timeout started occurring after a previous successful execution and played this way for a while and now occurs during the first one.
The underlying data has not grown or changed to affect the complexity of the operation.
The stored procedure worked fine in the .NET application and then stopped working without any modification to the stored procedure.  It continues to work fine on the server completing in under a second.
---
Background:
The stored procedure essentially builds a short list of integers into a temporary table.  This process involves 2 very small tables (<500 records) and happens quickly.  This list is then used to do a more complex cross database select which seems to be the sticking point. (removing this command allows the stored procedure to run fine albeit without results)
Keep in mind this stored procedure did work on the .NET client for a while.

Now when I paste in an earlier version of the stored procedure there is no problem at all. The difference between the older and newer version was in the way the list of integers is built, the final select command is the same  and the list of integers in the temporary table is the same list of integers as in the not working version.

I don't think my problem is with my stored procedure rather that some how it is executing differently from the server as opposed to the client.
Things I have tried :

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

No impact.

Restarted SQL server.
Restarted Client development machine.

No impact.

Any other suggestions would be appreciated!
dgloverukAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dgloverukAuthor Commented:
Further to this I just wanted to add that the expected result set is under 50 rows and about 20 columns wide with no large data fields.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The stored procedure worked fine in the .NET application and then stopped working without any modification to the stored procedure.
What about the .NET application? Has been changed? Or the server has been patched?

I think you know that but I like to remind people that SQL Server 2000 is not supported anymore. Can you migrate the database to a more recent version of SQL Server?
0
dgloverukAuthor Commented:
Hello Vitor.
We cannot migrate the database unfortunately.
The .net application has been changed although the stored procedure was used by more than one application and the behaviour is repeated in other applications.
Regards,
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not expert in .NET but I can imagine that the way of how the application connects to the database changed and that's why you are getting the timeouts now. Like I said, SQL Server 2000 is not supported anymore so the new connections methods doesn't take this version of SQL Server in mind.

I suggest you to talk with the application developers and check if they know a workaround for this issue.
0
dgloverukAuthor Commented:
Hi Vitor, I appreciate your consideration and response.  I am the application developer.
I have determined that the calling stored procedure has had one of its date time parameters now include a time component.
i.e 2014-09-15 was submitted to the stored procedure as 2014-09-15 23:59:59
However when testing this on the server whole dates were used.
Using the date time in this format gives a ODBC SQL Server Driver error of Datetime field overflow so I presume even though this date type in .net is valid it isn't passed in a valid format.
I think I am closer to understanding the cause but just need to work out what format I need to supply the stored procedure.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Anyway it's strange that the SP will give a timeout error just because the date format. At least should return no data.
0
Shepherd NhongoSenior Support EngineerCommented:
Hi.

I once had a similar problem and the solution is to go to Control Panel\All Control Panel Items\Administrative Tools\ODBC Data Sources (32-bit) or Control Panel\All Control Panel Items\Administrative Tools\ODBC Data Sources (64-bit) and change the connection pooling settings from 60 seconds to about 300. This should give you good results.
SQL Pooling
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Changing the connection timeout is a workaround that won't solve the problem. Only says to application that instead of waiting 60 seconds (1 minute) for a response it will wait 300 seconds (5 minutes).
I don't think an user want to wait until 5 minutes for an operation that before was almost instantaneous.
0
dgloverukAuthor Commented:
Yes that's right Vitor... but thank you Shepherd for your information.

I have determined some more about the problem but it leaves more questions.

If I :
exec spGetResultsByScope @StartDate = '2014-08-29',@EndDate = '2014-09-02'
Instantaneous.
exec spGetResultsByScope @StartDate = '2014-08-30',@EndDate = '2014-09-02'
Timeout.
Dates I supply after @startdate also cause the issue.
I can supply :
exec spGetResultsByScope @StartDate = '2010-09-03',@EndDate = '2014-08-29'
which encompasses years worth of data and its instant.
But something not right with recent dates.

I will try to look at the underlying data to see if I can determine anything else.  I have posted the final stage of my stored procedure to see if there is anything obvious that might make this stored procedure prone to problems.
Incidently I tried a date range in the past representing a weekend as there would ordinarily be no data at a weekend and there was no problem with the stored procedure and the results came back 0.
I am thinking is it possible there is some kind of indexing issue?


select ot1.*,Contracts,Perms,NamedContracts,NamedPerms,CVSend,FirstInterviews from
(select * from #KPIS)
 ot1 inner JOIN (
SELECT     x1.*, x2.CVSend, X2.FirstInterviews
FROM         (
SELECT     t1.KPIID, SUM(CASE WHEN (Contract = 1 AND Trisys.dbo.ContractSkill.SkillID = 30960) THEN 1 ELSE 0 END) AS Contracts,
                                              SUM(CASE WHEN (Contract = 0 AND Trisys.dbo.ContractSkill.SkillID = 30960) THEN 1 ELSE 0 END) AS Perms,
                                              SUM(CASE WHEN (Contract = 1 AND Trisys.dbo.ContractSkill.SkillID = 30961) THEN 1 ELSE 0 END) AS NamedContracts,
                                              SUM(CASE WHEN (Contract = 0 AND Trisys.dbo.ContractSkill.SkillID = 30961) THEN 1 ELSE 0 END) AS NamedPerms
                      FROM         Trisys.dbo.Contract INNER JOIN
                                  Trisys.dbo.ContractDate c ON Trisys.dbo.Contract.ContractId = c.ContractId INNER JOIN
                                        Trisys.dbo.ContractSkill ON Trisys.dbo.Contract.ContractId = Trisys.dbo.ContractSkill.ContractId FULL OUTER JOIN
                      #KPIS t1 ON Trisys.dbo.Contract.UserId = t1.ConsultantID AND c.ContractDate BETWEEN t1.StartRange AND t1.EndRange

                       GROUP BY t1.KPIID)
                      x1 inner join
(SELECT     KPIID, MAX(CVSend) AS CVSend, MAX(FirstInterviews) AS FirstInterviews
FROM         (SELECT     t2.KPIID, SUM(CASE WHEN CVSend = 1 THEN 1 ELSE 0 END) AS CVSend,
                                              SUM(CASE WHEN FirstInterview = 1 THEN 1 ELSE 0 END) AS FirstInterviews
                       FROM          Trisys.dbo.TaskDate d INNER JOIN
                                              Trisys.dbo.TaskUsers ON d .TaskID = Trisys.dbo.TaskUsers.TaskId INNER JOIN
                                              #KPIS t2 ON d .CreatedDate BETWEEN t2.StartRange AND t2.EndRange AND
                                              Trisys.dbo.TaskUsers.UserId = t2.ConsultantID
                       WHERE      (d .CVSend = 1) OR
                                              (d .FirstInterview = 1)
                       GROUP BY t2.KPIID
                       UNION ALL
                       SELECT     #KPIS.KPIID, 0 AS CVSend, 0 AS FirstInterviews
                       FROM         #KPIS) a
GROUP BY KPIID) x2 on x1.kpiid = x2.kpiid
)
ot2 on ot1.KPIID = ot2.KPIID
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check the statistics date for those tables
SELECT STATS_DATE(table_id. index_id)

Open in new window

If the date is 2014-08-29 you already have a clue that can be confirmed if you run the query in SSMS to show the actual execution plan for both cases. If execution plans are different then you need to reindex the table(s).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shepherd NhongoSenior Support EngineerCommented:
This sounds more like a dirty file system. I  recommend you run chkdsk /r onyourHDDVolume: & then try again. In most cases a dirty files system will land us in these kind of errors.

Cheers
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please don't run CHKDSK on volumes with SQL Server files before doing first the following steps:

1. Backup all databases

2. Run a DBCC CHECKDB on each database

3. Talk to your storage administrator if it's a network disk

0
dgloverukAuthor Commented:
Hi guys, thank you for your words of caution Vitor.
I would like to add one more thing which may help, forgive the big post of the entire stored proc but it may show light....


CREATE PROCEDURE spGetResultsByScope @Scope as int =  0,@StartDate as datetime,@EndDate as datetime,@DeptID as int=0,@ConsultantID as int = 0,@Grade as nvarchar(100) = '',@LastUpdated as datetime ='2008-01-01' AS
begin
if not exists(select 1 from Trisys.dbo.Users where Trisys.dbo.Users.LastKPIUpdate > @LastUpdated) return
CREATE TABLE #Consultants(ConsultantID int)
CREATE TABLE #KPIList(KPIID int)
CREATE TABLE #KPIS (
      [KPIID] [int] NOT NULL ,
      [ConsultantID] [int] NOT NULL ,
      [KPIName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
      [KPIStartDate] [datetime] NOT NULL ,
      [KPIEndDate] [datetime] NOT NULL ,
      [TargetStartPositionsAdded] [float] NOT NULL ,
      [TargetEndPositionsAdded] [float] NOT NULL ,
      [TargetStartCVsSent] [float] NOT NULL ,
      [TargetEndCVsSent] [float] NOT NULL ,
      [TargetStartFirstInterviews] [float] NOT NULL ,
      [TargetEndFirstInterviews] [float] NOT NULL ,
      [TargetStartPermPlacements] [float] NOT NULL ,
      [TargetEndPermPlacements] [float] NOT NULL ,
      [TargetStartContractPlacements] [float] NOT NULL ,
      [TargetEndContractPlacements] [float] NOT NULL ,
      [TargetStartRangeInterviewsToStart] [float] NOT NULL ,
      [TargetEndRangeInterviewsToStart] [float] NOT NULL ,
      [TargetStartInnerInterviewsToStart] [float] NOT NULL ,
      [TargetEndInnerInterviewsToStart] [float] NOT NULL ,
      [TargetStartRangeCVsSentToInterview] [float] NOT NULL ,
      [TargetEndRangeCVsSentToInterview] [float] NOT NULL ,
      [TargetStartInnerCVsSentToInterview] [float] NOT NULL ,
      [TargetEndInnerCVsSentToInterview] [float] NOT NULL ,
      [TargetStartRangeCVsSentToPosition] [float] NOT NULL ,
      [TargetEndRangeCVsSentToPosition] [float] NOT NULL ,
      [TargetStartInnerCVsSentToPosition] [float] NOT NULL ,
      [TargetEndInnerCVsSentToPosition] [float] NOT NULL ,
      [WeightPositions] [int] NOT NULL ,
      [WeightCVsSent] [int] NOT NULL ,
      [WeightInterviews] [int] NOT NULL ,
      [WeightPermPlacements] [int] NOT NULL ,
      [WeightContractPlacements] [int] NOT NULL ,
      [WeightCVsSentToPosition] [int] NOT NULL ,
      [WeightCVsSentToInterview] [int] NOT NULL ,
      [WeightInterviewsToStart] [int] NOT NULL,
      [StartRange] [datetime] NOT NULL,
      [EndRange] [datetime] NOT NULL,
      [Updated] [datetime] NOT NULL,
      [DepartmentID] [int] NOT NULL,
      [Department][nvarchar](30) not null,
      [Consultant][nvarchar](200) not null,
      [Grade][nvarchar] (100)not null,
      [Active][bit] not null
)

--CompanyWide - 1
      if @Scope = 0
      begin
            insert into #Consultants (ConsultantID) (select distinct ConsultantID from KPI)
      end
--ByDepartment - 1
      if @Scope = 1
      begin
            insert into #Consultants (ConsultantID) (SELECT DISTINCT dbo.KPI.ConsultantID FROM dbo.KPI INNER JOIN FGDB.dbo.tblUser ON dbo.KPI.ConsultantID = FGDB.dbo.tblUser.TrisysUserID WHERE  (FGDB.dbo.tblUser.DeptID = @DeptID))
      end
--ByGrade - 2
      if @Scope = 2
      begin
            insert into #Consultants (ConsultantID) (SELECT DISTINCT dbo.KPI.ConsultantID FROM dbo.KPI INNER JOIN FGDB.dbo.tblUser ON dbo.KPI.ConsultantID = FGDB.dbo.tblUser.TrisysUserID WHERE (FGDB.dbo.tblUser.DeptID = @ConsultantID) AND (FGDB.dbo.tblUser.Grade = @Grade))
      end
--ByConsultant - 3
      if @Scope = 3
      begin
            insert into #Consultants (ConsultantID) (SELECT dbo.KPI.ConsultantID FROM dbo.KPI INNER JOIN FGDB.dbo.tblUser ON dbo.KPI.ConsultantID = FGDB.dbo.tblUser.TrisysUserID INNER JOIN  FGDB.dbo.tblUser tblUser_1 ON FGDB.dbo.tblUser.LoginMatching = tblUser_1.LoginMatching WHERE     (tblUser_1.TrisysUserID = @ConsultantID) GROUP BY dbo.KPI.ConsultantID)
      end
--ByConsultantInADepartment - 4
      if @Scope = 4
      begin
            insert into #Consultants (ConsultantID) (SELECT DISTINCT dbo.KPI.ConsultantID FROM dbo.KPI INNER JOIN FGDB.dbo.tblUser ON dbo.KPI.ConsultantID = FGDB.dbo.tblUser.TrisysUserID WHERE  (FGDB.dbo.tblUser.TrisysUserID = @ConsultantID))
      end


      insert into #KPIList(dbo.KPI.KPIID)
            
SELECT     dbo.KPI.KPIID
FROM         dbo.KPI INNER JOIN
                      #Consultants ON dbo.KPI.ConsultantID = #Consultants.ConsultantID INNER JOIN
                      Trisys.dbo.Users ON dbo.KPI.ConsultantID = Trisys.dbo.Users.UserId INNER JOIN
                      FGDB.dbo.tblUser ON Trisys.dbo.Users.UserId = FGDB.dbo.tblUser.TrisysUserID INNER JOIN
                      FGDB.dbo.tblDepartment ON FGDB.dbo.tblUser.DeptID = FGDB.dbo.tblDepartment.DeptID


WHERE    
(Trisys.dbo.Users.LastKPIUpdate > @LastUpdated) and  (((@StartDate >= dbo.KPI.KPIStartDate) and (@StartDate <= dbo.KPI.KPIEndDate)) or ((@EndDate >= dbo.KPI.KPIStartDate) and (@EndDate <= dbo.KPI.KPIEndDate)))
      
insert into #KPIS(dbo.KPI.KPIID, dbo.KPI.ConsultantID, dbo.KPI.KPIName, dbo.KPI.KPIStartDate, dbo.KPI.KPIEndDate, dbo.KPI.TargetStartPositionsAdded,
                      dbo.KPI.TargetEndPositionsAdded, dbo.KPI.TargetStartCVsSent, dbo.KPI.TargetEndCVsSent, dbo.KPI.TargetStartFirstInterviews,
                      dbo.KPI.TargetEndFirstInterviews, dbo.KPI.TargetStartPermPlacements, dbo.KPI.TargetEndPermPlacements, dbo.KPI.TargetStartContractPlacements,
                      dbo.KPI.TargetEndContractPlacements, dbo.KPI.TargetStartRangeInterviewsToStart, dbo.KPI.TargetEndRangeInterviewsToStart,
                      dbo.KPI.TargetStartInnerInterviewsToStart, dbo.KPI.TargetEndInnerInterviewsToStart, dbo.KPI.TargetStartRangeCVsSentToInterview,
                      dbo.KPI.TargetEndRangeCVsSentToInterview, dbo.KPI.TargetStartInnerCVsSentToInterview, dbo.KPI.TargetEndInnerCVsSentToInterview,
                      dbo.KPI.TargetStartRangeCVsSentToPosition, dbo.KPI.TargetEndRangeCVsSentToPosition, dbo.KPI.TargetStartInnerCVsSentToPosition,
                      dbo.KPI.TargetEndInnerCVsSentToPosition, dbo.KPI.WeightPositions, dbo.KPI.WeightCVsSent, dbo.KPI.WeightInterviews, dbo.KPI.WeightPermPlacements,
                      dbo.KPI.WeightContractPlacements, dbo.KPI.WeightCVsSentToPosition, dbo.KPI.WeightCVsSentToInterview, dbo.KPI.WeightInterviewsToStart,
                      StartRange,  EndRange, dbo.KPI.Updated,
            DepartmentID,Department,Consultant,Grade,Active
)
            
SELECT     dbo.KPI.KPIID, dbo.KPI.ConsultantID, dbo.KPI.KPIName, dbo.KPI.KPIStartDate, dbo.KPI.KPIEndDate, dbo.KPI.TargetStartPositionsAdded,
                      dbo.KPI.TargetEndPositionsAdded, dbo.KPI.TargetStartCVsSent, dbo.KPI.TargetEndCVsSent, dbo.KPI.TargetStartFirstInterviews,
                      dbo.KPI.TargetEndFirstInterviews, dbo.KPI.TargetStartPermPlacements, dbo.KPI.TargetEndPermPlacements, dbo.KPI.TargetStartContractPlacements,
                      dbo.KPI.TargetEndContractPlacements, dbo.KPI.TargetStartRangeInterviewsToStart, dbo.KPI.TargetEndRangeInterviewsToStart,
                      dbo.KPI.TargetStartInnerInterviewsToStart, dbo.KPI.TargetEndInnerInterviewsToStart, dbo.KPI.TargetStartRangeCVsSentToInterview,
                      dbo.KPI.TargetEndRangeCVsSentToInterview, dbo.KPI.TargetStartInnerCVsSentToInterview, dbo.KPI.TargetEndInnerCVsSentToInterview,
                      dbo.KPI.TargetStartRangeCVsSentToPosition, dbo.KPI.TargetEndRangeCVsSentToPosition, dbo.KPI.TargetStartInnerCVsSentToPosition,
                      dbo.KPI.TargetEndInnerCVsSentToPosition, dbo.KPI.WeightPositions, dbo.KPI.WeightCVsSent, dbo.KPI.WeightInterviews,
                      dbo.KPI.WeightPermPlacements, dbo.KPI.WeightContractPlacements, dbo.KPI.WeightCVsSentToPosition, dbo.KPI.WeightCVsSentToInterview,
                      dbo.KPI.WeightInterviewsToStart, dbo.KPI.KPIStartDate AS StartRange, dbo.KPI.KPIEndDate AS EndRange, Trisys.dbo.Users.LastKPIUpdate,
               FGDB.dbo.tblUser.DeptID as DepartmentID, FGDB.dbo.tblDepartment.Department, FGDB.dbo.tblUser.Firstname + ' ' + FGDB.dbo.tblUser.Surname AS Consultant,FGDB.dbo.tblUser.Grade, FGDB.dbo.tblUser.Active
FROM         dbo.KPI INNER JOIN
                    #KPIList ON dbo.KPI.KPIID = #KPIList.KPIID INNER JOIN
                      Trisys.dbo.Users ON dbo.KPI.ConsultantID = Trisys.dbo.Users.UserId INNER JOIN
                      FGDB.dbo.tblUser ON Trisys.dbo.Users.UserId = FGDB.dbo.tblUser.TrisysUserID INNER JOIN
                      FGDB.dbo.tblDepartment ON FGDB.dbo.tblUser.DeptID = FGDB.dbo.tblDepartment.DeptID

            --Update #KPIS set StartRange = @StartDate where @StartDate > StartRange
            --Update #KPIS set EndRange = @EndDate where @EndDate < EndRange
--select KPIID,KPIStartDate,StartRange,KPIEndDate,Endrange from #KPIS
DROP TABLE #KPIList
DROP TABLE #Consultants

select ot1.*,Contracts,Perms,NamedContracts,NamedPerms,CVSend,FirstInterviews from
(select * from #KPIS)
 ot1 inner JOIN (
SELECT     x1.*, x2.CVSend, X2.FirstInterviews
FROM         (
SELECT     t1.KPIID, SUM(CASE WHEN (Contract = 1 AND Trisys.dbo.ContractSkill.SkillID = 30960) THEN 1 ELSE 0 END) AS Contracts,
                                              SUM(CASE WHEN (Contract = 0 AND Trisys.dbo.ContractSkill.SkillID = 30960) THEN 1 ELSE 0 END) AS Perms,
                                              SUM(CASE WHEN (Contract = 1 AND Trisys.dbo.ContractSkill.SkillID = 30961) THEN 1 ELSE 0 END) AS NamedContracts,
                                              SUM(CASE WHEN (Contract = 0 AND Trisys.dbo.ContractSkill.SkillID = 30961) THEN 1 ELSE 0 END) AS NamedPerms
                      FROM         Trisys.dbo.Contract INNER JOIN
                                  Trisys.dbo.ContractDate c ON Trisys.dbo.Contract.ContractId = c.ContractId INNER JOIN
                                        Trisys.dbo.ContractSkill ON Trisys.dbo.Contract.ContractId = Trisys.dbo.ContractSkill.ContractId FULL OUTER JOIN
                      #KPIS t1 ON Trisys.dbo.Contract.UserId = t1.ConsultantID AND c.ContractDate BETWEEN t1.StartRange AND t1.EndRange

                       GROUP BY t1.KPIID)
                      x1 inner join
(SELECT     KPIID, MAX(CVSend) AS CVSend, MAX(FirstInterviews) AS FirstInterviews
FROM         (SELECT     t2.KPIID, SUM(CASE WHEN CVSend = 1 THEN 1 ELSE 0 END) AS CVSend,
                                              SUM(CASE WHEN FirstInterview = 1 THEN 1 ELSE 0 END) AS FirstInterviews
                       FROM          Trisys.dbo.TaskDate d INNER JOIN
                                              Trisys.dbo.TaskUsers ON d .TaskID = Trisys.dbo.TaskUsers.TaskId INNER JOIN
                                              #KPIS t2 ON d .CreatedDate BETWEEN t2.StartRange AND t2.EndRange AND
                                              Trisys.dbo.TaskUsers.UserId = t2.ConsultantID
                       WHERE      (d .CVSend = 1) OR
                                              (d .FirstInterview = 1)
                       GROUP BY t2.KPIID
                       UNION ALL
                       SELECT     #KPIS.KPIID, 0 AS CVSend, 0 AS FirstInterviews
                       FROM         #KPIS) a
GROUP BY KPIID) x2 on x1.kpiid = x2.kpiid
)
ot2 on ot1.KPIID = ot2.KPIID
drop table #KPIS
end
GO


You will see in the middle three commented out lines :
--Update #KPIS set StartRange = @StartDate where @StartDate > StartRange
      --Update #KPIS set EndRange = @EndDate where @EndDate < EndRange
--select KPIID,KPIStartDate,StartRange,KPIEndDate,Endrange from #KPIS

Without these lines in the procedure works for all dates.
However the data in question being tested does not have StartRange/EndRange values that would cause an update to occur... I checked with the select command shown above and the values are all identical before and after (note that EndRange/Startrange begin life as copies of KPIStartDate/EndRange) so
my question becomes..
Does doing updates like this on temporary tables have possibility of causing a problem IF the update has no updates to do?
Anything obviously not right there?

I will follow the maintenance processes set out otherwise... it just seemed to me less likely to be an indexing / damage issue if the extraction of these two update commands would allow the subsequent select to work?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post inside a code tag? I can't copy like this.
0
dgloverukAuthor Commented:
Sorry vitor :
Note the only parameters supplied throughout this testing are startdate and endate.
CREATE PROCEDURE spGetResultsByScope @Scope as int =  0,@StartDate as datetime,@EndDate as datetime,@DeptID as int=0,@ConsultantID as int = 0,@Grade as nvarchar(100) = '',@LastUpdated as datetime ='2008-01-01' AS
begin
if not exists(select 1 from Trisys.dbo.Users where Trisys.dbo.Users.LastKPIUpdate > @LastUpdated) return
CREATE TABLE #Consultants(ConsultantID int)
CREATE TABLE #KPIList(KPIID int)
CREATE TABLE #KPIS (
	[KPIID] [int] NOT NULL ,
	[ConsultantID] [int] NOT NULL ,
	[KPIName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
	[KPIStartDate] [datetime] NOT NULL ,
	[KPIEndDate] [datetime] NOT NULL ,
	[TargetStartPositionsAdded] [float] NOT NULL ,
	[TargetEndPositionsAdded] [float] NOT NULL ,
	[TargetStartCVsSent] [float] NOT NULL ,
	[TargetEndCVsSent] [float] NOT NULL ,
	[TargetStartFirstInterviews] [float] NOT NULL ,
	[TargetEndFirstInterviews] [float] NOT NULL ,
	[TargetStartPermPlacements] [float] NOT NULL ,
	[TargetEndPermPlacements] [float] NOT NULL ,
	[TargetStartContractPlacements] [float] NOT NULL ,
	[TargetEndContractPlacements] [float] NOT NULL ,
	[TargetStartRangeInterviewsToStart] [float] NOT NULL ,
	[TargetEndRangeInterviewsToStart] [float] NOT NULL ,
	[TargetStartInnerInterviewsToStart] [float] NOT NULL ,
	[TargetEndInnerInterviewsToStart] [float] NOT NULL ,
	[TargetStartRangeCVsSentToInterview] [float] NOT NULL ,
	[TargetEndRangeCVsSentToInterview] [float] NOT NULL ,
	[TargetStartInnerCVsSentToInterview] [float] NOT NULL ,
	[TargetEndInnerCVsSentToInterview] [float] NOT NULL ,
	[TargetStartRangeCVsSentToPosition] [float] NOT NULL ,
	[TargetEndRangeCVsSentToPosition] [float] NOT NULL ,
	[TargetStartInnerCVsSentToPosition] [float] NOT NULL ,
	[TargetEndInnerCVsSentToPosition] [float] NOT NULL ,
	[WeightPositions] [int] NOT NULL ,
	[WeightCVsSent] [int] NOT NULL ,
	[WeightInterviews] [int] NOT NULL ,
	[WeightPermPlacements] [int] NOT NULL ,
	[WeightContractPlacements] [int] NOT NULL ,
	[WeightCVsSentToPosition] [int] NOT NULL ,
	[WeightCVsSentToInterview] [int] NOT NULL ,
	[WeightInterviewsToStart] [int] NOT NULL,
	[StartRange] [datetime] NOT NULL,
	[EndRange] [datetime] NOT NULL,
	[Updated] [datetime] NOT NULL,
	[DepartmentID] [int] NOT NULL,
	[Department][nvarchar](30) not null,
	[Consultant][nvarchar](200) not null,
	[Grade][nvarchar] (100)not null,
	[Active][bit] not null
) 

--CompanyWide - 1
	if @Scope = 0
	begin
		insert into #Consultants (ConsultantID) (select distinct ConsultantID from KPI)
	end
--ByDepartment - 1
	if @Scope = 1
	begin
		insert into #Consultants (ConsultantID) (SELECT DISTINCT dbo.KPI.ConsultantID FROM dbo.KPI INNER JOIN FGDB.dbo.tblUser ON dbo.KPI.ConsultantID = FGDB.dbo.tblUser.TrisysUserID WHERE  (FGDB.dbo.tblUser.DeptID = @DeptID))
	end
--ByGrade - 2
	if @Scope = 2
	begin
		insert into #Consultants (ConsultantID) (SELECT DISTINCT dbo.KPI.ConsultantID FROM dbo.KPI INNER JOIN FGDB.dbo.tblUser ON dbo.KPI.ConsultantID = FGDB.dbo.tblUser.TrisysUserID WHERE (FGDB.dbo.tblUser.DeptID = @ConsultantID) AND (FGDB.dbo.tblUser.Grade = @Grade))
	end
--ByConsultant - 3
	if @Scope = 3
	begin
		insert into #Consultants (ConsultantID) (SELECT dbo.KPI.ConsultantID FROM dbo.KPI INNER JOIN FGDB.dbo.tblUser ON dbo.KPI.ConsultantID = FGDB.dbo.tblUser.TrisysUserID INNER JOIN  FGDB.dbo.tblUser tblUser_1 ON FGDB.dbo.tblUser.LoginMatching = tblUser_1.LoginMatching WHERE     (tblUser_1.TrisysUserID = @ConsultantID) GROUP BY dbo.KPI.ConsultantID)
	end
--ByConsultantInADepartment - 4
	if @Scope = 4
	begin
		insert into #Consultants (ConsultantID) (SELECT DISTINCT dbo.KPI.ConsultantID FROM dbo.KPI INNER JOIN FGDB.dbo.tblUser ON dbo.KPI.ConsultantID = FGDB.dbo.tblUser.TrisysUserID WHERE  (FGDB.dbo.tblUser.TrisysUserID = @ConsultantID))
	end


	insert into #KPIList(dbo.KPI.KPIID) 
		
SELECT     dbo.KPI.KPIID
FROM         dbo.KPI INNER JOIN
                      #Consultants ON dbo.KPI.ConsultantID = #Consultants.ConsultantID INNER JOIN
                      Trisys.dbo.Users ON dbo.KPI.ConsultantID = Trisys.dbo.Users.UserId INNER JOIN
                      FGDB.dbo.tblUser ON Trisys.dbo.Users.UserId = FGDB.dbo.tblUser.TrisysUserID INNER JOIN
                      FGDB.dbo.tblDepartment ON FGDB.dbo.tblUser.DeptID = FGDB.dbo.tblDepartment.DeptID


WHERE    
(Trisys.dbo.Users.LastKPIUpdate > @LastUpdated) and  (((@StartDate >= dbo.KPI.KPIStartDate) and (@StartDate <= dbo.KPI.KPIEndDate)) or ((@EndDate >= dbo.KPI.KPIStartDate) and (@EndDate <= dbo.KPI.KPIEndDate))) 
	
insert into #KPIS(dbo.KPI.KPIID, dbo.KPI.ConsultantID, dbo.KPI.KPIName, dbo.KPI.KPIStartDate, dbo.KPI.KPIEndDate, dbo.KPI.TargetStartPositionsAdded, 
                      dbo.KPI.TargetEndPositionsAdded, dbo.KPI.TargetStartCVsSent, dbo.KPI.TargetEndCVsSent, dbo.KPI.TargetStartFirstInterviews, 
                      dbo.KPI.TargetEndFirstInterviews, dbo.KPI.TargetStartPermPlacements, dbo.KPI.TargetEndPermPlacements, dbo.KPI.TargetStartContractPlacements, 
                      dbo.KPI.TargetEndContractPlacements, dbo.KPI.TargetStartRangeInterviewsToStart, dbo.KPI.TargetEndRangeInterviewsToStart, 
                      dbo.KPI.TargetStartInnerInterviewsToStart, dbo.KPI.TargetEndInnerInterviewsToStart, dbo.KPI.TargetStartRangeCVsSentToInterview, 
                      dbo.KPI.TargetEndRangeCVsSentToInterview, dbo.KPI.TargetStartInnerCVsSentToInterview, dbo.KPI.TargetEndInnerCVsSentToInterview, 
                      dbo.KPI.TargetStartRangeCVsSentToPosition, dbo.KPI.TargetEndRangeCVsSentToPosition, dbo.KPI.TargetStartInnerCVsSentToPosition, 
                      dbo.KPI.TargetEndInnerCVsSentToPosition, dbo.KPI.WeightPositions, dbo.KPI.WeightCVsSent, dbo.KPI.WeightInterviews, dbo.KPI.WeightPermPlacements, 
                      dbo.KPI.WeightContractPlacements, dbo.KPI.WeightCVsSentToPosition, dbo.KPI.WeightCVsSentToInterview, dbo.KPI.WeightInterviewsToStart,
                      StartRange,  EndRange, dbo.KPI.Updated,
		DepartmentID,Department,Consultant,Grade,Active
) 
		
SELECT     dbo.KPI.KPIID, dbo.KPI.ConsultantID, dbo.KPI.KPIName, dbo.KPI.KPIStartDate, dbo.KPI.KPIEndDate, dbo.KPI.TargetStartPositionsAdded, 
                      dbo.KPI.TargetEndPositionsAdded, dbo.KPI.TargetStartCVsSent, dbo.KPI.TargetEndCVsSent, dbo.KPI.TargetStartFirstInterviews, 
                      dbo.KPI.TargetEndFirstInterviews, dbo.KPI.TargetStartPermPlacements, dbo.KPI.TargetEndPermPlacements, dbo.KPI.TargetStartContractPlacements, 
                      dbo.KPI.TargetEndContractPlacements, dbo.KPI.TargetStartRangeInterviewsToStart, dbo.KPI.TargetEndRangeInterviewsToStart, 
                      dbo.KPI.TargetStartInnerInterviewsToStart, dbo.KPI.TargetEndInnerInterviewsToStart, dbo.KPI.TargetStartRangeCVsSentToInterview, 
                      dbo.KPI.TargetEndRangeCVsSentToInterview, dbo.KPI.TargetStartInnerCVsSentToInterview, dbo.KPI.TargetEndInnerCVsSentToInterview, 
                      dbo.KPI.TargetStartRangeCVsSentToPosition, dbo.KPI.TargetEndRangeCVsSentToPosition, dbo.KPI.TargetStartInnerCVsSentToPosition, 
                      dbo.KPI.TargetEndInnerCVsSentToPosition, dbo.KPI.WeightPositions, dbo.KPI.WeightCVsSent, dbo.KPI.WeightInterviews, 
                      dbo.KPI.WeightPermPlacements, dbo.KPI.WeightContractPlacements, dbo.KPI.WeightCVsSentToPosition, dbo.KPI.WeightCVsSentToInterview, 
                      dbo.KPI.WeightInterviewsToStart, dbo.KPI.KPIStartDate AS StartRange, dbo.KPI.KPIEndDate AS EndRange, Trisys.dbo.Users.LastKPIUpdate,
	         FGDB.dbo.tblUser.DeptID as DepartmentID, FGDB.dbo.tblDepartment.Department, FGDB.dbo.tblUser.Firstname + ' ' + FGDB.dbo.tblUser.Surname AS Consultant,FGDB.dbo.tblUser.Grade, FGDB.dbo.tblUser.Active
FROM         dbo.KPI INNER JOIN
                    #KPIList ON dbo.KPI.KPIID = #KPIList.KPIID INNER JOIN
                      Trisys.dbo.Users ON dbo.KPI.ConsultantID = Trisys.dbo.Users.UserId INNER JOIN
                      FGDB.dbo.tblUser ON Trisys.dbo.Users.UserId = FGDB.dbo.tblUser.TrisysUserID INNER JOIN
                      FGDB.dbo.tblDepartment ON FGDB.dbo.tblUser.DeptID = FGDB.dbo.tblDepartment.DeptID

		Update #KPIS set StartRange = @StartDate where @StartDate > StartRange
		Update #KPIS set EndRange = @EndDate where @EndDate < EndRange
select KPIID,KPIStartDate,StartRange,KPIEndDate,Endrange from #KPIS
return

DROP TABLE #KPIList
DROP TABLE #Consultants

select ot1.*,Contracts,Perms,NamedContracts,NamedPerms,CVSend,FirstInterviews from 
(select * from #KPIS)
 ot1 inner JOIN (
SELECT     x1.*, x2.CVSend, X2.FirstInterviews
FROM         (
SELECT     t1.KPIID, SUM(CASE WHEN (Contract = 1 AND Trisys.dbo.ContractSkill.SkillID = 30960) THEN 1 ELSE 0 END) AS Contracts, 
                                              SUM(CASE WHEN (Contract = 0 AND Trisys.dbo.ContractSkill.SkillID = 30960) THEN 1 ELSE 0 END) AS Perms, 
                                              SUM(CASE WHEN (Contract = 1 AND Trisys.dbo.ContractSkill.SkillID = 30961) THEN 1 ELSE 0 END) AS NamedContracts, 
                                              SUM(CASE WHEN (Contract = 0 AND Trisys.dbo.ContractSkill.SkillID = 30961) THEN 1 ELSE 0 END) AS NamedPerms
			    FROM         Trisys.dbo.Contract INNER JOIN
                   		   Trisys.dbo.ContractDate c ON Trisys.dbo.Contract.ContractId = c.ContractId INNER JOIN
                     			 Trisys.dbo.ContractSkill ON Trisys.dbo.Contract.ContractId = Trisys.dbo.ContractSkill.ContractId FULL OUTER JOIN
                      #KPIS t1 ON Trisys.dbo.Contract.UserId = t1.ConsultantID AND c.ContractDate BETWEEN t1.StartRange AND t1.EndRange

                       GROUP BY t1.KPIID)
                      x1 inner join
(SELECT     KPIID, MAX(CVSend) AS CVSend, MAX(FirstInterviews) AS FirstInterviews
FROM         (SELECT     t2.KPIID, SUM(CASE WHEN CVSend = 1 THEN 1 ELSE 0 END) AS CVSend, 
                                              SUM(CASE WHEN FirstInterview = 1 THEN 1 ELSE 0 END) AS FirstInterviews
                       FROM          Trisys.dbo.TaskDate d INNER JOIN
                                              Trisys.dbo.TaskUsers ON d .TaskID = Trisys.dbo.TaskUsers.TaskId INNER JOIN
                                              #KPIS t2 ON d .CreatedDate BETWEEN t2.StartRange AND t2.EndRange AND 
                                              Trisys.dbo.TaskUsers.UserId = t2.ConsultantID
                       WHERE      (d .CVSend = 1) OR
                                              (d .FirstInterview = 1)
                       GROUP BY t2.KPIID
                       UNION ALL
                       SELECT     #KPIS.KPIID, 0 AS CVSend, 0 AS FirstInterviews
                       FROM         #KPIS) a
GROUP BY KPIID) x2 on x1.kpiid = x2.kpiid
) 
ot2 on ot1.KPIID = ot2.KPIID
drop table #KPIS
end
GO

Open in new window

0
dgloverukAuthor Commented:
I will get the database reindexed later tonight.
If the problem stays then I'll investigate further.
Why narrowing those .startrange and .endrange to the specific dates would cause that last select to time out is beyond me.
I tried to look at the execution plan as suggested Vitor but I got an error saying :
Server: Msg 208, Level 16, State 1, Procedure spGetResultsByScope, Line 55
Invalid object name '#Consultants'.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Something strange is that you have a return in the middle of the code, just before de DROP TABLE statement.
It seems that never do the rest of the code:
	Update #KPIS set StartRange = @StartDate where @StartDate > StartRange
	Update #KPIS set EndRange = @EndDate where @EndDate < EndRange
	select KPIID,KPIStartDate,StartRange,KPIEndDate,Endrange from #KPIS
	return <------- THIS ONE

	DROP TABLE #KPIList
	DROP TABLE #Consultants

Open in new window

0
dgloverukAuthor Commented:
Hi Vitor,
I requested this closed since your suggestion to reindex worked.  I reindexed just the Trisys.DBO.TaskDate table which  final Select command was scanning.
You are right there was a return although this was just something I put in when diagnosing the update commands previously to see what impact they were having.  It wasn't in the problem version, it just crept in when I was posting up to here!
Thanks again Vitor!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Let me just suggest you to avoid those 2 updates. You can do that if you validate the dates during the INSERT statement. Like this:
(...)
	insert into #KPIS(dbo.KPI.KPIID, dbo.KPI.ConsultantID, dbo.KPI.KPIName, dbo.KPI.KPIStartDate, dbo.KPI.KPIEndDate, dbo.KPI.TargetStartPositionsAdded, 
						  dbo.KPI.TargetEndPositionsAdded, dbo.KPI.TargetStartCVsSent, dbo.KPI.TargetEndCVsSent, dbo.KPI.TargetStartFirstInterviews, 
						  dbo.KPI.TargetEndFirstInterviews, dbo.KPI.TargetStartPermPlacements, dbo.KPI.TargetEndPermPlacements, dbo.KPI.TargetStartContractPlacements, 
						  dbo.KPI.TargetEndContractPlacements, dbo.KPI.TargetStartRangeInterviewsToStart, dbo.KPI.TargetEndRangeInterviewsToStart, 
						  dbo.KPI.TargetStartInnerInterviewsToStart, dbo.KPI.TargetEndInnerInterviewsToStart, dbo.KPI.TargetStartRangeCVsSentToInterview, 
						  dbo.KPI.TargetEndRangeCVsSentToInterview, dbo.KPI.TargetStartInnerCVsSentToInterview, dbo.KPI.TargetEndInnerCVsSentToInterview, 
						  dbo.KPI.TargetStartRangeCVsSentToPosition, dbo.KPI.TargetEndRangeCVsSentToPosition, dbo.KPI.TargetStartInnerCVsSentToPosition, 
						  dbo.KPI.TargetEndInnerCVsSentToPosition, dbo.KPI.WeightPositions, dbo.KPI.WeightCVsSent, dbo.KPI.WeightInterviews, dbo.KPI.WeightPermPlacements, 
						  dbo.KPI.WeightContractPlacements, dbo.KPI.WeightCVsSentToPosition, dbo.KPI.WeightCVsSentToInterview, dbo.KPI.WeightInterviewsToStart,
						  StartRange,  EndRange, dbo.KPI.Updated,
			DepartmentID,Department,Consultant,Grade,Active) 	
	SELECT     dbo.KPI.KPIID, dbo.KPI.ConsultantID, dbo.KPI.KPIName, dbo.KPI.KPIStartDate, dbo.KPI.KPIEndDate, dbo.KPI.TargetStartPositionsAdded, 
						  dbo.KPI.TargetEndPositionsAdded, dbo.KPI.TargetStartCVsSent, dbo.KPI.TargetEndCVsSent, dbo.KPI.TargetStartFirstInterviews, 
						  dbo.KPI.TargetEndFirstInterviews, dbo.KPI.TargetStartPermPlacements, dbo.KPI.TargetEndPermPlacements, dbo.KPI.TargetStartContractPlacements, 
						  dbo.KPI.TargetEndContractPlacements, dbo.KPI.TargetStartRangeInterviewsToStart, dbo.KPI.TargetEndRangeInterviewsToStart, 
						  dbo.KPI.TargetStartInnerInterviewsToStart, dbo.KPI.TargetEndInnerInterviewsToStart, dbo.KPI.TargetStartRangeCVsSentToInterview, 
						  dbo.KPI.TargetEndRangeCVsSentToInterview, dbo.KPI.TargetStartInnerCVsSentToInterview, dbo.KPI.TargetEndInnerCVsSentToInterview, 
						  dbo.KPI.TargetStartRangeCVsSentToPosition, dbo.KPI.TargetEndRangeCVsSentToPosition, dbo.KPI.TargetStartInnerCVsSentToPosition, 
						  dbo.KPI.TargetEndInnerCVsSentToPosition, dbo.KPI.WeightPositions, dbo.KPI.WeightCVsSent, dbo.KPI.WeightInterviews, 
						  dbo.KPI.WeightPermPlacements, dbo.KPI.WeightContractPlacements, dbo.KPI.WeightCVsSentToPosition, dbo.KPI.WeightCVsSentToInterview, 
						  dbo.KPI.WeightInterviewsToStart, 
						  CASE 
							WHEN @StartDate > dbo.KPI.KPIStartDate THEN @StartDate
							ELSE dbo.KPI.KPIStartDate 
						END AS StartRange, 
						CASE 
							WHEN @EndDate < dbo.KPI.KPIEndDate THEN @EndDate
							ELSE dbo.KPI.KPIEndDate 
						END AS EndRange, Trisys.dbo.Users.LastKPIUpdate,
				 FGDB.dbo.tblUser.DeptID as DepartmentID, FGDB.dbo.tblDepartment.Department, FGDB.dbo.tblUser.Firstname + ' ' + FGDB.dbo.tblUser.Surname AS Consultant,FGDB.dbo.tblUser.Grade, FGDB.dbo.tblUser.Active
	FROM         dbo.KPI 
	INNER JOIN #KPIList ON dbo.KPI.KPIID = #KPIList.KPIID 
	INNER JOIN Trisys.dbo.Users ON dbo.KPI.ConsultantID = Trisys.dbo.Users.UserId 
	INNER JOIN FGDB.dbo.tblUser ON Trisys.dbo.Users.UserId = FGDB.dbo.tblUser.TrisysUserID 
	INNER JOIN FGDB.dbo.tblDepartment ON FGDB.dbo.tblUser.DeptID = FGDB.dbo.tblDepartment.DeptID

	--Update #KPIS set StartRange = @StartDate where @StartDate > StartRange
	--Update #KPIS set EndRange = @EndDate where @EndDate < EndRange
	select KPIID,KPIStartDate,StartRange,KPIEndDate,Endrange from #KPIS
	--return

	DROP TABLE #KPIList
	DROP TABLE #Consultants
(...)

Open in new window

0
Anthony PerkinsCommented:
I reindexed the Trisys.DBO.TaskDate table which was the table being scanned for date criteria in the final select statement. This resolved the problem.
I am afraid that is a temporary fix at best and I suspect the timeouts will reoccur.  From a very cursory look at the Stored Procedure, here is what I found:
1. No indexes created on the temporary tables
2. Some urgent re-factoring of the larger queries.
3. You should be using OLEDB and not ODBC.

Without knowing the schema for the permanent tables let alone the indexes available it is difficult to be more precise.
0
dgloverukAuthor Commented:
Given the issue seemed to affect some dates and not others, I reindexed the Trisys.DBO.TaskDate table which was the table being scanned for date criteria in the final select statement.
This resolved the problem.
Thank you Vitor for your thoughts and your original post suggesting a reindex of this table.
First time I've encountered a functional need to do this but I won't forget this lesson!
Regards,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.