INSERT INTO Issue in SQL 2012
-
SQL Kiwi (8/21/2012)
No they're not.OK, I'm really curious what's going on behind the scene.
I managed to get identical plans on 2008 as well.
And different plans for the same queries launched from the same SSMS window.
--TRUNCATE TABLE [dbo].[Test]
DECLARE @initTime datetime
SET @initTime = GETDATE()
INSERT INTO [dbo].[Test]
(ID)
SELECT N
FROM dbo.Tally AS T
ORDER BY N
PRINT 'Order By N : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
--TRUNCATE TABLE [dbo].[Test]
SET @initTime = GETDATE()
INSERT INTO [dbo].[Test]
(ID)
SELECT N
FROM dbo.Tally AS T
ORDER BY N DESC
PRINT 'Order By N DESC : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
DECLARE @initTime datetimeSET @initTime = GETDATE()
INSERT INTO [dbo].[Test]
(ID)
SELECT N
FROM dbo.Tally AS T
ORDER BY N
PRINT 'Order By N : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
--TRUNCATE TABLE [dbo].[Test]
SET @initTime = GETDATE()
INSERT INTO [dbo].[Test]
(ID)
SELECT N
FROM dbo.Tally AS T
ORDER BY N DESC
PRINT 'Order By N DESC : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
I must to mention - I changed the environment a little bit by ecreating the table with a clustered index on (ID DESC).
It seems one of the queries uses previously cached plan, and another one had to create a new one, as it text differs a little bit from the cached SQL text.
Here identical plans make some sense, because SQL Server has to revert the order or Tally rows in both cases to insert them into dbo.Test.
_____________
Code for TallyGenerator -
GSquared (8/22/2012)
Based on that, I'll just pipe in with, "How dare you speak about those people that way! They've suffered in ways you can't possibly imagine! You are an evil, evil person!"Last argument of MVP's
:hehe:
_____________
Code for TallyGenerator -
Sergiy (8/23/2012)
OK, I'm really curious what's going on behind the scene.Let me explain. In an update query, the optimizer considers sorting the incoming rows into index order. Sorting into key order tends to result in more sequential access to the index, but there may be a cost involved in sorting. In deciding whether to sort of not, the optimizer weighs up the pros and cons and makes a cost-based decision
You can tell if the optimizer decided a sort was worthwhile or not by checking the properties of the update operator. If you see [DMLRequestSort] = True, the optimizer decided sorting was worth doing. Of course, you may or may not see an *explicit* sort in the plan. If the optimizer finds a plan that happens to produce rows in the desired order naturally, it can use that plan without a sort.
So, whether [DMLRequestSort] appears or not depends on a detailed calculation that I won't go into, expect to say that you are generally more likely to see it appear as the number of rows to be updated becomes larger.
Second point is that before SQL Server 2012, the optimizer always produced a plan as if an identity column existed. As Gail mentioned, ORDER BY in this context has only ever guaranteed that the order of assignment of identity values would match the ORDER BY clause. SQL Server 2012 contains an improvement so that the plan only has the shape required by the identity-assignment guarantee if the table actually does contain a column with the identity property.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply