SELECT INTO with Parallelism

Lets see one more feature of SQL Server 2014

Its SELECT INTO with parallelism

Select into supports parallelism and due to this time for inserting data into table is reduced



But this feature works only when database compatibility level is 120

Lets see an example





CREATE TABLE t
(id INT)

CREATE CLUSTERED COLUMNSTORE INDEX idx ON t



DECLARE @i INT
SET @i = 1
WHILE @i <= 10000000
BEGIN
       INSERT INTO t
       VALUES (@i)
       SET @i = @i  + 1
END




Now we will compare perfromance with older compatibility level



ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110

SELECT * INTO t1 FROM t

lets see plan for it


Also look at property of table insert ,it says parallel = false





Now we will run the query

It took 10 seconds


Now we will change compatibility level to 120


ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 120

SELECT * INTO t2 FROM t
lets see plan for it




Now look at property of table insert , it says parallel = true



Now we will run the query

It took 6 seconds

Its using parallelism while inserting data into table and hence speed is improved.

Here we can see the plan is different in both the cases.


Its a simply but noticable feature and do not requires any code change.







No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts