SQL Server 2014: parallel execution of SELECT INTO statements

Forrards
March 30, 2023

In Microsoft SQL Server 2014 new in-memory options are presented. One of them is online transaction processing (OLTP) that complementing the existing business analytics and data storage options to create the most feature-rich solution for in-memory database. SQL Server 2014 also offers new cloud opportunities to simplify the process of migrating SQL databases to the cloud and discover new hybrid deployment scenarios.

Therefore, it is high time to start exploring what new and interesting is waiting for us in the new version of the most popular industrial DBMS.

Speaking of updates first we should turn to a number of documents, which also became available to the public:

But let’s go step by step and start with a new and very interesting feature in the SQL Server 2014 -  parallel execution of SELECT ... INTO statements.

We conduct the test in 2 versions:

SQL Server 2012

select1

SQL Server 2014

select2

In both versions we create table dbo.test_table and fill it with test data (65536 records)

set nocount on;
go

if object_id( N'dbo.test_table', N'U' ) is not null
drop table dbo.test_table;
go

create table dbo.test_table
( id int primary key
, dt datetime default getdate()
, uniqid uniqueidentifier default newid()
, val nvarchar(1024) default replicate( 'A', 1024 )
);
go

with
cte1 as ( select t1.* from ( values(1),(1) ) t1(i) )
, cte2 as ( select t2.* from cte1 t1 cross join cte1 t2 )
, cte3 as ( select t3.* from cte2 t2 cross join cte2 t3 )
, cte4 as ( select t4.* from cte3 t3 cross join cte3 t4 )
, cte5 as ( select t5.* from cte4 t4 cross join cte4 t5 )
insert into dbo.test_table ( id )
select row_number() over ( order by (select null) ) as id from cte5;
go

Then we insert all this data into a temporary table using SELECT ... INTO statement

set statistics time on;
set statistics io on;
go

checkpoint;
dbcc dropcleanbuffers;
go

select * into #t
from dbo.test_table;
go

and compare two execution plans:

SQL Server 2012

plan1

SQL Server 2014

plan2

Forrards
March 30, 2023
Share
Recent articles:
Database consultancy: SaaS database considerations
March 31, 2023

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

More
Database consultancy: tree-like structure
March 31, 2023

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

More
Database consultancy: Hierarchy in MS SQL Server
March 31, 2023

In our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.

More

Subscribe to our blog

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Database consultancy: SaaS database considerations

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

March 31, 2023
Database consultancy: tree-like structure

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

March 31, 2023

Discuss your project with us

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.