|
getsrikar
Starting Member
2 Posts |
 Posted - 2008-04-04 : 14:26:02
|
| Hi All,I spent considerabale amount of time on tuning the following query.I tried to remove temp table with derived table,but while executing, i was getting errors.I dont have any idea about tuning on Cursors in the below query.My major concerns are, how can i replace cursors and temp tables in the below query.Any help from anyone will be greatly appriciated.... /* TOTAL TIME TAKEN FOR EXECUTION : 43 SECONDS */ declare @Month int declare @IDate datetime DECLARE @ptrval binary(16) declare @length int Declare @tempbody varchar(8000) declare @cmd varchar(100) declare @cmd1 varchar(100) declare @recordcount int Declare @FromEmail varchar(100) Declare @ToEmail varchar(300) Declare @DBName varchar(50) Declare @Body varchar(100) Declare @Subject varchar(100) Declare @Subject1 varchar(100) Declare @Count varchar(20) Declare @OutStand varchar(20) DECLARE @FDate datetime DECLARE @TDate datetime DECLARE @TodayDate datetime Declare @RequestBy varchar(50) Declare @QDate datetime Declare @QDay varchar(10) DECLARE @I int SET @Month = 2 SET @IDate = Null IF @IDate is Null SET @IDate = getdate() SET @TodayDate = @IDate SET @QDate = @TodayDate SET @QDAY = DATENAME(weekday, @QDATE) SET @I = 1 WHILE (@I <= @Month) BEGIN SET @FDate = dbo.FirstDayOfTheMonth(dbo.FirstDayOfTheMonth(@QDate)-1) SET @TDate = dbo.LastDayOfTheMonth(dbo.FirstDayOfTheMonth(@QDate)-1) SET @I = @I + 1 SET @QDATE = @TDATE END SELECT DISTINCT Masters_Users.firstname+' '+Masters_Users.lastname as 'RequestedBy', COUNT(NewCCaseNumber) as 'EUO Requested', SUM(case when convert(varchar,CaseManager_EUO.ResponseDate,101) = '12/31/9999' then 1 else 0 END) as 'EUO OutStand' INTO ##tmpEUO FROM CaseManager_EUO INNER JOIN Masters_ClaimCases ON CaseManager_EUO.ClaimCaseID = Masters_ClaimCases.ClaimCaseID INNER JOIN Masters_Vendors ON CaseManager_EUO.VendorID = Masters_Vendors.VendorID INNER JOIN Masters_Organisation ON Masters_Vendors.OrganisationID = Masters_Organisation.OrganisationID INNER JOIN Masters_Users ON CaseManager_EUO.CreatedBy = Masters_Users.UserID WHERE (Convert(varchar, CaseManager_EUO.RequestedDate, 112) between Convert(varchar, @FDate, 112) and Convert(varchar, @TDate, 112)) group by Masters_Users.firstname+' '+Masters_Users.lastname with Rollup DECLARE tmpcurEUORequest Cursor For SELECT DISTINCT Masters_Users.firstname+' '+Masters_Users.lastname as 'RequestedBy', COUNT(NewCCaseNumber) as 'EUO Requested', SUM(case when convert(varchar,CaseManager_EUO.ResponseDate,101) = '12/31/9999' then 1 else 0 END) as 'EUO Outstand' FROM CaseManager_EUO INNER JOIN Masters_ClaimCases ON CaseManager_EUO.ClaimCaseID = Masters_ClaimCases.ClaimCaseID INNER JOIN Masters_Vendors ON CaseManager_EUO.VendorID = Masters_Vendors.VendorID INNER JOIN Masters_Organisation ON Masters_Vendors.OrganisationID = Masters_Organisation.OrganisationID INNER JOIN Masters_Users ON CaseManager_EUO.CreatedBy = Masters_Users.UserID WHERE (CaseManager_EUO.RequestedDate between @FDate and @TDate) group by Masters_Users.firstname+' '+Masters_Users.lastname with Rollup SET @FromEMail = 'EUO-ToNF' SET @ToEMail = 'mail1@gmail.com;mail2@gmail.com'-- SET @ToEMail = 'mail1@gmail.com' SET @Body = '<html><body>Please find attached -- EUO Request Monthly Report . <br><br><br>NF Administrator</body></html>' select @DBName = db_name(dbid) from master..sysprocesses where spid=@@SPID SET @cmd = 'bcp '+@DBName+'.dbo.emailbody out c:\EUOMonthlyReport.html -c' SET @cmd1 = 'bcp ##tmpEUO out c:\EUOMonthlyReport.txt -c' set @recordcount = 0 delete from emailbody open tmpcurEUORequest Fetch next from tmpcurEUORequest Into @RequestBy, @Count, @OutStand insert into EmailBody (body) values ('<HTML><BODY><table width=100% border=0><tr><th align=center>American Transit Insurance Company</th></tr><tr><th align=center>No Fault - EUO Monthly Request</th></tr><tr><th align=center>Dates: '+DATENAME(weekday, @FDATE)+', '+CONVERT(char(12),@FDATE,110)+' to '+DATENAME(weekday, @TDATE)+', '+CONVERT(char(12),@TDATE,110)+'</th></tr><tr><td align=right>Today: '+DATENAME(weekday, getdate())+', '+CONVERT(char(19),getdate())+'</td></tr></table><br>'+ '<table border=1 cellspacing=0 cellpadding=2 width=100% ><tr><th align=center>Request By</th><th align=center>EUO Requested</th><th align=center>O/S EUO</th></tr>') While @@Fetch_Status = 0 Begin SET @tempbody = '<tr><td><b>'+CAST(ISNULL(@RequestBy, 'ALL EXAMINERS') AS VARCHAR)+'</b></td><td align=right>'+CAST(@Count as varchar)+'</td><td align=right>'+CAST(@OutStand as varchar)+'</td></tr>' SELECT @ptrval = TEXTPTR(body) FROM emailbody UPDATETEXT emailbody.body @ptrval NULL 0 @tempbody Fetch next from tmpcurEUORequest Into @RequestBy, @Count, @OutStand set @recordcount = @recordcount + 1 End SET @tempbody = '</table></body></HTML>' SELECT @ptrval = TEXTPTR(body) FROM emailbody UPDATETEXT emailbody.body @ptrval NULL 0 @tempbody exec master..xp_cmdshell @cmd exec master..xp_cmdshell @cmd1 -- DECLARE @SUBJECT VARCHAR(100) SET @SUBJECT = 'We REQUESTED '+CAST(@RECORDCOUNT AS VARCHAR)+' EUO. (HTML) ' SET @SUBJECT1 = 'We REQUESTED '+CAST(@RECORDCOUNT AS VARCHAR)+' EUO. (TXT) ' EXEC sp_send_cdosysmail @FromEmail,@ToEmail, @SUBJECT, @Body, 'c:\EUOMonthlyReport.html' EXEC sp_send_cdosysmail @FromEmail,@ToEmail, @SUBJECT1, @Body, 'c:\EUOMonthlyReport.txt' deallocate tmpcurEUORequest drop table ##tmpEUO |
|
SP Tuning-Help