September 29, 2015 at 11:55 pm
This is odd. It's the first time I am using an aggregate with the OVER clause.
Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.
Why and what can I do?
I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.
if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM
CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money])
insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints])
selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,
min(MyTableID) over(partition by mm.ParticipantID) as [StartPoints],
max(MyTableID) over(partition by mm.ParticipantID) as [EndPoints],
min(Points) over(partition by mm.ParticipantID) as [LowPoints],
max(Points) over(partition by mm.ParticipantID) as [HighPoints]
fromMyTable mm
join(
select top 1000 ParticipantID
fromMyTable
whereMyTableID < (select max(MyTableID) as MyTableID from MyTable where DateCreated < dateadd(mm,-1,getdate()))
group by ParticipantID
order by ParticipantID
) t
ont.ParticipantID=mm.ParticipantID
order by mm.ParticipantID, mm.MyTableID
-----------------------------------------------------------------
-- without insert
-----------------------------------------------------------------
[font="Courier New"]
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 5 ms.
(9139 row(s) affected)
Table 'Worktable'. Scan count 3, logical reads 22279, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTable'. Scan count 1002, logical reads 44280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 109 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
[/font]
-----------------------------------------------------------------
-- with insert
-----------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.
Table 'MyTable'. Scan count 1006, logical reads 547713, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 12, logical reads 22282, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(9139 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 8969 ms, elapsed time = 8472 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
September 30, 2015 at 1:12 am
ignacio.jose (9/29/2015)
This is odd. It's the first time I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.
Why and what can I do?
I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.
if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM
CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money])
insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints])
selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,
min(MyTableID) over(partition by mm.ParticipantID) as [StartPoints],
max(MyTableID) over(partition by mm.ParticipantID) as [EndPoints],
min(Points) over(partition by mm.ParticipantID) as [LowPoints],
max(Points) over(partition by mm.ParticipantID) as [HighPoints]
fromMyTable mm
join(
select top 1000 ParticipantID
fromMyTable
whereMyTableID < (select max(MyTableID) as MyTableID from MyTable where DateCreated < dateadd(mm,-1,getdate()))
group by ParticipantID
order by ParticipantID
) t
ont.ParticipantID=mm.ParticipantID
order by mm.ParticipantID, mm.MyTableID
-----------------------------------------------------------------
-- without insert
-----------------------------------------------------------------
[font="Courier New"]
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 5 ms.
(9139 row(s) affected)
Table 'Worktable'. Scan count 3, logical reads 22279, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTable'. Scan count 1002, logical reads 44280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 109 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
[/font]
-----------------------------------------------------------------
-- with insert
-----------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.
Table 'MyTable'. Scan count 1006, logical reads 547713, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 12, logical reads 22282, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(9139 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 8969 ms, elapsed time = 8472 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Quick suggestion, try running the insert query with OPTION (MAXDOP 1).
😎
The insert query has a parallel execution plan, which I suspect is the main cause for the slowdown as the partitioning and aggregation of the streams is probably more costly than the actual gain.
Questions:
1) What is the Cost Threshold for Parallelism setting on the server?
2) Can you post the DDL for the "MyTable" and preferably some sample data as an insert statement?
3) Are there any POC indices on the table that support the Partitioning, Ordering and Covering for the Over clauses (key lookup in the plan suggest not)?
September 30, 2015 at 6:19 pm
Hi Eirikur,
Thank you for your message.
The "cost threshold for parallelism" is set to 50, I can see where you are going and I had a data type error for the column ConferenceID (incorrect nvarchar, correct int) on my temp table which push the overall subtree cost to 52 which cause the optimizer to use a different execution plan. Correcting the data type drop the subtree cost to 32.
We do have a covering indexes and MyTableID is the primary key. I have added some sample data, the StartPoints and EndPoints are really the first MyTableID and last MyTableID which I intend to use to get the StartPoints and EndPoints from the temp table.
Thanks you again and here is the additional information you requested.
CREATE TABLE [dbo].[MyTable](
[MyTableID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ConferenceID] [nvarchar](50) NOT NULL,
[ParticipantID] [int] NOT NULL,
[Points] [money] NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()),
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([MyTableID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_MyTalbe_Points] ON [dbo].[MyTable] ([Points] ASC) INCLUDE ([ParticipantID], [DateCreated])
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply