July 16, 2008 at 11:21 am
Can this query be tuned better than this.
DECLARE @ProcDescription varchar(50)
SET @ProcDescription = 'EmployeeHours'
DECLARE @LastUpdate datetime
SET @LastUpdate = GETDATE()
IF EXISTS(SELECT * FROM LinkServer.EmployeeDb.DBO.Procs_LastUpdate WHERE (ProcDesc = @ProcDescription))
BEGIN
SELECT @LastUpdate = LastDate FROM LinkServer.EmployeeDb.DBO.Procs_LastUpdate WHERE (ProcDesc = @ProcDescription)
END
DECLARE @Month varchar(2)
SET @Month = DATEPART(month,@LastUpdate)
DECLARE @Year varchar(4)
SET @Year = DATEPART(year,@LastUpdate)
DECLARE @StartDate datetime,
@EndDate datetime,
@StopDate datetime
--Back up two months
SET @StartDate = DATEADD(month,-2,@Month + '/1/' + @Year)
SET @EndDate = DATEADD(day,-1,DATEADD(month,1,@StartDate))
SET @StopDate = DATEADD(month,DATEDIFF(month,@StartDate,GETDATE()),@StartDate)
WHILE (@StartDate < @StopDate)
BEGIN
SET @StartDate = DATEADD(month,1,@StartDate)
SET @EndDate = DATEADD(day,-1,DATEADD(month,1,@StartDate))
EXEC PushEmployeeRollupToPortal @StartDate, @EndDate
END
--SET @EndDate = DATEADD(month,-1,@EndDate)
--Update the last Procs_LastUpdate table
IF EXISTS(SELECT * FROM LinkServer.EmployeeDb.DBO.Procs_LastUpdate WHERE (ProcDesc = @ProcDescription))
BEGIN
UPDATE LinkServer.EmployeeDb.DBO.Procs_LastUpdate SET LastDate = @EndDate WHERE (ProcDesc = @ProcDescription)
END
ELSE
BEGIN
INSERT LinkServer.EmployeeDb.DBO.Procs_LastUpdate(
ProcDesc,
LastDate
)
VALUES(
@ProcDescription,
@EndDate
)
END
July 16, 2008 at 12:34 pm
OK.
WHILE (@StartDate < @StopDate)
BEGIN
SET @StartDate = DATEADD(month,1,@StartDate)
SET @EndDate = DATEADD(day,-1,DATEADD(month,1,@StartDate))
EXEC PushEmployeeRollupToPortal @StartDate, @EndDate
END
Whatever is happening in PushEmployeeRollupToPortal, you're calling it a number of times, effectively RBAR. A better approach would be to pass it the parameters you want and process the data as a set. Not knowing what's in there, it's hard to suggest better approaches.
One other thing:
IF EXISTS(SELECT * FROM LinkServer.EmployeeDb.DBO.Procs_LastUpdate WHERE (ProcDesc = @ProcDescription))
BEGIN
UPDATE LinkServer.EmployeeDb.DBO.Procs_LastUpdate SET LastDate = @EndDate WHERE (ProcDesc = @ProcDescription)
END
ELSE
BEGIN...
That's a read you have to pay for one way or another, right. So let's pay for it once:
UPDATE LinkServer.EmployeeDb.DBO.Procs_LastUpdate SET LastDate = @EndDate WHERE (ProcDesc = @ProcDescription)
IF @@ROWCOUNT = 0
BEGIN...
After that, I'd have to see execution plans to make better suggestions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2008 at 1:43 am
Mike, as GSquared suggested on another thread, perhaps you should consider getting a competant SQL consultant in to help you with the myriad of performance problems that you seem to have.
While we can offer suggestions, we can't fix everything for you. I doubt any of us here have that kind of free time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 7:55 am
IF EXISTS(SELECT * FROM LinkServer.EmployeeDb.DBO.Procs_LastUpdate WHERE (ProcDesc = @ProcDescription))
BEGIN
SELECT @LastUpdate = LastDate FROM LinkServer.EmployeeDb.DBO.Procs_LastUpdate WHERE (ProcDesc = @ProcDescription)
END
This is another example of potentially hitting the data twice. Just do the SELECT in the first place, and if necessary for the logic set @lastupdate to something appropriate if you don't get a hit on the select.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply