October 8, 2014 at 8:58 am
Why is the query on the "in-memory" table with the "native compiled" stored proc, slower than the equivalent on disk?
Tnx.
USE master;
-- Create database
CREATE DATABASE InMemory
ON PRIMARY(NAME = InMemoryData,
FILENAME = 'f:\MSSQL\data\InMemoryData.mdf', SIZE=200MB),
-- Memory Optimized Data
FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA(
NAME = [InMemory_InMem_dir],
FILENAME = 'f:\MSSQL\data\InMemory_InMem_dir')
LOG ON (name = [InMem_demo_log], Filename='f:\MSSQL\data\InMemory.ldf', SIZE=100MB)
GO
-- Create table
USE InMemory
GO
-- Create a Simple Table
CREATE TABLE dbo.DummyTable (ID INT NOT NULL PRIMARY KEY
,Name VARCHAR(100) NOT NULL
,INDEX IdxName (Name))
GO
-- Create a Memory Optimized Table
CREATE TABLE dbo.DummyTable_Mem (ID INT NOT NULL
,Name VARCHAR(100) COLLATE Latin1_General_BIN2 NOT NULL
,INDEX IdxName (Name)
,CONSTRAINT ID_Clust_DummyTable_Mem PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT=1000000)
) WITH (MEMORY_OPTIMIZED=ON)
GO
-- Create stored procedure to insrt 100,000 rows.
CREATE PROCEDURE Simple_Insert_test
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter AS INT = 1
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter <= 100000)
BEGIN
INSERT INTO dbo.DummyTable SELECT @counter, 'On disk test '+CONVERT(VARCHAR(21),GETDATE(),121)
SET @counter = @counter + 1
END
SELECT DATEDIFF(SECOND, @start, GETDATE() ) [Simple_Insert IN sec]
END
GO
-- Inserting same 100,000 rows using InMemory Table
CREATE PROCEDURE dbo.InMemory_Insert_test
WITH NATIVE_COMPILATION, SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='english')
DECLARE @counter AS INT = 1
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter <= 100000)
BEGIN
INSERT INTO dbo.DummyTable_Mem SELECT @counter, 'In-Memory test '+CONVERT(VARCHAR(21),GETDATE(),121)
SET @counter = @counter + 1
END
SELECT DATEDIFF(SECOND, @start, GETDATE() ) [InMemory_Insert IN sec]
END
GO
-- Create procedure to quuery some data.
CREATE PROCEDURE Simple_Query_test
AS
BEGIN
SET NOCOUNT ON
DECLARE @start DATETIME
SELECT @start = GETDATE()
SELECT MIN(id) AS MinID,MAX(id) AS MaxID FROM dbo.DummyTable
SELECT DATEDIFF(ms, @start, GETDATE() ) [Disk_Query IN ms]
SELECT @start = GETDATE()
SELECT MIN(Name) AS MinName,MAX(Name) AS MaxName FROM dbo.DummyTable
SELECT DATEDIFF(ms, @start, GETDATE() ) [Disk_Query IN ms]
END
GO
CREATE PROCEDURE dbo.InMemory_Query_test
WITH NATIVE_COMPILATION, SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='english')
DECLARE @start DATETIME, @MinName VARCHAR(100), @MaxName VARCHAR(100)
SELECT @start = GETDATE()
SELECT MIN(id) AS MinID,MAX(id) AS MaxID FROM dbo.DummyTable_Mem
SELECT DATEDIFF(ms, @start, GETDATE() ) [InMemory_Query IN ms]
SELECT @start = GETDATE()
--select MIN(Name) AS MinName,MAX(Name) AS MaxName from dbo.DummyTable_Mem
SELECT TOP 1 @MinName=Name FROM dbo.DummyTable_Mem ORDER BY name ASC
SELECT TOP 1 @MaxName=Name FROM dbo.DummyTable_Mem ORDER BY name DESC
SELECT @MinName, @MaxName
SELECT DATEDIFF(ms, @start, GETDATE() ) [Disk_Query IN ms]
END
GO
-- Running the test for Insert
EXEC Simple_Insert_test
GO
EXEC dbo.InMemory_Insert_test
GO
-- Running the test for Query
EXEC Simple_Query_test
GO
EXEC dbo.InMemory_Query_test
GO
-- Cleanup database.
USE master;
DROP DATABASE InMemory
GO
October 8, 2014 at 12:43 pm
You've created 100,000 rows, but they are in 1,000,000 buckets in your hash. That's a lot of empty space that's going to be scanned. I'd guess that's why you're seeing a lot of performance issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2014 at 2:37 pm
Yeah, I'd agree with Grant. It seems you are wasting a lot of memory and causing undue work with a less than optimal bucket count.
Here is a little light reading on the topic.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 9, 2014 at 8:56 am
I have changed my script, but same result.
Insert in-memory is faster, very fast (Disk: 74 sec, in-memory: 0 sec).
In-memory query is slower: Query on Disk: 0ms, in-memory: 20ms.
USE master;
-- Create database
CREATE DATABASE InMemory
ON PRIMARY(NAME = InMemoryData,
FILENAME = 'f:\MSSQL\data\InMemoryData.mdf', SIZE=200MB),
-- Memory Optimized Data
FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA(
NAME = [InMemory_InMem_dir],
FILENAME = 'f:\MSSQL\data\InMemory_InMem_dir')
LOG ON (name = [InMem_demo_log], Filename='f:\MSSQL\data\InMemory.ldf', SIZE=100MB)
GO
-- Create table
USE InMemory
GO
-- Create a Simple Table
CREATE TABLE dbo.DummyTable (ID INT NOT NULL PRIMARY KEY
,Name VARCHAR(100) NOT NULL
,INDEX IdxName (Name))
GO
-- Create a Memory Optimized Table
CREATE TABLE dbo.DummyTable_Mem (ID INT NOT NULL
,Name VARCHAR(100) COLLATE Latin1_General_BIN2 NOT NULL
,INDEX IdxName (Name)
,CONSTRAINT ID_Clust_DummyTable_Mem PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT=100000)
) WITH (MEMORY_OPTIMIZED=ON)
GO
-- Create stored procedure to insrt 100,000 rows.
CREATE PROCEDURE Simple_Insert_test
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter AS INT = 1
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter <= 100000)
BEGIN
INSERT INTO dbo.DummyTable SELECT @counter, 'On disk test '+CONVERT(VARCHAR(21),GETDATE(),121)
SET @counter = @counter + 1
END
SELECT DATEDIFF(SECOND, @start, GETDATE() ) [Simple_Insert IN sec]
END
GO
-- Inserting same 100,000 rows using InMemory Table
CREATE PROCEDURE dbo.InMemory_Insert_test
WITH NATIVE_COMPILATION, SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='english')
DECLARE @counter AS INT = 1
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter <= 100000)
BEGIN
INSERT INTO dbo.DummyTable_Mem SELECT @counter, 'In-Memory test '+CONVERT(VARCHAR(21),GETDATE(),121)
SET @counter = @counter + 1
END
SELECT DATEDIFF(SECOND, @start, GETDATE() ) [InMemory_Insert IN sec]
END
GO
-- Create procedure to quuery some data.
CREATE PROCEDURE Simple_Query_test
AS
BEGIN
SET NOCOUNT ON
DECLARE @start DATETIME
SELECT @start = GETDATE()
SELECT MIN(id) AS MinID,MAX(id) AS MaxID FROM dbo.DummyTable
SELECT DATEDIFF(ms, @start, GETDATE() ) [Disk_Query IN ms]
SELECT @start = GETDATE()
SELECT MIN(Name) AS MinName,MAX(Name) AS MaxName FROM dbo.DummyTable
SELECT DATEDIFF(ms, @start, GETDATE() ) [Disk_Query IN ms]
END
GO
CREATE PROCEDURE dbo.InMemory_Query_test
WITH NATIVE_COMPILATION, SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='english')
DECLARE @start DATETIME, @MinName VARCHAR(100), @MaxName VARCHAR(100)
SELECT @start = GETDATE()
SELECT MIN(id) AS MinID,MAX(id) AS MaxID FROM dbo.DummyTable_Mem
SELECT DATEDIFF(ms, @start, GETDATE() ) [InMemory_Query IN ms]
SELECT @start = GETDATE()
--select MIN(Name) AS MinName,MAX(Name) AS MaxName from dbo.DummyTable_Mem
SELECT TOP 1 @MinName=Name FROM dbo.DummyTable_Mem ORDER BY name ASC
SELECT TOP 1 @MaxName=Name FROM dbo.DummyTable_Mem ORDER BY name DESC
SELECT @MinName, @MaxName
SELECT DATEDIFF(ms, @start, GETDATE() ) [InMemory_Query IN ms]
END
GO
-- Running the test for Insert
EXEC Simple_Insert_test
GO
EXEC dbo.InMemory_Insert_test
GO
-- Running the test for Query
EXEC Simple_Query_test
GO
EXEC dbo.InMemory_Query_test
GO
-- Cleanup database.
USE master;
DROP DATABASE InMemory
GO
October 10, 2014 at 11:57 am
Part of the puzzle for the slowness is in how the query is written. The in-memory version can be written to be faster with a tweak here or there.
The other part of the puzzle is that your query definition is designed to not work that well with in-memory indexes. More specifically, the query you have requires querying the index in the reverse of the defined order, in order to fulfill the query specifications. This kind of query is clearly outlined to be better with a disk based index. Look at the entry for "Retrieve rows in a sort-order matching the reverse of the index definition." at the following link http://msdn.microsoft.com/en-us/library/dn133166.aspx.
That said, I was able to get your query to run consistently in 10ms. The query as provided originally ran in a total of 28ms (14ms to select the min and max id, and another 14ms to select the min and max names). By tweaking the code as follows, I now have it running in 10 ms total (10ms for min and max id, and 0ms for min and max name).
CREATE PROCEDURE dbo.InMemory_Query_test2
WITH NATIVE_COMPILATION, SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='english')
DECLARE @start DATETIME, @MinName VARCHAR(100), @MaxName VARCHAR(100),@MinID Int, @MaxID Int
SELECT @start = GETDATE()
SELECT @MinID = MIN(id),@MaxID = MAX(id) FROM dbo.DummyTable_Mem
Select @MinID as MinID,@MaxID as MaxID
SELECT DATEDIFF(ms, @start, GETDATE() ) [InMemory_Query IN ms]
SELECT @start = GETDATE()
--select MIN(Name) AS MinName,MAX(Name) AS MaxName from dbo.DummyTable_Mem
SELECT @MinName=Name FROM dbo.DummyTable_Mem where id = @MinId
SELECT @MaxName=Name FROM dbo.DummyTable_Mem where id = @MaxId
SELECT @MinName as MinName, @MaxName as MaxName
SELECT DATEDIFF(ms, @start, GETDATE() ) [InMemory_Query IN ms]
END
GO
In the end, if you have a scenario that matches this test harness, then you have to weigh the other queries that will hit this table to determine if the ~10ms each execution for this query type outweighs the performance gain of the other queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 10, 2014 at 12:03 pm
I also meant to include that one of the changes I made was to the bucket count. At 100,000, the original query took 14ms on each side of the min/max selects (as previously described). When changing to 10,000 for this query (or even 100), then the query improved to 10ms for each of the min/max segments being selected. Again, that hit is because of the hit for query in the reverse order of the defined hash index.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 10, 2014 at 3:50 pm
Thanks for walking through that. I kept meaning to get to it, but didn't have the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2014 at 3:07 am
OK, thanks.
Your example by replacing the select of the MIN(Name) and MAX(Name) by selecting the name of the Min and Max ID is not valid because in real live the min and max Name do not correspond with the min and max ID.
But I understand why my query was slow.
So in-memory tables are not always so optimal, especially in range and some functions (Count, Min or Max).
Rgds.
October 20, 2014 at 8:49 am
aarded (10/20/2014)
OK, thanks.Your example by replacing the select of the MIN(Name) and MAX(Name) by selecting the name of the Min and Max ID is not valid because in real live the min and max Name do not correspond with the min and max ID.
But I understand why my query was slow.
So in-memory tables are not always so optimal, especially in range and some functions (Count, Min or Max).
Rgds.
Ok. Thanks. I was only basing it off the example that you provided. I didn't know that your example was not based on your real-life scenario.
And yes, in memory is not a magic bullet for everything. As always, research and test over and over again.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply