SQL Server Performance

Unbelievable

Discussion in 'General Developer Questions' started by hmckillop, Sep 25, 2003.

  1. hmckillop New Member

    I am trying to optimise a stored proc to remove table scans and would like anyone to provide a rational explanation to the problem I am about to explain.
    This is a simplified version of my stored proc.

    CREATE PROCEDURE usp_MyProc
    (
    @pi_UserOIDObjectID -- a user defined type of BIGINT
    )
    AS
    SET NOCOUNT ON
    Select * from MyTable Where UserOID = @pi_UserOID


    MyTable has many rows, but is indexed onthe field UserOID which is of ObjectID Type
    When I run the execution plan it says I perform a table scan rather than a bookmark and a clustered index seek.

    OK so If change the stored proc to be

    CREATE PROCEDURE usp_MyProc
    (
    @pi_UserOIDObjectID -- a user defined type of BIGINT
    )
    AS
    SET NOCOUNT ON
    DECLARE @v_MyID ObjectID
    SET @v_MyID = @pi_UserID
    Select * from MyTable Where UserOID = @v_MyID


    In the execution plan it performs as expected, a clustered index seek.


    WHY WHY WHY???

    Any answers are much appreciated.

    Thanks

  2. gaurav_bindlish New Member

    I think we are dealing with a typical case where caching of execution plan is creating problems.

    I think SQL Server generated an execution plan for this stored procedure when it was executed first based on the input value. Now if this input value was not good enough to make query selective, SQL Server decided to have table scan. For future queries also it is using the same plan.

    So if the input criteia varies so muh in your query, I would recommend you create this procedure using WITH RECOMPILE option so that every time a new plan will be developed based on the input value.

    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. hmckillop New Member

    I tried calling my stored proc using the WITH RECOMPILE directive but it doesnt stop the problem.

    Is it possible that SQL server thinks that a Table scan is more optimal/faster in certain cases?
  4. gaurav_bindlish New Member

    In that case you may have to force the use of index using an hint in the query.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. Twan New Member

    What are the indexes on the table? What is the PK?

    The clustered one is a unique index on UserOID?

    The parameter passed to the proc is never below or above the range of currently valid UserOIDs?

    Cheers
    Twan

Share This Page