Performance drop off with TOP X

  • I'm having some issues with getting the TOP X records for a query that runs in 10 seconds without the TOP. I'm not ordering the result set and when I choose TOP 20 records, it runs for 17+ minutes and never finishes. The query is to find all of the parentless Details. This query should return 0 results based on the current data. The indexes are not fragmented and auto update stats have been running. Attached are the actual execution plan that doesn't use TOP and the estimated TOP plan.

    ;WITH Excpt

    as

    (

    SELECT

    A.DetailID,

    A.ClaimID,

    B.MCareNo

    FROM dbo.udf_GetDetailsAsOfTaskListID(17, 0) AS A

    LEFT JOIN dbo.udf_GetClaimsAsOfTaskListID(17, 0) AS B

    ON A.ClaimID = B.ClaimID

    WHERE B.ClaimID IS NULL

    )

    SELECT TOP 20 DetailID, ClaimID, MCareNo, (SELECT COUNT(*) FROM EXCPT) AS TOTALCOUNT FROM EXCPT

    Quick background. We deal with medical records that have a header record (Claims) and many child (Details) records. We have an audit table that gets populated with each modification to either level, named [Claims/Details]_Checkpoints. The udfs get each set of records at any point in time. The udfs have functioned very well over the last two years dealing with 500+K Claims and 2M Details but now I'm hitting this odd problem that used to run fine. This is a system generated reporting query so it is asking for fields from B, even though they won't have any data. I want the TOP X records but also I need to know the total number of records.

    Here are the table counts:

    Claims_Checkpoints = 267.5K

    Details_Checkpoints = 1.7M

    AuditTable = 2.7M

    Tables:

    CREATE TABLE [dbo].[Claims_Checkpoints](

    [AuditID] [int] NOT NULL,

    [ClaimID] [int] NOT NULL,

    [MCareNo] [char](6) NOT NULL DEFAULT (''),

    [MCaidNo] [char](15) NOT NULL DEFAULT ('')

    CONSTRAINT [PK_Claims_Checkpoints] PRIMARY KEY CLUSTERED ([AuditID])

    )

    CREATE INDEX [ClaimID]

    ON [dbo].[Claims_Checkpoints] ([ClaimID])

    CREATE INDEX [MNos]

    ON [dbo].[Claims_Checkpoints] ([MCareNo],[MCaidNo]) INCLUDE ([ClaimID])

    GO

    CREATE TABLE [dbo].[Details_Checkpoints] (

    [AuditID] int NOT NULL,

    [DetailID] int NOT NULL,

    [ClaimID] int NOT NULL DEFAULT ((0))

    CONSTRAINT [PK_Details_Checkpoints] PRIMARY KEY CLUSTERED ([AuditID])

    )

    CREATE INDEX [DetailID]

    ON [dbo].[Details_Checkpoints] ([DetailID])

    CREATE INDEX [ClaimID]

    ON [dbo].[Details_Checkpoints] ([ClaimID])

    GO

    CREATE TABLE [dbo].[AuditTable] (

    [ID] [int] NOT NULL IDENTITY(1,1),

    [IdentityID] [int] NOT NULL DEFAULT ((0)),

    [TableName] [char](7) NOT NULL DEFAULT (''),

    [Action] [char](1) NOT NULL DEFAULT ('')

    CONSTRAINT [PK_AuditTable] PRIMARY KEY CLUSTERED ([ID] ASC)

    )

    CREATE INDEX [Table_Action_Identity]

    ON [dbo].[AuditTable] ([TableName],[Action],[IdentityID])

    CREATE INDEX [Table_Identity_Action]

    ON [dbo].[AuditTable] ([TableName],[IdentityID],[Action])

    -- should be used a lot with udf_Get[Tablename]AsOf[ID/TaskListID]

    CREATE UNIQUE INDEX [ID_Table_Identity_Action]

    ON [dbo].[AuditTable] ([ID],[TableName],[Action],[IdentityID])

    GO

    UDFs:

    CREATE FUNCTION [dbo].[udf_GetClaimsAsOfTaskListID]

    (

    @TaskListID int,

    @ReturnDeletes tinyint

    )

    RETURNS TABLE

    AS RETURN

    (

    SELECT a.*

    FROM Claims_Checkpoints as a

    WHERE EXISTS (SELECT

    MAX(ID)

    FROM AuditTable

    WHERE TableName = 'Claims'

    AND Action IN ('I', 'U', 'D')

    AND ID <= (SELECT EndRowVersion FROM v_TaskListInfo WHERE TasklistID = @TaskListID)

    GROUP BY IdentityID,

    CASE WHEN @ReturnDeletes = 0 THEN 'U' -- get all actions, deletes don't have a checkpoint record

    WHEN @ReturnDeletes = 1 AND Action <> 'D' THEN 'U' ELSE 'D' END -- don't remove any records, just find the latest

    HAVING MAX(ID) = a.AuditID)

    )

    GO

    CREATE FUNCTION [dbo].[udf_GetDetailsAsOfTaskListID]

    (

    @TaskListID int,

    @ReturnDeletes tinyint

    )

    RETURNS TABLE

    AS RETURN

    (

    SELECT a.*

    FROM Details_Checkpoints as a

    WHERE EXISTS (SELECT

    MAX(ID)

    FROM AuditTable

    WHERE TableName = 'Details'

    AND Action IN ('I', 'U', 'D')

    AND ID <= (SELECT EndRowVersion FROM v_TaskListInfo WHERE TasklistID = @TaskListID)

    GROUP BY IdentityID,

    CASE WHEN @ReturnDeletes = 0 THEN 'U' -- get all actions, deletes don't have a checkpoint record

    WHEN @ReturnDeletes = 1 AND Action <> 'D' THEN 'U' ELSE 'D' END -- don't remove any records, just find the latest

    HAVING MAX(ID) = a.AuditID)

    )

    /* Anything is possible but is it worth it? */

  • As an option, try the below changes.

    ;WITH Excpt

    as

    (

    SELECT

    A.DetailID,

    A.ClaimID,

    B.MCareNo

    ,Row_Number() over (Order by a.detailid) as RowNum

    FROM dbo.udf_GetDetailsAsOfTaskListID(17, 0) AS A

    LEFT JOIN dbo.udf_GetClaimsAsOfTaskListID(17, 0) AS B

    ON A.ClaimID = B.ClaimID

    WHERE B.ClaimID IS NULL

    )

    SELECT DetailID, ClaimID, MCareNo, (SELECT COUNT(*) FROM EXCPT) AS TOTALCOUNT

    FROM EXCPT

    Where RowNum <= 20

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (3/15/2010)


    As an option, try the below changes.

    ;WITH Excpt

    as

    (

    SELECT

    A.DetailID,

    A.ClaimID,

    B.MCareNo

    ,Row_Number() over (Order by a.detailid) as RowNum

    FROM dbo.udf_GetDetailsAsOfTaskListID(17, 0) AS A

    LEFT JOIN dbo.udf_GetClaimsAsOfTaskListID(17, 0) AS B

    ON A.ClaimID = B.ClaimID

    WHERE B.ClaimID IS NULL

    )

    SELECT DetailID, ClaimID, MCareNo, (SELECT COUNT(*) FROM EXCPT) AS TOTALCOUNT

    FROM EXCPT

    Where RowNum <= 20

    Thanks for that! I've used windowing functions to the same work but not sure why I was over looking it here. I made a slight change since I don't want the results sorted.

    ;WITH Excpt

    as

    (

    SELECT

    A.DetailID,

    A.ClaimID,

    B.MCareNo

    ,Row_Number() over (Order by (SELECT NULL)) as RowNum

    FROM dbo.udf_GetDetailsAsOfTaskListID(17, 0) AS A

    LEFT JOIN dbo.udf_GetClaimsAsOfTaskListID(17, 0) AS B

    ON A.ClaimID = B.ClaimID

    WHERE B.ClaimID IS NULL

    )

    SELECT DetailID, ClaimID, MCareNo, (SELECT COUNT(*) FROM EXCPT) AS TOTALCOUNT

    FROM EXCPT

    Where RowNum <= 20

    /* Anything is possible but is it worth it? */

  • You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Consider these best practices:

    http://bestpractices-sql.blogspot.com/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply