Solved

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

Posted on 2014-09-15
21
136 Views
Last Modified: 2014-09-20
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!
0
Comment
Question by:dgloveruk
  • 9
  • 9
  • 2
  • +1
21 Comments
 

Author Comment

by:dgloveruk
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:dgloveruk
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:dgloveruk
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Anyway it's strange that the SP will give a timeout error just because the date format. At least should return no data.
0
 

Expert Comment

by:Shepherd Nhongo
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:dgloveruk
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Expert Comment

by:Shepherd Nhongo
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:dgloveruk
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Can you post inside a code tag? I can't copy like this.
0
 

Author Comment

by:dgloveruk
Comment Utility
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
 

Assisted Solution

by:dgloveruk
dgloveruk earned 0 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:dgloveruk
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Closing Comment

by:dgloveruk
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now