Click here to Skip to main content
15,900,907 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am getting time out on executing below query(SP)
SQL
 SELECT '5051'                               
 , ROW_NUMBER() OVER(ORDER BY DDRNextSenddate) as SponsoringBankSequenceNo                                
 , 'AUTH' as InstructionCode                                
 , MAINT.OriginatorIdentificationCode                                
 , MAINT.MandateReference                                
 , dbo.GetDirectDebitReferenceNumber(MAINT.MandateReference)                                   
 , 'AED' as CurrencyCode                                
 , MAINT.DirectDebitMinimumAmount as ClaimedAmount                    
 --, CASE WHEN ISNULL(OMAIN.AccountNowithBank,'') != ''                 
 --    THEN dbo.DDSDecrypt(OMAIN.AccountNowithBank)                 
 --    ELSE dbo.DDSDecrypt(MAINT.SettlmentAccount) END AS OriginatorAccountNumber                                
 , BNK.RoutingCode as PayingBank                                
 , dbo.DDSDecrypt(MAINT.Accountnumber) as Payer                                
-- , @Department                               
-- , @ip_CREATEDBY                  
 , Getdate()                 
 , 0                
 , 0                               
 ,GetDate()        
 ,ISNULL(MAINT.DDAType,'F')       
 ,NextDueDate                               
 FROM DDS_DDAMaintenance MAINT                                
 INNER JOIN DDS_Banks BNK ON BNK.EID=MAINT.PayingBankID                          
 INNER JOIN DDS_ORIGINATOR_MAINTANANCE OMAIN ON OMAIN.originatoridentificationcode=MAINT.originatoridentificationcode  



select COUNT(1) from DDS_DDAMaintenance--78455 RECORDS

select COUNT(1) from DDS_Banks--159 RECORDS

select COUNT(1) from DDS_ORIGINATOR_MAINTANANCE--18 RECORDS
While fetching from DDS_DDAMaintenance it takes only 3 sec
select * from DDS_DDAMaintenance--(3 SEC)

But on joining with DDS_Banks & DDS_ORIGINATOR_MAINTANANCE the execution time is quite high and getting timed out.

What I have tried:

select COUNT(1) from DDS_DDAMaintenance--78455 RECORDS

select COUNT(1) from DDS_Banks--159 RECORDS

select COUNT(1) from DDS_ORIGINATOR_MAINTANANCE--18 RECORDS
While fetching from DDS_DDAMaintenance it takes only 3 sec
select * from DDS_DDAMaintenance--(3 SEC)

But on joining with DDS_Banks & DDS_ORIGINATOR_MAINTANANCE the execution time is quite high and getting timed out.
Posted
Comments
HobbyProggy 26-Apr-16 2:23am    
Do you have indexes set on your database tables? Maybe you should set them on the identfying columns on which you join. This could enhance the performance enormously.
Tomas Takac 26-Apr-16 2:27am    
Your tables are quite small. I guess some of the functions in your select clause is causing you problems. Try to run your query as SELECT * instead to see if it still times out. Try to look at the estimated query plan (Ctrl+L in management studio).
Sinisa Hajnal 26-Apr-16 2:39am    
Try without DDSDecrypt in your select.
Also, put GetDate() into a variable and use that.
Check that you have indexes on every column used in joining.
If there is a link between dds_banks and dds_originator, you maybe need another join column?
James McCullough 26-Apr-16 9:26am    
Looking at your code, I was wondering if your ROW_NUMBER OVER ORDER BY clause might be costing you dearly? If you're using T-SQL, you could try running an execution plan and see where it's dragging its feet. That might help you narrow down at least where the worst cases are coming from. If you want help with the execution plan, let me know.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900