SQL Server Performance

execution plan question

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by abbi, Mar 2, 2008.

  1. abbi New Member

    I have a query that accesses a table with a non-clustered index. When I drop the index and run the query, it takes 1.25 minutes to run. With the index, it takes 2 seconds to run. Yet, the execution plan is the same with or without the index. How can that be?
    Thank you!
  2. techbabu303 New Member

    Can you post the query ?
  3. abbi New Member

    The table that has the added index is called within a function that is being called by the stored procedure. I wonder if this is the problem?
    The table has 232,441 rows. It is SQL Server 2005. The number of distinct values for the index is 123,476.
    Here is the Stored Procedure:
    ALTER PROCEDURE [dbo].[gp_SelectContainerNew]
    (
    @RequestID int,
    @ContainerTypeID int
    )
    AS

    BEGIN
    SET NOCOUNT ON

    --display Containers
    SELECT
    C.ContainerBarcode
    ,PC.PCRCondition
    ,CONVERT(varchar,C.CreatedDt,101) AS CreatedDt
    ,SUBSTRING(U1.[User],Charindex('',U1.[User])+1,LEN(U1.[User])) AS CreatedBy
    FROM dbo.PCRCondition PC
    INNER JOIN dbo.Container C ON C.PCRCondition_id = PC.PCRCondition_id
    INNER JOIN View_User U1 ON U1.[User_id] = C.CreatedBy_id
    WHERE C.PCRRequest_id = @RequestID AND C.ContainerType_id = @ContainerTypeID AND C.IsActive = 1
    ORDER BY C.ContainerBarcode

    --display Container SlotsContainerBarcode
    SELECT
    C.ContainerBarcode
    ,CSD.Mouse_id
    ,CSD.TubeBarcode
    ,CS.Y+CS.X AS Slot
    ,dbo.GetPrimerPairList(CSD.Container_id, CSD.Slot_id) AS PrimerPairs
    ,S.Strain
    ,CS.Y
    ,CS.X
    FROM dbo.Container C
    INNER JOIN dbo.XREF_ContainerSlotData CSD ON CSD.Container_id = C.Container_id
    INNER JOIN dbo.LOOK_ContainerSlot CS ON CS.Slot_id= CSD.Slot_id
    INNER JOIN dbo.XREF_PCRRequestTail T ON T.Mouse_id=CSD.Mouse_id
    INNER JOIN Medusa.Medusa.dbo.Geno S ON S.Geno_id = T.Geno_id
    WHERE C.PCRRequest_id = @RequestID AND C.ContainerType_id = @ContainerTypeID AND C.IsActive=1
    ORDER BY ContainerBarcode,CS.Y,CS.X


    END

    The table that has the index is in a function that is called within the stored procedure.
    Here is the function that contains the table that the index was added to:
    ALTER FUNCTION [dbo].[GetPrimerPairList]
    (
    @Container_id INT
    , @Slot_id INT
    )
    RETURNS VARCHAR(2000)
    AS
    BEGIN
    DECLARE @List VARCHAR(2000)

    SET @List = ''
    SELECT @List = COALESCE(@List + ', ', '') + P.PrimerPair
    FROM dbo.PrimerPair P
    INNER JOIN XREF_ContainerSlotDataPrimerPair XP ON XP.PrimerPair_id = P.PrimerPair_id
    WHERE XP.Container_id = @Container_id AND XP.Slot_id = @Slot_id AND XP.IsActive=1

    RETURN SUBSTRING(@List,3,LEN(@List))
    END
  4. Adriaan New Member

    Please do not start multiple threads for the same issue.
  5. satya Moderator

    Locked as it refers a duplicate based thread.
  6. satya Moderator

    Are you using to force to use that index?
    How about defragmentation methods for indexes on this database?

Share This Page