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:
|OS||Physical Memory||CPU||HDD||SQL Version||SQL Maximum Memory||CTfP|
|Windows 10 64||16GB|
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:
- Create a test database, with a test table
- insert 999 999 rows, 3 times - raw, not indexes, just plain bad row inserts
- perform some updates to the data sets
- create an index on the table
- 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.
Insert 3 x999 999
< 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.