SQL Server Performance

Query Tuning - Please help

Discussion in 'T-SQL Performance Tuning for Developers' started by getsrikar, Apr 4, 2008.

  1. getsrikar New Member

    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)+', &nbsp;'+CONVERT(char(12),@FDATE,110)+'&nbsp;&nbsp;to&nbsp;&nbsp;'+DATENAME(weekday, @TDATE)+', &nbsp;'+CONVERT(char(12),@TDATE,110)+'</th></tr><tr><td align=right>Today: '+DATENAME(weekday, getdate())+', &nbsp;'+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
  2. Luis Martin Moderator

    Did you try to find better indexes using ITW or DTA?

Share This Page