Replica of Store Procedure taking more time

anoopms9 · April 5, 2018, 5:22am

Hi All,
I‌ have optimized one store procedure. Original is taking 35 seconds to take data and new SP is taking more than 1 min.

‌S‌o, I tried to make the replica of same SP with the same code, even that is taking more than 1 min.

‌S‌ummary is, new replica of same SP is taking also more than 1 min.

‌P‌lease suggests, if any one faced this issue. This is very urgent issue.

T‌hanks,‌‌‌‌

stepson · April 5, 2018, 6:15am

Probably bad parameter sniffing...

Both are pointing to the same db ?
See the execution plan for both SP
Add to that SP with recompile , for a new exec plan
See Your Session Settings = DBCC USEROPTIONS ; it's the same ?
slow in app

anoopms9 · April 5, 2018, 8:10am

Thanks for reply...

Yes, Same DB only SP with new name (Even code is same)...

same parameter are passing in both SP but result are different and I tested back to back 10 times both SP....

stepson · April 5, 2018, 9:15am

You should post the code for both SPs.
If the code is the same , the results should be the same.

anoopms9 · April 5, 2018, 10:08am

This is the problem, same sp is taking more time, if creating a new sp with same code, is any cache issue be possible?

stepson · April 5, 2018, 12:01pm

You have to look at the waiting taks (sys.dm_os_waiting_tasks) , session waits (sys.dm_exec_session_wait_stats).

same parameters = Yes (You confirmed that it's the same parameteres)
same User Options ? Yes/No
same shape of the execution plan ? Yes/No

Keep in mind about cold cache when you don't have it in memory (execution plan and data)

anoopms9 · April 6, 2018, 6:50am

Thanks for all, my challenge is the below code. If I am not using it then SP is taking 6 seconds to get data and after using it, taking more then 1 min
Please do you have any idea to optimize it

Update TMP1

Set DriverID = FDR.DriverID,
CompanyID = CDT.CompanyName
from @TempTable1 TMP1

   Inner Join (Select FaxID, CompanyID, LTrim(Replace(Stuff((Select  ', ' + ISNULL(Cast(FD1.DriverID as varchar(10)),'') From FaxId_Document_Relation FD1 Where FD1.FaxID = FD2.FaxID For XML PATH ('')),1,1,''),' ,','')) as DriverID

                    From FaxId_Document_Relation FD2
                      Where FaxID in (select FaxID from @TempTable1) 
                  Group By FaxID, CompanyID) as FDR ON TMP1.FaxID = FDR.FaxID
      --Inner Join CustomerDetails CDT on CDT.CompanyID = FDR.CompanyID --Anoop
	  Inner Join (select CompanyID, CompanyName from CustomerDetails) as CDT on CDT.CompanyID = FDR.CompanyID

stepson · April 6, 2018, 7:39am

Indexes? what index(es) do you have defined?
Can you post also the execution plan?

Because it's an update , then take into account transaction log : splitting pages, file grow,... you have to look at the wait stats, before and after update.
Take into account possible auto update statistics
(Possible replication, DR)

What recovery model do you have?

Can you post some dummy data , so we can test it

stepson · April 6, 2018, 7:42am

First observation:

Can you replace @TempTable1 TMP1 with a temp table #TempTable1 as TMP1 and add an index on FaxID
about table FaxId_Document_Relation what indexes do you have? (hope FaxID, CompanyID including DriverID)

anoopms9 · April 6, 2018, 8:00am

Thanks, now I am changing it to Temptable and I am also sending you the SP, if you can give valuable input on that, so it will be great for me.
And problem area, I already send above

CREATE Procedure [dbo].[SP_DQF_ALL_RunReport]

@RundateStart Date,

@RunDateEnd Date
As
Declare @TempTable Table (RunDate Date, CompanyID Varchar(100) null, FaxID DQF_TYPE_FAXID, DriverID varchar(200) NULL, StatusMessage Varchar(max), ProcessingStatus varchar(40), RecordType Varchar(2))

Declare @TempTableDocument Table (RunDate Date, CompanyID Varchar(100) null, FaxID DQF_TYPE_FAXID, DocumentType Varchar(100), DriverID varchar(200) NULL, StatusMessage Varchar(max), ProcessingStatus varchar(40), RecordType Varchar(2))

Insert Into @TempTable (RunDate, CompanyID, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Cast(CPS.DateofCreation as Date), CDT.CompanyName, CPS.FaxID, null, 'Pending for Data Entry', 'Pending Data Entry','FX'
from CMS_CPScreenData_Temp CPS
Inner Join DQF_FAXID_AUDIT_HDR HDR ON HDR.FaxID = CPS.FaxID And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd and CPS.FaxID > 0
Inner Join CustomerDetails CDT ON CDT.FADV_CustomerID = HDR.CustomerID
Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)
Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - Exceptional Document', 'Partial Document','FX'
from CMS_CPScreenData CPS
Inner Join FaxId_Document_Relation FDR on CPS.FaxID = FDR.FaxID and Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0
and CPS.IsReadyForRoboTwoProcessing = 2 and FDR.Robo2Remarks = 'Exceptional Document'
Where CPS.FaxID not in (select faxid from @TempTable)
Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)
Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - Exceptional Document', 'Partial Document','FX'
from CMS_CPScreenData CPS
Inner Join FaxId_Document_Relation FDR ON CPS.FaxID = FDR.FaxID And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0
Inner Join DocumentType DOT ON DOT.CustomerID = CPS.CustomerID and FDR.DocumentID = DOT.DocumentTypeID and DOT.IsException = 1
Where CPS.FaxID not in (select faxid from @TempTable)
Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)
Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - Unable to Login to Right Choice' , 'Exception','FX'
from CMS_CPScreenData CPS
Inner Join ErrorLog ELG ON CPS.CPScreenDataID = ELG.CPScreenDataID and ELG.ErrorMessage like '%Right Choice Login%'

                               And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd and CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Group By Cast(CPS.DateofCreation as Date), CPS.FaxID, ELG.CPSCreenDataID
Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)
Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'No Driver Found for SSN', 'Exception','FX'
from CMS_CPScreenData CPS
Inner Join ErrorLog ELG ON CPS.CPScreenDataID = ELG.CPScreenDataID and ELG.ErrorMessage like '%Process CMS Faxes%'
And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd and CPS.FaxID > 0
Where CPS.FaxID not in (select faxid from @TempTable)
Group By Cast(CPS.DateofCreation as Date), CPS.FaxID, ELG.CPSCreenDataID
Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)
Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'System Exception in ROBO 1 - Need to Check', 'Error','FX'
from CMS_CPScreenData CPS
Inner Join ErrorLog ELG ON CPS.CPScreenDataID = ELG.CPScreenDataID and ELG.ErrorMessage like '%System.Exception%' and ELG.RobotID = 1
And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd and CPS.FaxID > 0
Where CPS.FaxID not in (select faxid from @TempTable)
Group By Cast(CPS.DateofCreation as Date), CPS.FaxID, ELG.CPSCreenDataID

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)
Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'System Exception in ROBO 2 - Need to Check', 'Error','FX'
from CMS_CPScreenData CPS
Inner Join ErrorLog ELG ON CPS.CPScreenDataID = ELG.CPScreenDataID and ELG.ErrorMessage like '%System.Exception%' and ELG.RobotID = 2

                              And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd and CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)
Group By Cast(CPS.DateofCreation as Date), CPS.FaxID, ELG.CPSCreenDataID
Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)
Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'System Exception in ROBO 1 - Need to Check', 'Error','FX'

from CMS_CPScreenData CPS
Inner Join ErrorLog ELG ON CPS.CPScreenDataID = ELG.CPScreenDataID and (ELG.ErrorMessage like '%UIPath.Core%' OR ELG.ErrorMessage like '%UiPath.Core%') and ELG.RobotID = 1

                              And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd and CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Group By Cast(CPS.DateofCreation as Date), CPS.FaxID, ELG.CPSCreenDataID
Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'System Exception in ROBO 2 - Need to Check' , 'Error','FX'

from CMS_CPScreenData CPS

   Inner Join ErrorLog ELG ON CPS.CPScreenDataID = ELG.CPScreenDataID and (ELG.ErrorMessage like '%UIPath.Core%' OR ELG.ErrorMessage like '%UiPath.Core%') and ELG.RobotID = 2  

                               And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd and CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Group By Cast(CPS.DateofCreation as Date), CPS.FaxID, ELG.CPSCreenDataID
Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)
Select Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - ' + Replace(Substring(Stuff((Select ', ' + EL1.ErrorMessage From ErrorLog EL1 Where EL1.CPScreenDataID = ELG.CPSCreenDataID FOr XML PATH ('')),1,1,''), 1, 50),', ,',','), 'Exception'
,'FX'
from CMS_CPScreenData CPS
Inner Join ErrorLog ELG ON CPS.CPScreenDataID = ELG.CPScreenDataID And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd and CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Group By Cast(CPS.DateofCreation as Date), CPS.FaxID, ELG.CPSCreenDataID

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Cast(CPS.DateofCreation as Date), Cast(Substring(Cast(CPS.FaxID * -1 as Varchar(20)),1,9) as int), NULL, 'Data Entry Not Complete for Dummy FaxID' , 'Pending Duplicate Entry','FX'

from CMS_CPScreenData_Temp CPS

Where Cast(Substring(Cast(CPS.FaxID * -1 as Varchar(20)),1,9) as int) not in (select faxid from @TempTable)

   And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID < 0 AND cps.NoOfDataEntries < 2

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Cast(CPS.DateofCreation as Date), Cast(Substring(Cast(CPS.FaxID * -1 as Varchar(20)),1,9) as int), null, 'Data Entry Complete for Dummy FaxID but FaxID not released from hold', 'Hold','FX'

from CMS_CPScreenData_Temp CPS

   Inner Join RoboActivity RAC On CPS.RoboActivitiesID = RAC.RoboActivitiesID And Cast(RAC.StartTime as Date) >= @RundateStart and Cast(RAC.StartTime as Date) <= @RundateEnd and CPS.FaxID < 0 AND cps.NoOfDataEntries = 2

Where Cast(Substring(Cast(CPS.FaxID * -1 as Varchar(20)),1,9) as int) not in (select faxid from @TempTable)

  And CPS.FaxID < 0 AND cps.NoOfDataEntries < 2 And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Data Entry Complete - Pending for ROBO 1 Processing', 'Pending ROBO1','FX'

from CMS_CPScreenData CPS

   Inner Join FaxId_Document_Relation FDR ON CPS.FaxID = FDR.FaxID And FDR.ProcessingState = 0  

                                             And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Processing Complete', 'Success','FX'

from CMS_CPScreenData CPS

Where CPS.FaxID not in (select faxid from @TempTable) and CPS.IsReadyForRoboTwoProcessing = 2

   And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0

Insert Into @TempTable (RunDate, CompanyID, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Cast(CPS.DateofCreation as Date), CDT.CompanyName, CPS.FaxID, null, 'Pending for ROBO 2 Processing', 'Pending Robo2','FX'

from CMS_CPScreenData CPS

   Inner Join CustomerDetails CDT ON CDT.CompanyID = CPS.CustomerID

Where CPS.FaxID not in (select faxid from @TempTable) and CPS.IsReadyForRoboTwoProcessing = 1

  And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - Processed By Agent', 'Processed By Agent','FX'

from CMS_CPScreenData CPS

   Inner Join FaxId_Document_Relation FDR ON CPS.FaxID = FDR.FaxID And FDR.DriverFileCreationRemark = 'No Record  found'  

               And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - FaxID Locked', 'Exception','FX'

from CMS_CPScreenData CPS

   Inner Join FaxId_Document_Relation FDR ON CPS.FaxID = FDR.FaxID And FDR.DriverFileCreationRemark  like '%Fax ID Record is Locked%'  

               And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - Multiple Driver file exists' , 'Exception','FX'

from CMS_CPScreenData CPS

   Inner Join FaxId_Document_Relation FDR ON CPS.FaxID = FDR.FaxID And FDR.DriverFileCreationRemark like '%Multiple Driver file%'  

                                              And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - Driver File is Locked' , 'Exception','FX'

from CMS_CPScreenData CPS

   Inner Join FaxId_Document_Relation FDR ON CPS.FaxID = FDR.FaxID And FDR.DriverFileCreationRemark like '%Driver File is Locked%'  

                                              And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Insert Into @TempTable (RunDate, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Distinct Cast(CPS.DateofCreation as Date), CPS.FaxID, null, 'Exception - Sufficient Information Not Available to Proceed' , 'Exception','FX'

from CMS_CPScreenData CPS

   Inner Join FaxId_Document_Relation FDR ON CPS.FaxID = FDR.FaxID And FDR.DriverFileCreationRemark like '%Relevent information%'   

                                             And Cast(CPS.DateofCreation as Date) >= @RundateStart And Cast(CPS.DateofCreation as Date) <= @RundateEnd And CPS.FaxID > 0

Where CPS.FaxID not in (select faxid from @TempTable)

Insert Into @TempTable (RunDate, CompanyID, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Cast(RAC.StartTime as Date), CDT.CompanyName, FaxID, null, 'Deleted - No Data Entry', 'Deleted - Pending Data Entry','FX'

from DQF_FAXID_AUDIT_HDR AUH

   Inner Join RoboActivity RAC On AUH.RoboActivityID = RAC.RoboActivitiesID And Cast(RAC.StartTime as Date) >= @RundateStart and Cast(RAC.StartTime as Date) <= @RundateEnd and AUH.ProcessingState = 1  

   Inner Join CustomerDetails CDT On AUH.CustomerID = CDT.FADV_CustomerID

Where AUH.FaxID not in (select faxid from @TempTable)

  And AUH.FaxID not in (Select faxid from CMS_CPScreenData)

Insert Into @TempTable (RunDate, CompanyID, FaxID, DriverID, StatusMessage, ProcessingStatus, RecordType)

Select Cast(RAC.StartTime as Date), CDT.CompanyName, FaxID, null, 'Unknown Error', 'Error','FX'

from DQF_FAXID_AUDIT_HDR AUH

   Inner Join RoboActivity RAC On AUH.RoboActivityID = RAC.RoboActivitiesID And Cast(RAC.StartTime as Date) >= @RundateStart and Cast(RAC.StartTime as Date) <= @RundateEnd and AUH.ProcessingState = 1  

   Inner Join CustomerDetails CDT On AUH.CustomerID = CDT.FADV_CustomerID

Where AUH.FaxID not in (select faxid from @TempTable)

Update @TempTable

Set StatusMessage = 'Processed By Agent',

   ProcessingStatus = 'Processed By Agent'

Where StatusMessage like '%Processed By Agent%'

Update @TempTable

Set ProcessingStatus = 'Partial Document'

Where StatusMessage like '%Partial%'

Update TMP

Set DriverID = FDR.DriverID,

  CompanyID = CDT.CompanyName

from @TempTable TMP

   Inner Join (Select FaxID, CompanyID, LTrim(Replace(Stuff((Select  ', ' + ISNULL(Cast(FD1.DriverID as varchar(10)),'') From FaxId_Document_Relation FD1 Where FD1.FaxID = FD2.FaxID For XML PATH ('')),1,1,''),' ,','')) as DriverID  

                    From FaxId_Document_Relation FD2  

                      Where FaxID in (select FaxID from @TempTable)  

                  Group By FaxID, CompanyID) as FDR ON TMP.FaxID = FDR.FaxID  

      Inner Join CustomerDetails CDT on CDT.CompanyID = FDR.CompanyID

Insert Into @TempTableDocument

Select TMP.RunDate, TMP.CompanyID, TMP.FaxID,DOC.DocumentTypeName, TMP.DriverID, ISNULL(FDR.Robo2Remarks, FDR.DriverFileCreationRemark), TMP.ProcessingStatus, 'DL'

From @TempTable TMP

   Inner Join FaxId_Document_Relation FDR ON TMP.FaxID = FDR.FaxID  

      Inner Join DocumentType DOC ON FDR.DocumentID = DOC.DocumentTypeID

Where TMP.StatusMessage not in ('Pending for Data Entry', 'Data Entry Not Complete for Dummy FaxID', 'Data Entry Complete - Pending for ROBO 1 Processing', 'Pending for ROBO 2 Processing' )

Insert Into @TempTableDocument

Select RunDate, CompanyID, FaxID,'Null', DriverID, StatusMessage, ProcessingStatus, RecordType

From @TempTable

select *

from @TempTableDocument

Order By RecordType,RunDate, CompanyID, FaxID

Select RunDate, CompanyID, ProcessingStatus, COUNT(*)

From @TempTableDocument

Where RecordType = 'FX'

Group By RunDate, CompanyID, ProcessingStatus

Thanks

stepson · April 6, 2018, 8:10am

Here is Easter so will take some time to analyze it. I will take a look ...

OPTION (recompile) could help , so you could test it
I see you are using Cast(CPS.DateofCreation as Date) >= @RundateStart - this will not be SARGable
ELG.ErrorMessage like '%Right Choice Login%' - this will hard to make fast

stepson · April 6, 2018, 9:12am

You are touching ErrorLog table too many time.The same for FaxId_Document_Relation. Try to do it, in one go.

Not having sample data, it's hard for it.

Select CPS.FaxID
    , case  when ELG.ErrorMessage like '%Right Choice Login%' then 1  -- type Unable to Login to Right Choice
            when ELG.ErrorMessage like '%Process CMS Faxes%' then 2 -- type No Driver Found for SSN
		    when ELG.ErrorMessage like '%System.Exception%' 
		        and ELG.RobotID = 1 then 3  -- type System Exception in ROBO 1 - Need to Check
		    when ELG.ErrorMessage like '%System.Exception%' 
		        and ELG.RobotID = 2 then 4  -- type System Exception in ROBO 2 - Need to Check
		    when (ELG.ErrorMessage like '%UIPath.Core%' OR ELG.ErrorMessage like '%UiPath.Core%') 
			     and ELG.RobotID = 1 then 5 --type System Exception in ROBO 1 - Need to Check
			when (ELG.ErrorMessage like '%UIPath.Core%' OR ELG.ErrorMessage like '%UiPath.Core%') 
			     and ELG.RobotID = 2 then 6 --type System Exception in ROBO 1 - Need to Check
			else -1 
		end as errType
	,  case  when ELG.ErrorMessage like '%Right Choice Login%' then 'Unable to Login to Right Choice'
            when ELG.ErrorMessage like '%Process CMS Faxes%' then 'No Driver Found for SSN'
		    when ELG.ErrorMessage like '%System.Exception%' 
		        and ELG.RobotID = 1 then 'System Exception in ROBO 1 - Need to Check'
		    when ELG.ErrorMessage like '%System.Exception%' 
		        and ELG.RobotID = 2 then 'System Exception in ROBO 2 - Need to Check'
		    when (ELG.ErrorMessage like '%UIPath.Core%' OR ELG.ErrorMessage like '%UiPath.Core%') 
			     and ELG.RobotID = 1 then 'System Exception in ROBO 1 - Need to Check'
			when (ELG.ErrorMessage like '%UIPath.Core%' OR ELG.ErrorMessage like '%UiPath.Core%') 
			     and ELG.RobotID = 2 then 'System Exception in ROBO 1 - Need to Check'
			else ErrorMessage 
		end as errMsg
from CMS_CPScreenData CPS
         Inner Join ErrorLog ELG 
             ON CPS.CPScreenDataID = ELG.CPScreenDataID              
             And Cast(CPS.DateofCreation as Date) >= @RundateStart 
             And Cast(CPS.DateofCreation as Date) <= @RundateEnd 
             and CPS.FaxID > 0  
Where CPS.FaxID not in (select faxid from @TempTable)

This select could be used to populate a temp table and use this temp table to replace the 7/8 insert_select statements in one . The same idea is for FaxId_Document_Relation

Very important: if you have index on DateofCreation ,

              Cast(CPS.DateofCreation as Date) >= @RundateStart 
             And Cast(CPS.DateofCreation as Date) <= @RundateEnd

this is a no no.This should be rewritten in a SARGable way

anoopms9 · April 6, 2018, 9:27am

Very very thanks Sir...

after changing to #temptable and add cluster index, my time reduced from 35 seconds to 5-6 seconds....

I will implement given insert query at 1 go in second stage....

Thanks a lot...

stepson · April 6, 2018, 12:03pm

You're welcome!

Good luck!

Replica of Store Procedure taking more time

Thanks for all, my challenge is the below code. If I am not using it then SP is taking 6 seconds to get data and after using it, taking more then 1 min Please do you have any idea to optimize it

Thanks for all, my challenge is the below code. If I am not using it then SP is taking 6 seconds to get data and after using it, taking more then 1 min
Please do you have any idea to optimize it