0

In my application I am using the below stored procedure, but it is taking a long time to execute. Both tables contain millions of records. Please guide me to optimize this procedure. I have searched a lot but not found any better way.

 CREATE PROCEDURE [dbo].[sp_SearchReportAdminPanel]
 @UserName nvarchar(max),
 @FromDate datetime,            
 @ToDate datetime,            
 @RecepientNumber nvarchar(max),            
 @Status nvarchar(max),            
 @SenderId nvarchar(max),  
 @PanelMasterId BIGINT = 1,
 @PageIndex INT = 1,        
 @PageSize INT = 10,        
 @RecordCount INT OUTPUT
 WITH RECOMPILE        
AS                      
BEGIN          
SET NOCOUNT ON;
DECLARE @SkipPageRank INT = (@PageIndex - 1) * @PageSize ;

SELECT temp.*,um.usr_Mobile_No as UserName INTO  #Results FROM 
( 
 SELECT XML_Id, Temp_Id, RIGHT(Mobile_number,10) as Recepient, Message_Text as Text,    
        Coalesce(Message_Status, 'Sent') as Status, CreatedDate,DeliveryDate,User_Id     
 FROM  Table1 xmd WITH (NOLOCK)            
 WHERE (xmd.CreatedDate between @FromDate AND @ToDate) and 
    (RIGHT(xmd.Mobile_number,10) = @RecepientNumber OR @RecepientNumber = '') and           
    (Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') and            
    (xmd.Sender_Id = @SenderId OR @SenderId = '')   
 UNION ALL
 SELECT XML_Id, Temp_Id, RIGHT(Mobile_number,10) as Recepient, Message_Text as Text,    
        Coalesce(Message_Status, 'Sent') as Status, CreatedDate,DeliveryDate,User_Id   
 FROM  Table2 xmd WITH (NOLOCK)            
 WHERE (xmd.CreatedDate between @FromDate AND @ToDate) and 
    (RIGHT(xmd.Mobile_number,10) = @RecepientNumber OR @RecepientNumber = '')   and         
    (Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') and            
    (xmd.Sender_Id = @SenderId OR @SenderId = '')
) temp INNER JOIN Table3 um WITH (NOLOCK) on temp.User_Id = um.usr_Id
WHERE
    (um.usr_Mobile_No = @UserName OR @UserName = '') and
    (um.PanelMasterId=@PanelMasterId)

   SELECT @RecordCount = COUNT(*) FROM #Results        

   select * from #Results
   ORDER BY XML_Id DESC    
   OFFSET @SkipPageRank ROWS
   FETCH NEXT @PageSize ROWS ONLY

    DROP TABLE #Results        
END 

Below is the query execution plan

1

Your use of this term

(Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') 

in your inner WHERE clauses is non SARG-able, so no index can be used. Change both occurrences as shown below, to make this term SARG-able:

(xmd.Message_Status  = @Status 
    OR (xmd.Message_Status is NULL AND @Status = 'Sent')
    OR @Status = '') 
| improve this answer | |
0

try pulling condition in the temp where up into the joins
filter early when you can

SELECT temp.*,um.usr_Mobile_No as UserName INTO  #Results FROM 
( 
 SELECT XML_Id, Temp_Id, RIGHT(Mobile_number,10) as Recepient, Message_Text as Text,    
        Coalesce(Message_Status, 'Sent') as Status, CreatedDate,DeliveryDate,User_Id     
 FROM  Table1 xmd WITH (NOLOCK)
 INNER JOIN Table3 um WITH (NOLOCK) 
        on Table1.User_Id = um.usr_Id 
       and (um.usr_Mobile_No = @UserName OR @UserName = '') 
       and (um.PanelMasterId=@PanelMasterId)            
 WHERE (xmd.CreatedDate between @FromDate AND @ToDate) and 
       (RIGHT(xmd.Mobile_number,10) = @RecepientNumber OR @RecepientNumber = '') and           
       (Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') and            
       (xmd.Sender_Id = @SenderId OR @SenderId = '')   
 UNION ALL
 SELECT XML_Id, Temp_Id, RIGHT(Mobile_number,10) as Recepient, Message_Text as Text,    
        Coalesce(Message_Status, 'Sent') as Status, CreatedDate,DeliveryDate,User_Id   
 FROM  Table2 xmd WITH (NOLOCK)
 INNER JOIN Table3 um WITH (NOLOCK) 
        on Table2.User_Id = um.usr_Id 
       and (um.usr_Mobile_No = @UserName OR @UserName = '') 
       and (um.PanelMasterId=@PanelMasterId)            
 WHERE (xmd.CreatedDate between @FromDate AND @ToDate) and 
       (RIGHT(xmd.Mobile_number,10) = @RecepientNumber OR @RecepientNumber = '')   and         
       (Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') and            
       (xmd.Sender_Id = @SenderId OR @SenderId = '')
) temp 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.