March 15, 2010 at 11:15 am
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? */
March 15, 2010 at 11:34 am
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
March 15, 2010 at 11:45 am
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? */
March 15, 2010 at 12:22 pm
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
March 15, 2010 at 2:08 pm
Consider these best practices:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply