none
Prepared statement execution RRS feed

  • Question

  • Hi Experts, 

    I have a stored procedure SP_1 executed from an application using the sp_executeSQL on SQL Server 2017. SP_1 contains

    5 parameters one of the parameter contains a string of values on which string_split function is applied.

    The result is loaded into a In Memory table variable & joined back to main table to get the result set. Something like below.

    I think prepared statement can be expensive as my parameter values are not fixed , plus I could eliminate extra cost of string separator function. 

    the parameters keep on getting changed so I am thinking to suggest app developer to do direct proc execution for performance boost. 

    The columns used for filter are part of a primary composite key

    The application call the proc 20-30K reads per minute. 

    Drawback of the direct execution passing table valued parameter directly I found that I can't use the full DB.schema.object convention. 

    Please suggest changing to direct procedure execution will reduce execution time or not. Current execution time is 60-80 milliseconds so even fraction of gain for a multiple execution can benefit.

    Current proc & suggest proc

    Current
    
    CREATE  PROCEDURE [dbo].[sp_1]
    	@param_1 int
    	, @param_2 date
    	, @param_3 smallint
    	, @param_4 varchar(32)
    	, @param_5 varchar(1000) = NULL
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    	
    	BEGIN TRY
    		IF(@param_5 IS NOT NULL)
    		BEGIN
    			DECLARE @TVP AS dbo.TablevaluedParameterInMemory
    
    			INSERT INTO @TVP
    			SELECT VALUE FROM string_split(@param_5, ',')
    			
    			SELECT X.column1
    				, X.column2
    			
    			FROM	X
    			INNER JOIN @TVP  T
    				ON X.Column1 = T.Column1
    			WHERE   X.hotel_id = @param_1
    				AND X.check_in = @param_2
    				AND X.los      = @param_3
    				AND X.extra_key = @param_4
    		END
    
    		END
    
    
    
    Thinking to implement
    
    CREATE  PROCEDURE [dbo].[sp_2]
    	@param_1 int
    	, @param_2 date
    	, @param_3 smallint
    	, @param_4 varchar(32)
    	, @TVP  dbo.TablevaluedParameterMemory Readonly
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    	
    	BEGIN TRY
    		
    		IF EXISTS (SELECT 1 FROM @TVP)
    
    		BEGIN
    			
    			SELECT X.column1
    				, X.column2
    			
    			FROM	X
    			INNER JOIN @TVP  T
    				ON X.Column1 = T.Column1
    			WHERE   X.hotel_id = @param_1
    				AND X.check_in = @param_2
    				AND X.los      = @param_3
    				AND X.extra_key = @param_4
    		END
    
    		END

    Thanks 

    Priya

    Please suggest changing to direct procedure execution will reduce execution time or not. Current execution time is 60-80 milliseconds so even fraction of gain for a multiple execution can benefit.
    Thursday, January 23, 2020 6:00 AM

All replies

  • Why if condition .The If no record that is no effect and also manage by try block
    Thursday, January 23, 2020 6:35 AM
  • Hello, Its not the complete 1-1 copy of actual code. Thanks
    Thursday, January 23, 2020 7:10 AM
  • Hi Priya Bange,

    Thank you for your issue . 

    I've met a lot of people who have run into performance problems with encapsulating code in a stored procedure. I think first of all, decide if you really must turn your code into a stored procedure. Secondly, if we encounter performance problems, we will try to find a solution.

    This is one reason stored procedures might be slow. Please refer .http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 24, 2020 6:24 AM