February 18, 2015 at 8:45 am
I had a puzzling experience with a script that performs an insert into a table based on a range of surrogate key values in the source.
Observation 1:
DECLARE @BeginVal INT = 1
DECLARE @EndVal INT = 100000
INSERT INTO TgtTable (col1,col2,...)
SELECT col1,col2,..
FROM SrcTable
WHERE surKey BETWEEN @BeginVal AND @EndVal
Return Time: >2min
----------------------------------------------------------------
Observation 2:
DECLARE @BeginVal INT = 1
DECLARE @EndVal INT = 100000
INSERT INTO TgtTable (col1,col2,...)
SELECT col1,col2,..
FROM SrcTable
WHERE surKey BETWEEN 1 AND 100000
Return Time: 9 seconds
----------------------------------------------------------------
Observation 3:
DECLARE @BeginVal INT = 1
DECLARE @EndVal INT = 100000
/*Created a stored proc to do insert */
EXEC usp_InsertIntoTgtTable @BeginVal, @EndVal
Return Time: 13 seconds
-------------------------------------------------------------------
Why would the statement run so long in observation 1?
Any insight would be greatly appreciated.
Letron
February 18, 2015 at 9:51 am
Warm Cache? Basically the pages would already be in memory from the first query so unless you flush the cache or they have expired the engine doesn't need to go to the disk to get the data.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 18, 2015 at 10:42 am
What are the query plans each time?
February 18, 2015 at 11:24 am
Compile time for the query and time to load everything into memory. Those are pretty standard costs that affect many queries the first time they're run.
"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
February 18, 2015 at 12:07 pm
The issue seems to be primarily with the variables however. To be clear, observation 1 never returned...I just cancelled it after 2 min because I knew that it shouldn't take that long. Also I should mention that the surKey column on the SrcTable is a primary clustered index.
After running observation 1 using variables (which never returns), I run observation 2 where I use the literal number values instead of the variables and it returns in 9 seconds. In the same SSMS session window, if I run observation 1 again where I use the variables for the predicate values, the query never returns.
But then in observation 3 (same SSMS session), if I pass the variables in as parameters into a stored procedure that performs my insert, it completes in 13 seconds.
What is it about using the variables that would prevent it from returning quickly in a simple select statement? The variables are declared as INT and I even put PRINT statements to verify the variable values before the insert statement. And why would it work better if I pass the variables in as parameters to a stored proc.
Letron
February 18, 2015 at 12:34 pm
What is the datatype of surKey and what are the execution plans?
February 18, 2015 at 12:35 pm
Throwing my spanner in the works, what does the following code return on your system?
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @BEGIN_VAL INT = 1;
DECLARE @END_VAL INT = 100000;
DECLARE @TIMER_RES TABLE(TR_TEXT VARCHAR(50) NOT NULL,TR_TIME DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME()));
IF OBJECT_ID(N'dbo.TBL_TEST_HEAP_INSERT_SOURCE') IS NOT NULL DROP TABLE dbo.TBL_TEST_HEAP_INSERT_SOURCE;
CREATE TABLE dbo.TBL_TEST_HEAP_INSERT_SOURCE
(
THIS_VAL INT NULL
);
IF OBJECT_ID(N'dbo.TBL_TEST_HEAP_INSERT_TARGET') IS NOT NULL DROP TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
CREATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET
(
THIS_VAL INT NULL
);
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_SOURCE(THIS_VAL)
SELECT
NM.N
FROM NUMS NM;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;
DBCC FREEPROCCACHE WITH NO_INFOMSGS ;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('FIRST INSERT');
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_TARGET(THIS_VAL)
SELECT
THIS_VAL
FROM dbo.TBL_TEST_HEAP_INSERT_SOURCE
WHERE THIS_VAL BETWEEN @BEGIN_VAL AND @END_VAL;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('FIRST INSERT');
TRUNCATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;
DBCC FREEPROCCACHE WITH NO_INFOMSGS ;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('SECOND INSERT');
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_TARGET(THIS_VAL)
SELECT
THIS_VAL
FROM dbo.TBL_TEST_HEAP_INSERT_SOURCE
WHERE THIS_VAL BETWEEN 1 AND 100000;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('SECOND INSERT');
TRUNCATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('THIRD INSERT');
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_TARGET(THIS_VAL)
SELECT
THIS_VAL
FROM dbo.TBL_TEST_HEAP_INSERT_SOURCE
WHERE THIS_VAL BETWEEN @BEGIN_VAL AND @END_VAL;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('THIRD INSERT');
TRUNCATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('FOURTH INSERT');
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_TARGET(THIS_VAL)
SELECT
THIS_VAL
FROM dbo.TBL_TEST_HEAP_INSERT_SOURCE
WHERE THIS_VAL BETWEEN 1 AND 100000;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('FOURTH INSERT');
TRUNCATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('FIRST INSERT 2');
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_TARGET(THIS_VAL)
SELECT
THIS_VAL
FROM dbo.TBL_TEST_HEAP_INSERT_SOURCE
WHERE THIS_VAL BETWEEN @BEGIN_VAL AND @END_VAL;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('FIRST INSERT 2');
TRUNCATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;
DBCC FREEPROCCACHE WITH NO_INFOMSGS ;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('SECOND INSERT 2');
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_TARGET(THIS_VAL)
SELECT
THIS_VAL
FROM dbo.TBL_TEST_HEAP_INSERT_SOURCE
WHERE THIS_VAL BETWEEN 1 AND 100000;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('SECOND INSERT 2');
TRUNCATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('THIRD INSERT 2');
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_TARGET(THIS_VAL)
SELECT
THIS_VAL
FROM dbo.TBL_TEST_HEAP_INSERT_SOURCE
WHERE THIS_VAL BETWEEN @BEGIN_VAL AND @END_VAL;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('THIRD INSERT 2');
TRUNCATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('FOURTH INSERT 2');
INSERT INTO dbo.TBL_TEST_HEAP_INSERT_TARGET(THIS_VAL)
SELECT
THIS_VAL
FROM dbo.TBL_TEST_HEAP_INSERT_SOURCE
WHERE THIS_VAL BETWEEN 1 AND 100000;
INSERT INTO @TIMER_RES(TR_TEXT) VALUES('FOURTH INSERT 2');
TRUNCATE TABLE dbo.TBL_TEST_HEAP_INSERT_TARGET;
SELECT
TR.TR_TEXT
,DATEDIFF(MICROSECOND, MIN(TR.TR_TIME),MAX(TR.TR_TIME)) AS DURATION
FROM @TIMER_RES TR
GROUP BY TR.TR_TEXT
ORDER BY DURATION ASC;
On mine it produces this results about 90% of the time, the second insert always coming last with around three times the duration of the others
TR_TEXT DURATION
------------------- -----------
THIRD INSERT 2 202011
FOURTH INSERT 202012
FIRST INSERT 2 203011
THIRD INSERT 203012
FOURTH INSERT 2 205012
FIRST INSERT 207012
SECOND INSERT 2 236014
SECOND INSERT 680039
February 18, 2015 at 2:39 pm
Local variables versus hard coded values versus parameters, you could be seeing parameter sniffing in action. The hard coded values will take an accurate measure of the statistics. The parameters will be sampled (sniffed) and use the same measure of the statistics. Local variables will use an average of the statistics. That could be it right there. As was mentioned several times, take a look at the execution plans.
"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
February 18, 2015 at 3:05 pm
Thanks SSCrazy, but unfortunately I don't have permission to run this on the particular box that is having the issue.
February 18, 2015 at 3:07 pm
I'm currently working with my DBA to get temporary SHOWPLAN permission. I will reply with details as soon as I can.
Thanks again guys,
Letron
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply