Performance of a simple INSERT

Question

Post reply

Performance of a simple INSERT

Thilina-424914

Old Hand

Points: 317
More actions
June 12, 2007 at 3:23 am

#124178

Hi All,
We've tested following INSERT SQL as a part of simple load testing.
Machine configuration:
CPU : 1 x Intel 1.83Ghz Dual Core
Memory : 2GB
Database data File 512 MB and Log file 100MB, Auto Increment Switched off on both log & data file
Database on Simple Recovery mode
SQL Code
=========
DECLARE @i int
SET NOCOUNT ON
SELECT getdate()
SET @i = 1
while @i < 1000001
begin
insert into t1 (col1, col2, col3) values (1, getdate(), 'fooooo')
SET @i= @i + 1
end
SELECT getdate()
GO
Took nearly 8.5 minutes to complete. The similar INSERT Statement block on Oracle took 1 Minute (1Min 3 Secs) on the same machine.
What could be the reason for such a huge difference ? or is there any other suggestion to increase SQL Server Performance ?
Thanks in Advance
Thilina

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thilina-424914 Old Hand Points: 317 More actions · Answer 1

Changed the code adding BEGIN/COMMIT TRAN Block and increased the size of the transaction log into 300MB

1 Minute and 20 Seconds

Then write this as a SP and call it, 8 seconds

--create table t1 (col1 int, col2 datetime, col3 varchar(10))

DECLARE @i int

SET NOCOUNT ON

SELECT getdate()

SET @i = 1

BEGIN TRANSACTION T1

WHILE @i < 1000000

BEGIN

INSERT INTO t1 (col1, col2, col3) VALUES (1, getdate(), 'buddy')

SET @i= @i + 1

END

COMMIT TRANSACTION T1

SELECT getdate()

GO

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

inserts really test the disk subsystem. You altered the batching which is why you got the difference. You really want to make sure each insert is a page, your first iteration would have probably carried out a million page writes, in a batch it would have needed very few writes, hence the difference.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Thilina,

The real key is that Oracle caters to ISAM programmers (I call it "RBAR" which is pronounced "ree-bar" and is a Modenism for "Row By Agonizing Row") and cursors and loops, etc, etc. SQL Server does not. SQL Server works best with "Set Based" programming.

It's a major paradigm shift for Oracle programmers but is most easily explained as "stop writing procs that solve rows... write procs that solve columns in sets".

Here's the equivalent code for your WHILE loop... I say "equivalent" because what ends up in T1 is identical to what the WHILE loop created...

--===== Declare a variable to keep track of duration with

DECLARE @StartTime DATETIME

SET @StartTime = GETDATE()

--===== If the test table exists, drop it

IF OBJECT_ID('TempDB.dbo.#T1','U') IS NOT NULL

DROP TABLE #T1

--===== Create the test table as a temporary table

CREATE TABLE #T1 (Col1 INT IDENTITY(1,1), Col2 DATETIME, Col3 VARCHAR(20))

--===== Insert the same set of data as the WHILE loop example (takes less than 8 seconds)

-- The cross-join is intentional, in this case.

INSERT INTO #T1 (Col2,Col3)

SELECT TOP 1000000

GETDATE() AS Col2,

'fooooo' AS Col3

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Display how long this all took

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())/1000.0,7,3) + ' Seconds duration'

There are many different solutions for this same problem and none of them require a WHILE loop. It's gonna take your Oracle programmers a bit to get used to, but start thinking in columns and sets instead of rows. You'll write much better SQL Server code, that way. Of course, we're all here to help you and the folks you work with make the paradigm shift to SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Also, not sure what you were looking at, but even you second example took over a minute the first time I executed it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

The root cause of poor performance of the while-loop-no-tran solution is log flushes. REALLY BAD from a performance standpoint. Adding in explicit trans holds off on the flush until complete - but as you saw creates a pretty big log. If it is big enough to begin with (or you can withstand the growths) then no prob. "Free" performance gain.

You could also batch it in groups of 50K by using set rowcount or TOP clause and get the best of both worlds.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Thilina-424914 Old Hand Points: 317 More actions · Answer 6

Thanks for the clear explanation TheSQLGuru. I'm a beginner to the SQL Server world. For the moment nearly 300MB Log was ok for me. So I'll try that batch commit method as well.

Hi Jeff --

Thank you very much for the knowledge given. The code you suggested took nearly 78 seconds to run in the first time but after that it was going below 3-4 seconds. Is there a way of writing these data in a persistent manner. I mean because this is temporary table it dies after the session closed right ?

Anyway this code showed me some other ways that I can explore and learn to improve the performance.

Thank you very much Jeff & TheSQLGuru

Best Regards,

Thilina

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

The likely reason it took 78 seconds the first time is because it probably made TempDB larger... the default initial size of TempDB is woefully inadequate. Our production box at work has TempDB initial size set to 12 gig. TempDB is also used by SQL Server as a "working area" for making hash tables and the like during normal everyday queries.

Yes, you can make the table persist simply by not creating it as a temp table... create it as a permanent table in one of your databases. I usually post code to write to TembDB using temp tables so as not to interfere with anyone's actual code they may have... nothing worse than me overwritting your data by accident so I play it safe by using temp tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)