HOW TO OPTIMEZES THE QUERY

Question

Post reply

HOW TO OPTIMEZES THE QUERY

harish.eeshu

Newbie

Points: 7
More actions
June 22, 2009 at 1:27 pm

#384821

I WRITTEN A STORED PROCEDURE LIKE THIS BUT ITS TAKING LONG TIME TO EXCEUTE,WE HAVE 50 TO 100 TABLES EACHTABLE IS HAVING RECORDS ABOVE 1,00,000 ,
ALTER PROCEDURE [dbo].[BSLIPolicykit_TOD_ReadyForArchiving]
@ConsoleDate NVARCHAR(100),
@PrintSuiteErrorDescription VARCHAR(MAX) out
AS
BEGIN
BEGIN TRY
CREATE TABLE #Temp(
PrintSuiteRowId NUMERIC(18,0),
PolicyNo VARCHAR(10),
PrintSuiteReadyForArchiving BIT,
PrintSuiteOutputStatus_Print INT,
PrintSuiteOutputStatus_CustomerHTML INT,
PrintSuiteConsoleDate DATETIME)
PRINT 'Rolling back archive status'
UPDATE t1_Summary SET PrintSuiteReadyForArchiving = 0 WHERE PrintSuiteReadyForArchiving = 1;
INSERT INTO #Temp
SELECT PrintSuiteRowId, PolicyNo, 1,PrintSuiteOutputStatus_Print, PrintSuiteOutputStatus_CustomerHTML, PrintSuiteConsoleDate
FROM t1_Summary
GROUP BY PrintSuiteRowId, PrintSuiteConsoleDate, PolicyNo, PrintSuiteOutputStatus_Print,PrintSuiteOutputStatus_CustomerHTML
HAVING PrintSuiteOutputStatus_Print = 60 and PrintSuiteOutputStatus_CustomerHTML>=30 AND PrintSuiteConsoleDate<=@ConsoleDate INSERT INTO #Temp
SELECT PrintSuiteRowId, PolicyNo, 1,PrintSuiteOutputStatus_Print, PrintSuiteOutputStatus_CustomerHTML, PrintSuiteConsoleDate
FROM t1_Summary
WHERE PrintSuiteConsoleDate<=@ConsoleDate
andt1_Summary.PolicyNo
in(select PolicyNo from #Temp with(nolock))
and t1_Summary.PrintSuiteRowId not in(select PrintSuiteRowId from #Temp with(nolock))
PRINT 'Making archive status true for despatched policies in main table'
UPDATE t1_Summary SET PrintSuiteReadyForArchiving=1 FROM #Temp
WHERE t1_Summary.PrintSuiteRowId=#Temp.PrintSuiteRowId
PRINT 'Making archive status true for I images'
UPDATE tAdditionalTransactionUploads set CanArchive=1
WHERE
tAdditionalTransactionUploads.ConsoleDate<=@ConsoleDate PRINT 'Making archive status true for tBSLIPolicyKit_DTR_DTRDetail' UPDATE T1 SET T1.PrintSuiteReadyForArchiving = 1 FROM tBSLIPolicyKit_DTR_DTRDetail T1, #Temp T2, tFilesUploaded T3
WHERE
T3.ConsoleDate <= @ConsoleDate AND
(LEN(T1.PrintSuiteRowId)=0 OR T1.PrintSuiteRowId=T2.PrintSuiteRowId) AND
T1.PrintSuiteUploadedFileId = T3.FileId;
PRINT 'Making archive status true for tPODDetails'
UPDATE T3 SET T3.CanArchive=1
FROM #Temp T1,tPODMaster T2,tPODDetails T3
WHERE
(T1.PrintSuiteRowId=T3.PrintSuiteRowId OR LEN(T3.PrintSuiteRowId)=0 ) AND
T2.ConsoleDate<=@ConsoleDate AND
T3.PODId=T2.Id
PRINT 'Making archive status true for BSLIPolicy Duplicates START'
UPDATE T3 SET T3.CanArchive=1
FROM #Temp T1,tUploadMaster T2,tBSLIPolicyDuplicates T3
WHERE
T1.PolicyNo=T3.PolicyNo AND
T2.ConsoleDate<=@ConsoleDate AND
T2.FileId=T3.SpoolFileId
PRINT 'Making archive status true for BSLIPolicy Duplicates END'
PRINT 'Making archive status true for tDespatchReturns START'
UPDATE tDespatchReturns SET CanArchive=1 WHERE PrintSuiteRowId IN(SELECT PrintSuiteRowId FROM #Temp)
PRINT 'Making archive status true for tDespatchReturns END '
PRINT 'Making archive status true for tQualityCheckConfirmationReportMaster START'
UPDATE tQualityCheckConfirmationReportMaster SET CanArchive=1
WHERE
ConsoleDate<=@ConsoleDate PRINT 'Making archive status true for tQualityCheckConfirmationReportMaster END'
PRINT 'MOVING FROM tBSLIPolicyDuplicates to tBSLIPolicyDuplicates_Archive start'
IF Not exists(SELECT * FROM sysobjects WITH (NOLOCK) WHERE name='tBSLIPolicyDuplicates_Archive')
SELECT * INTO tBSLIPolicyDuplicates_Archive FROM tBSLIPolicyDuplicates WHERE CanArchive=1
ELSE
BEGIN
SET IDENTITY_INSERT tBSLIPolicyDuplicates_Archive ON
INSERT INTO tBSLIPolicyDuplicates_Archive([Id],[SpoolFileId],[PolicyNo],[Count],[CanArchive])
SELECT [Id],[SpoolFileId],[PolicyNo],[Count],[CanArchive]
FROM tBSLIPolicyDuplicates T1
WHERE T1.CanArchive=1
SET IDENTITY_INSERT tBSLIPolicyDuplicates_Archive OFF
END
PRINT 'MOVING FROM tBSLIPolicyDuplicates to tBSLIPolicyDuplicates_Archive End '
PRINT 'MOVING FROM tPODDetails to tPODDetails_Archive start'
IF Not exists(SELECT * FROM sysobjects WITH (NOLOCK) WHERE name='tPODDetails_Archive')
SELECT * INTO tPODDetails_Archive FROM tPODDetails WHERE CanArchive=1
ELSE
BEGIN
SET IDENTITY_INSERT tPODDetails_Archive ON
INSERT INTO tPODDetails_Archive([Id],[PODId],[PODNumber],[Date],[PolicyNo],[Name],[City],[PrintSuiteRowId],[Remarks],[Origin],[PickupDate],[Status],[ReceivedTime],[ReceivedBy],[Relation],[Identity],[CanProcess],[CanArchive])
SELECT [Id],[PODId],[PODNumber],[Date],[PolicyNo],[Name],[City],[PrintSuiteRowId],[Remarks],[Origin],[PickupDate],[Status],[ReceivedTime],[ReceivedBy],[Relation],[Identity],[CanProcess],[CanArchive]
FROM tPODDetails T1
WHERE T1.CanArchive=1
SET IDENTITY_INSERT tPODDetails_Archive OFF
END
PRINT 'MOVING FROM tPODDetails to tPODDetails_Archive End '
PRINT 'MOVING FROM tPODMaster to tPODMaster_Archive start'
IF Not exists(SELECT * FROM sysobjects WITH (NOLOCK) WHERE name='tPODMaster_Archive')
SELECT * INTO tPODMaster_Archive FROM tPODMaster WHERE tPODMaster.Id NOT IN(SELECT DISTINCT PODId FROM tPODDetails WITH(NOLOCK))
ELSE
BEGIN
SET IDENTITY_INSERT tPODMaster_Archive ON
INSERT INTO tPODMaster_Archive([Id],[FileName],[ConsoleDate],[UploadedBy],[UploadedDate],[HashValue],[PolicyCount],[FileSize])
SELECT [Id],[FileName],[ConsoleDate],[UploadedBy],[UploadedDate],[HashValue],[PolicyCount],[FileSize]
FROM tPODMaster T1
WHERE T1.Id
NOT IN(SELECT DISTINCT PODId FROM tPODDetails WITH(NOLOCK))
SET IDENTITY_INSERT tPODMaster_Archive OFF
END
PRINT 'MOVING FROM tPODMASTER to tPODMASTER_Archive End '
PRINT 'MOVING FROM tDespatchReturns to tDespatchReturns_Archive start'
IF Not exists(SELECT * FROM sysobjects WITH (NOLOCK) WHERE name='tDespatchReturns_Archive')
SELECT * INTO tDespatchReturns_Archive FROM tDespatchReturns WHERE CanArchive=1
ELSE
BEGIN
SET IDENTITY_INSERT tDespatchReturns_Archive ON
INSERT INTO tDespatchReturns_Archive([Id],[PrintSuiteRowId],[JobId],[OutPutId],[PolicyNo],[ReturnAirwayBillNo],[ReturnDate],[ReturnBy],[IsSendtoSpeedPost],[AirwayBillNo],[AirwayBillDate],[ResentBy],[ResentReturnedDate],[ResentReturnedBy],[IsSendToHO],[HOSendDate],[DespatchreturnComments],[SpeedpostDespatchreturnComments],[CourierName],[CanArchive])
SELECT [Id],[PrintSuiteRowId],[JobId],[OutPutId],[PolicyNo],[ReturnAirwayBillNo],[ReturnDate],[ReturnBy],[IsSendtoSpeedPost],[AirwayBillNo],[AirwayBillDate],[ResentBy],[ResentReturnedDate],[ResentReturnedBy],[IsSendToHO],[HOSendDate],[DespatchreturnComments],[SpeedpostDespatchreturnComments],[CourierName],[CanArchive]
FROM tDespatchReturns T1
WHERE T1.CanArchive=1
SET IDENTITY_INSERT tDespatchReturns_Archive OFF
END
PRINT 'MOVING FROM tDespatchReturns to tDespatchReturns_Archive End'
PRINT 'MOVING FROM tQualityCheckConfirmationReportMaster to tQualityCheckConfirmationReportMaster_Archive start'
IF Not exists(SELECT * FROM sysobjects WITH (NOLOCK) WHERE name='tQualityCheckConfirmationReportMaster_Archive')
SELECT * INTO tQualityCheckConfirmationReportMaster_Archive FROM tQualityCheckConfirmationReportMaster WHERE CanArchive=1
ELSE
BEGIN
SET IDENTITY_INSERT tQualityCheckConfirmationReportMaster_Archive ON
INSERT INTO tQualityCheckConfirmationReportMaster_Archive([Id],[FileName],[UploadedBy],[UploadedDate],[ConsoleDate],[FileSize],[HashValue],[ExcelFormatNo],[ReportName],[Purpose],[User],[CanArchive])
SELECT [Id],[FileName],[UploadedBy],[UploadedDate],[ConsoleDate],[FileSize],[HashValue],[ExcelFormatNo],[ReportName],[Purpose],[User],[CanArchive]
FROM tQualityCheckConfirmationReportMaster T1
WHERE T1.CanArchive=1
SET IDENTITY_INSERT tQualityCheckConfirmationReportMaster_Archive OFF
END
PRINT 'MOVING FROM tQualityCheckConfirmationReportMaster to tQualityCheckConfirmationReportMaster_Archive End'
DELETE tBSLIPolicyDuplicates WHERE CanArchive=1
DELETE tPODDetails WHERE CanArchive=1
DELETE tDespatchReturns WHERE CanArchive=1
DELETE tQualityCheckConfirmationReportMaster WHERE CanArchive=1
DROP TABLE #Temp
END TRY
BEGIN CATCH
SET @PrintSuiteErrorDescription = ERROR_MESSAGE()+ERROR_LINE()
END CATCH
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Ok, you told us the number and size of the tables you're using. You also provided almost 200 lines of code.

What exactly is your question / expectation?

Also, since this looks like a performance related question please carefully read and follow the guidelines of this article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/.

Doing so will much more likely get you the answer you're looking for.

Note: Writing in capital letters is considered as yelling. There's no reason to yell at us.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

Without an execution plan (see the article that Lutz referenced), we can't tell. That long-distance brain-meld just isn't working, so we don't have enough information.

That being said, I would bet that your problem is in your indexes... or lack thereof... on the tables doing the updates. Without appropriate indexes, you're forcing sql to perform scans, and on many tables with > 1 million rows, this is a recipe for disaster.

So, get that execution plan to us, and we can better help you out.

Live long and prosper.....

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

David Betteridge Hall of Fame Points: 3264 More actions · Answer 3

My first step would be to run your query bit by bit to determine which bit(s) are taking too much time.

David

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 4

John Paul-702936

SSCrazy

Points: 2182

June 25, 2009 at 12:24 pm

#1015540

Post Execution Plan

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) forums such as this are for targeted, short problems where people that are volunteering their time can provide a solution in a matter of minutes. What you posted could take an experienced tuning professional days to wade through and completely optimize. If you really want to get this fixed (unless there is a single magic bullet somewhere in that mess), I strongly recommend you go route of hiring a pro to fix this. He/she can also teach you how to do this type of tuning analysis/improvement yourself for other sprocs.

2) Without any other knowledge, I would say that:

a) you are doing a LOT of table scans due to either no indexes on certain columns or those columns being so non-specific that indexes aren't used by the optimizer

b) you are using temp tables way too much - tempdb IO stalls could be issue

c) IN clauses such as you have all over the place can make it difficult for the optimizer to get a good plan

d) you do updates on the temp tables that probably could be in-lined into the temp table population inserts.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service