SQL Server Performance

Procedure with Parameters Performance

Discussion in 'T-SQL Performance Tuning for Developers' started by CyberNet36, Jun 22, 2004.

  1. CyberNet36 New Member

    We create a stored procedure like
    CREATE PROCEDURE test
    (@cli_id int)
    AS
    select * from BV_ADRESSE where CLI_ID = @cli_id

    when we try it in query Analyser (exec test 1), the execution time is
    SQL Server Execution Times:
    CPU time = 1281 ms, elapsed time = 4030 ms.

    when we do directly the query :

    declare @CLI_ID int
    SET @CLI_ID = 1
    SELECT CLI_ID FROM BV_ADRESSE Where [CLI_ID] = @CLI_ID
    the execution time is
    SQL Server Execution Times:
    CPU time = 109 ms, elapsed time = 30 ms.

    Why this difference ?
  2. derrickleggett New Member


    select * from BV_ADRESSE where CLI_ID = @cli_id

    SELECT CLI_ID FROM BV_ADRESSE Where [CLI_ID] = @CLI_ID


    This is "not" the same thing. You should never use * in a procedure; and it will make a difference in execution time. It also utilizes resources to figure out what the columns are.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. Argyle New Member

    Say the queries had been the same then the difference between the direct query and the stored procedure has to do with how sql server compiles the query plan.

    With the stored procedure, sql server doesn't know the value coming in (compared to the direct query where you do SET @CLI_ID = 1) and has to rely in index statistics. So if you send in value 5 to the procedure the first time a query plan will be built for value 5. But this might not be a good plan for the value 1234 etc.

    To solve this make sure you have updated statistics (sp_updatestats) or assign the stored procedure variables to local vairables inside the procedure and then use those local variables in the query. This allows sql server to build a better plan.

    Example:
    CREATE PROCEDURE test
    (@cli_id_sp int)
    AS
    DECLARE @cli_id INT
    SET @cli_id = @cli_id_sp
    select * from BV_ADRESSE where CLI_ID = @cli_id
  4. derrickleggett New Member

    minus the select * Why do people keep using that when they don't need to?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. vbkenya New Member

    Derrick...they don't know better. I guess that where the lemons start dropping from the sky.

    Step into Argyle's shoes and reconstruct your query. By the way, how much data do you have? What is the response time after running the query several times over? Does the execution plan change after you restructure the query?

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com

Share This Page