June 17, 2008 at 4:43 pm
I was curious if anyone has encountered this before or if they can shed some light on my scenario below:
On one SQL Server 2005 box if you execute the follow queries there is a dramatic speed difference
Scenario #1
{
CREATE TABLE #MYTABLE ( col1 varchar(255), col2 varchar(255), col3 varchar(255) )
INSERT INTO #MYTABLE
EXEC MyProc
MyProc
Select col1, col2, col3 from someTable -- This returns roughly 375k rows.
}
Now, if you use the procedure to insert the data
Scenario #2
{
CREATE TABLE #MYTABLE ( col1 varchar(255), col2 varchar(255), col3 varchar(255) )
EXEC MyProc
MyProc
INSERT INTO #MYTABLE
Select col1, col2, col3 from someTable -- This returns roughly 375k rows.
}
Now, Scenario #1 returns in roughly 4 minutes. However, Scenario #2 returns in 35 minutes.
The machine is relatively powerful, 16 core, 8gb ram and RAID setup. Transaction Log, TempDb and Database are all on different physical drives.
Does anyone have any thoughts to this?
June 17, 2008 at 6:01 pm
How strange... I did it with a million rows...
Scenario 1 = 36 seconds.
Scenario 2 = 8 seconds.
Here's the million rows of data...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 100,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
Col1 = IDENTITY(INT,1,1),
Col2 = ABS(CHECKSUM(NEWID()))%50000+1,
Col3 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
Col4 = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
Col5 = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
Col6 = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
Col7 = RIGHT(NEWID(),12)
INTO dbo.SomeTable
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.SomeTable
ADD PRIMARY KEY CLUSTERED (Col1)
Here's the two procs...
CREATE PROCEDURE MyProc1 AS
SELECT Col1,Col2,Col3 FROM SomeTable
GO
CREATE PROCEDURE MyProc2 AS
INSERT INTO #MyTable2
SELECT Col1,Col2,Col3 FROM SomeTable
GO
... and here's the test code...
--===== Scenario #1
CREATE TABLE #MyTable1 ( col1 varchar(255), col2 varchar(255), col3 varchar(255) )
SET STATISTICS IO ON
SET STATISTICS TIME ON
INSERT INTO #MyTable1
EXEC MyProc1
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE ('=',100)
--===== Scenario #2
CREATE TABLE #MyTable2 ( col1 varchar(255), col2 varchar(255), col3 varchar(255) )
SET STATISTICS IO ON
SET STATISTICS TIME ON
EXEC MyProc2
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO
--===== Rerun housekeepoing
DROP TABLE #MyTable1
DROP TABLE #MyTable2
The IO statistics clearly show why Scenario 1 is a pig as compared to Scenario 2. What's VERY odd, though, is when you turn on the actual execution plan... Scenario 1 doesn't show up on the execution plan.
Hey Grant! Here's another odd-ball example for execution plans.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 9:33 am
Ummmm. That was strange. Where the heck did the execution plan go?
If I run this query, I can see it.
SELECT
p.*,
q.*,
cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q
I couldn't begin to tell you why it disappears... That's weird.
"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
June 18, 2008 at 11:11 am
Version 1 has to create a worktable, put the data in there, then move it from the worktable to the temp table. Insert...Exec always works that way, so far as I know.
The other is Insert...Select, and doesn't have to work that way. It just puts the data directly into the temp table.
On Jeff's question about the disappearing execution plan, that is weird. I tried just running that part, with "Show Actual Execution Plan" selected, and it didn't even open up the tab on the results pannel for it. Very strange!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 6:17 pm
Grant Fritchey (6/18/2008)
Ummmm. That was strange. Where the heck did the execution plan go?
Cool... it's not just my machine and it's not just me... gettin' old enough where I have to check once in a while... 😉
Yeah, it is weird! Leave it to me to find the oddball stuff, huh?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2008 at 5:52 am
I wish I had a contact or three at MS. I'd love to see this moved up the chain... I'm going to try contacting a couple of people.
"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
June 19, 2008 at 11:10 am
So much for contacting people.
I'm still baffled. I'm not sure why this is happening like it is. Odd stuff.
"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
June 19, 2008 at 11:15 am
Send it to Steve Jones... he's an MVP... if he doesn't have contacts, no one has contacts... 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply