Geek Answers Handbook

Execution Plan Against ROW_NUMBER

I am trying to fine tune a query that I use ROW_NUMBERwith many parameters PARTITION BYto get a unique record from db. The query looks like the one below:

SELECT  SDP.SuspectID                       
        ,SDP.RecordID                   
        ,SDP.Field
        ,SDP.FieldEntryDate
        ,SDP.ScreenDate                 
        ,SDP.SuspectDetails              
        ,CONVERT(VARCHAR(10),SDP.PeriodEndDate,120) AS PeriodEndDate        
        ,ROW_NUMBER() OVER (PARTITION BY SDP.recordID, SDP.Field, SDP.ScreenerID, SDP.PeriodEndDate, SDP.PeriodID, SDP.[Source], SDP.SuspectDetails ORDER BY SDP.UploadDate) AS RowNum
        ,SDP.DatabaseAccountCode
        ,SDP.RecordUpdateType
INTO        #Temp
FROM        dbo.SuspectDataPoint
            AS SDP
LEFT JOIN   dbo.Screener
            AS S
            ON S.screenerID = SDP.screenerID                                                            
WHERE       SDP.ScreenerId not in (719)
            AND S.DatabaseName = 'db'
            AND CONVERT(DATE, SDP.FieldEntryDate) > DATEADD(dd,-24,GETDATE())

I tried to manually find what the most inefficient parts of this query were, and it turned out that it works> 10 times longer with ROW_NUMBERthan without.

, , -, . , , , - (45%). , ROW_NUMBER, % (61%). , ROW_NUMBER SORT, (25%) . , , , ROW_NUMBER, ( 1 13). - ? ?

enter image description here

EDIT:

enter image description here

EDIT:

/****** Object:  Table [dbo].[Screener]    Script Date: 10/17/2014 5:53:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Screener](
    [ScreenerID] [int] NOT NULL,
    [ScreenerName] [varchar](255) NOT NULL,
    [ScreenerDescription] [nvarchar](4000) NULL,
    [Script] [text] NULL,
    [HitRate] [numeric](10, 2) NOT NULL DEFAULT ((1)),
    [CreatedOn] [date] NULL,
    [CreatedBy] [varchar](7) NULL,
    [SuspectReason] [nvarchar](4000) NULL,
    [IsExtremeOutlier] [bit] NOT NULL DEFAULT ((0)),
    [DatabaseName] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ScreenerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SuspectDataPoint]    Script Date: 10/17/2014 5:53:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SuspectDataPoint](
    [SuspectID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [RecordID] [varchar](30) NOT NULL,
    [Field] [varchar](50) NOT NULL,
    [ScreenerID] [int] NOT NULL,
    [ScreenDate] [datetime] NOT NULL,
    [SuspectDetails] [nvarchar](4000) NULL,
    [PeriodEndDate] [datetime] NULL,
    [FieldEntryDate] [datetime] NOT NULL,
    [OriginalValue] [nvarchar](4000) NULL,
    [Source] [nvarchar](300) NULL,
    [UniqueSystemID] [int] NOT NULL,
    [DatabaseAccountCode] [varchar](50) NULL,
    [RecordUpdateType] [varchar](60) NULL,
    [UploadDate] [datetime] NOT NULL DEFAULT (getdate()),
    [PeriodID] [varchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
    [SuspectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [in_n_Screener_DatabaseName]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [in_n_Screener_DatabaseName] ON [dbo].[Screener]
(
    [DatabaseName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [in_n_SuspectDataPoint_DatabaseAccountCode]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [in_n_SuspectDataPoint_DatabaseAccountCode] ON [dbo].[SuspectDataPoint]
(
    [DatabaseAccountCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [in_n_SuspectDataPoint_Field]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [in_n_SuspectDataPoint_Field] ON [dbo].[SuspectDataPoint]
(
    [Field] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [in_n_SuspectDataPoint_RecordID]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [in_n_SuspectDataPoint_RecordID] ON [dbo].[SuspectDataPoint]
(
    [RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [in_n_SuspectDataPoint_ScreenerID]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [in_n_SuspectDataPoint_ScreenerID] ON [dbo].[SuspectDataPoint]
(
    [ScreenerID] ASC
)
INCLUDE (   [ScreenDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [in_n_SuspectDataPoint_UniqueSystemID]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [in_n_SuspectDataPoint_UniqueSystemID] ON [dbo].[SuspectDataPoint]
(
    [UniqueSystemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [SuspectDataPoint_FieldEntryDate_Index]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [SuspectDataPoint_FieldEntryDate_Index] ON [dbo].[SuspectDataPoint]
(
    [FieldEntryDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [SuspectDataPoint_RecordUpdateType_Index]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [SuspectDataPoint_RecordUpdateType_Index] ON [dbo].[SuspectDataPoint]
(
    [RecordUpdateType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [SuspectDataPoint_ScreenDate_Index]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [SuspectDataPoint_ScreenDate_Index] ON [dbo].[SuspectDataPoint]
(
    [ScreenDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [SuspectDataPoint_Source_Index]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [SuspectDataPoint_Source_Index] ON [dbo].[SuspectDataPoint]
(
    [Source] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [SuspectDataPoint_SuspectID_Index]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [SuspectDataPoint_SuspectID_Index] ON [dbo].[SuspectDataPoint]
(
    [SuspectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [SuspectDataPoint_UploadDate_Index]    Script Date: 10/17/2014 5:53:52 PM ******/
CREATE NONCLUSTERED INDEX [SuspectDataPoint_UploadDate_Index] ON [dbo].[SuspectDataPoint]
(
    [UploadDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Screener]  WITH CHECK ADD  CONSTRAINT [FK_Screener_DatabaseInfo] FOREIGN KEY([DatabaseName])
REFERENCES [dbo].[DatabaseInfo] ([DatabaseName])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Screener] CHECK CONSTRAINT [FK_Screener_DatabaseInfo]
GO
+4
1

, parallelism .

" " " ROW_NUMBER", , , ,

CONVERT(DATE, SDP.FieldEntryDate) > DATEADD(dd,-24,GETDATE())

where Row_Number OVER. WHERE SPD, , OVER . () (ed), ROW_NUMBER.

0

More articles:


All Articles