August 28, 2008 at 1:23 pm
HI,
we are using sqlserver 2000 sp3a and sqlserverreporting2000 reporting services.
we have a database called HUB in sqlserver 2000 and a database called HUBR in sqlserverreporting server.
sqlserver and sqlreportingserver are installed in a different servers and i have a stored procedure in sqlreportingserver which moves the data from HUB to HUBR for reporting purposes but its talking 4 Hrs to complete.please help me in tuning this procedure.
/***********************************************************************************************************************************
Name : sp_DTS_PopulateReportingTables
Description : This SP is used to populate data FROM HUB database to Reporting Database
***********************************************************************************************************************************/
CREATE PROCEDURE [dbo].[sp_DTS_PopulateReportingTables]
AS
BEGIN
SET NOCOUNT ON
DECLARE @UpdateDateTime as DateTime
SET @UpdateDateTime = GETDATE()
DECLARE @DataTransferTime as DateTime
SELECT @DataTransferTime = MAX(DataTransferTime) FROM DataTransferDetails
DECLARE @Message as varchar(8000)
DECLARE @RowCount as Int
SET @Message = 'echo ------------------------------------DateTimeStamp: ' + CONVERT(VARCHAR, @UpdateDateTime) + '----------------------------- >> C:\DataTransferLog.txt'
EXEC master..xp_cmdshell @Message, no_output
--For Data Transfer using Created Date
BEGIN TRAN
INSERT INTO AUDITAREATABLES
SELECT
[AuditAreaTablesId],
[AuditArea],
[RelatedTableName],
[RelatedTableId],
[IsValid],
[CreatedBy],
[ModifiedBy],
[DateCreated],
[DateModified]
FROM [USESSQL].HUB.dbo.AUDITAREATABLES
WHERE DateCreated >= @DataTransferTime
--ERROR HANDLING
SET @RowCount = @@ROWCOUNT
IF @@ERROR = 0
BEGIN
SET @Message = 'echo AUDITAREATABLES Table populated successfully. Records Affected - ' + CONVERT(VARCHAR, @RowCount ) + ' Rows >> C:\DataTransferLog.txt'
EXEC master..xp_cmdshell @Message, no_output
END
ELSE
BEGIN
SET @Message = 'echo Problem in populating AUDITAREATABLES. ErrorId - ' + CONVERT(VARCHAR, @@ERROR) + ' >> C:\DataTransferLog.txt'
EXEC master..xp_cmdshell @Message, no_output
GOTO ERROR_HANDLER
END
TRUNCATE TABLE AUDITCOLUMNS
INSERT INTO AUDITCOLUMNS
SELECT * FROM [USESSQL].HUB.dbo.AUDITCOLUMNS
--ERROR HANDLING
SET @RowCount = @@ROWCOUNT
IF @@ERROR = 0
BEGIN
SET @Message = 'echo AUDITCOLUMNS Table populated successfully. Records Affected - ' + CONVERT(VARCHAR, @RowCount ) + ' Rows >> C:\DataTransferLog.txt'
EXEC master..xp_cmdshell @Message, no_output
END
ELSE
BEGIN
SET @Message = 'echo Problem in populating AUDITCOLUMNS. ErrorId - ' + CONVERT(VARCHAR, @@ERROR) + ' >> C:\DataTransferLog.txt'
EXEC master..xp_cmdshell @Message, no_output
GOTO ERROR_HANDLER
END
INSERT INTO AUDITLOG
SELECT [AuditLogID],
[TableID],
[RowsAffected],
[Event],
[PostedDateTime],
[UserName],
[HostName],
[ApplicationName]
FROM [USESSQL].HUB.dbo.AUDITLOG
WHERE PostedDateTime >= @DataTransferTime
--ERROR HANDLING
SET @RowCount = @@ROWCOUNT
IF @@ERROR = 0
BEGIN
SET @Message = 'echo AUDITLOG Table populated successfully. Records Affected - ' + CONVERT(VARCHAR, @RowCount ) + ' Rows >> C:\DataTransferLog.txt'
EXEC master..xp_cmdshell @Message, no_output
END
ELSE
BEGIN
SET @Message = 'echo Problem in populating AUDITLOG. ErrorId - ' + CONVERT(VARCHAR, @@ERROR) + ' >> C:\DataTransferLog.txt'
EXEC master..xp_cmdshell @Message, no_output
GOTO ERROR_HANDLER
END
INSERT INTO AUDITLOGDETAIL
SELECT ALD.AuditLogId,
ALD.RowKey,
ALD.ColumnID,
ALD.Status,
ALD.OldValue,
ALD.NewValue
FRO
August 28, 2008 at 5:01 pm
I have edited your post because it was too long and causing issues. I found > 67kb worth of a stored procedure.
No one is going to tune something that long. You need to examine the procedure, perhaps doing it in stages to find out what is the slow part and then ask questions about that part.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 29, 2008 at 2:54 pm
Adding to what Steve said analyze the execution plan and look for the costliest operator and try to tune the query part which is consuming the most of the cost as comapred to rest ones:
Query cost relative to the batch parameter.
MJ
August 29, 2008 at 3:17 pm
I have found that it is faster to call stored procedures across a linked server than to do a direct table select. You may want to try encapsulating you selects in stored procedures on HUB and then call the stored procedures across the linked server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply