December 9, 2008 at 8:21 am
I am runnig the following SP which inserts like 3 million records is taking nearly 20hrs, the same proc i used to run before and it used to take 1.48 hrs, I have checked all the indexs too inthe reports idont see any such where it need reduild. is there anythingi can improve in my t-sql.
USE [RevDept_Procedures]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertRevenue]
@term as varchar(2),
@StageDB as varchar(25),
@termDB as varchar(25),
@label as varchar(50),
@termyr as varchar(4)
AS
BEGIN
while @termyr < '2009'
Begin
declare @query as varchar(1500)
declare @sql as varchar(1500)
set @query = 'insert into RevDept_goal.dbo.goal
select ''' + @term + ''', ''Start ' + @termyr + ' Reveiw delete ' + @term + ''' as BookMark,
getdate() as ExecutionTime, ''' + @label + ''''
exec(@query)
set @sql = 'Delete from ' + @termDB + '.dbo.Reveiw' + @termyr
exec (@sql)
set @query = 'insert into RevDept_goal.dbo.goal
select ''' + @term + ''', ''End ' + @termyr + ' Reveiw delete ' + @term + ''' as BookMark,
getdate() as ExecutionTime, ''' + @label + ''''
exec(@query)
set @query = 'insert into RevDept_goal.dbo.goal
select ''' + @term + ''', ''Start ' + @termyr + ' Reveiw insert ' + @term + ''' as BookMark,
getdate() as ExecutionTime, ''' + @label + ''''
exec(@query)
set @sql = 'Insert into ' + @termDB + '.dbo.Reveiw' + @termyr +
' select * from (
select RevEmpID, Servcdate, Rvw1 as Reveiw, FndgType
from ' + @termDB + '.dbo.Revenue' + @termyr +
' where Rvw1 is not null and (Rev_ID is null or Rev_ID=''SEN'')
union
select RevEmpID, Servcdate, Rvw2 as Reveiw, FndgType
from ' + @termDB + '.dbo.Revenue' + @termyr +
' where Rvw2 is not null and (Rev_ID is null or Rev_ID=''SEN'')
union
select RevEmpID, Servcdate, Rvw3 as Reveiw, FndgType
from ' + @termDB + '.dbo.Revenue' + @termyr +
' where Rvw3 is not null and (Rev_ID is null or Rev_ID=''SEN'')
union
select RevEmpID, Servcdate, Rvw4 as Reveiw, FndgType
from ' + @termDB + '.dbo.Revenue' + @termyr +
' where Rvw4 is not null and (Rev_ID is null or Rev_ID=''SEN'')
union
select RevEmpID, Servcdate, Rvw5 as Reveiw, FndgType
from ' + @termDB + '.dbo.Revenue' + @termyr +
' where Rvw5 is not null and (Rev_ID is null or Rev_ID=''SEN'')
) as cts'
exec (@sql)
set @query = 'insert into RevDept_goal.dbo.goal
select ''' + @term + ''', ''End ' + @termyr + ' Reveiw insert ' + @term + ''' as BookMark,
getdate() as ExecutionTime, ''' + @label + ''''
exec(@query)
set @termyr = @termyr + 1
End
END
December 9, 2008 at 8:46 am
Change the DELETE FROM to TRUNCATE, you're clearing out all rows and TRUNCATE is non-logged.
The UNION joins together 5 different result sets before inserting them into the target table - break this down into 5 separate INSERTs - you may be exceeding the "tipping point" http://www.sqlservercentral.com/Forums/Topic561839-360-1.aspx.
Keep things simple, change unnecessary dynamic sql:
set @query = 'insert into RevDept_goal.dbo.goal
select ''' + @term + ''', ''Start ' + @termyr + ' Reveiw delete ' + @term + ''' as BookMark,
getdate() as ExecutionTime, ''' + @label + ''''
exec(@query)
INSERT INTO RevDept_goal.dbo.goal
SELECT '' + @term + '',
'Start ' + @termyr + ' Reveiw delete ' + @term + '' AS BookMark,
getdate() AS ExecutionTime, '' + @label + ''
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply