SQL Server vs SQL Server v.Next

,

So, you ask "Why?" 

Well, firstly, because it is fun knowing.  In this short article, I'll try to convince you that SQL Server on Linux (v.Next) should not be discarded as a performance orientated database solution. Yes, it still comes with limitations, but I firmly believe Microsoft is onto something here.

For this test, I won't be discussing any feature comparison, license or support costs, v.Next is still in its infancy and should be allowed to be developed to its full potential as a complete Enterprise market offering.

The testing was done on the hardware available to me, 2 x laptops. Not the most comprehensive test, but for most sceptics, this should indicate something, and hopefully change views on the feasibility of using SQL Server on Linux from a performance point of view and start some converations and tests of your own.

Hardware specificiations of laptops used and SQL configuration:

Configurations
OS Physical Memory CPU HDD SQL Version SQL Maximum Memory CTfP
Windows 10 64 16GB

Core i7-5600 @2.6GHz

Toshiba 512GCSU SSD

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) 

10240

50

Linux (Ubuntu 16.10)
16GB

Core i7-4700 @2.4GHz

Samsung 850 SSD

Microsoft SQL Server v.Next (CTP1.2) - 14.0.200.24 (X64) 
                              10240 50

Note: The hardware is very similar, though some might argue the performance of the Toshiba is below that of the Samsung. What the SSD on the Windows instance lacks, it gains with it's faster CPU. 

Here's what we'll do:

  1. Create a test database, with a test table
  2. insert 999 999 rows, 3 times - raw, not indexes, just plain bad row inserts
  3. perform some updates to the data sets
  4. create an index on the table 
  5. review statistics and times

:

We have a very simple table, with 4 columns with generated characters. The database and table are similar in design, with no specific autogrowth settings or configurations applied. Here is the script.

create database test
go
use test
create table testimports
(column1 int, column2 varchar(100), column3 varchar(100), column4 varchar(100), column5 bit, column6 datetime)
go

For the data loads, we'll go ahead and load the 3 x runs of 999 999 rows each

declare @int int
set @int = 1
while @int < 1000000
 begin
 insert into testimports values
 (@int, LEFT(CAST(NEWID() AS VARCHAR(100)), 99), LEFT(CAST(NEWID() AS VARCHAR(100)), 99), LEFT(CAST(NEWID() AS VARCHAR(100)), 99),
 1, getdate())
 set @int = @int+1
end

Next we perform some updates. Bad queries and updates are a reality, so let's be real. We'll go ahead and replace every "D" with "%":

UPDATE [dbo].[testimports]
SET column2 = REPLACE(column2, 'D','%')

Next, we delete some rows with a LIKE '$4%'.

delete from [dbo].[testimports]
where column3 like '%4%' 

Finally, we'll go ahead and create a clustered index on these tables.

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndexSQLSERVER] ON [dbo].[testimports]
([column1] 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)
GO

Alright, I feel confident I've done enough bad coding, and bring my instruction testing to a quick conclusion.

Results!

Test

Platform

Run1

Run2

Run3

Insert 3 x999 999

SQL Server

3min17sec

3min28sec

3min38sec

SQL v.Next

1min58sec

2min33sec

3min06sec

Updates

SQL Server

9 Seconds

4 Seconds

4 Seconds

SQL v.Next

6 Seconds

3 Seconds

3 Seconds

Delete

SQL Server

10 Seconds

SQL v.Next

7 Seconds

Index Creation

SQL Server

3 Seconds

SQL v.Next

< 0 Seconds

SQL v.Next was the winner in each section, but what was interesting was during the 999 999 row inserts, SQL Server got slower consistantly by 10 seconds, while v.Next was slower by a rate of 30 seconds at each run, meaning, if another 2 INSERT runs were done, v.Next would have been slower by 8 seconds for run 5, and slower by 28 seconds at run 6.

A quick look proved that v.Next is by no means slower out of the box, and gave a full SQL Server a run for its money, and I am looking forward to what comes next. 

Rate

4.67 (9)

Share

Share

Rate

4.67 (9)