Performance question for a 500000 row insert.
-
Hello,
I have a table with 6 columns (with datatypes INT, DECIMAL, SMALLDATETIME). I have a script that is trying to insert row-by-row upto 500000 rows.
Its taking approx. 45 min, is this normal? The primary key is on int column that is automatically incremented.
Is there a way I can increase the performance?
Another question, while there is a data update (INSERT OR UPDATE) would it be ok to perform DBCCREINDEX on that table.
Thanks.
-
why not use a bulk insert method... row by row is gonna take way more time than necessary... I often see inserts of 250 k lines that take 1-2 seconds... 45 minutes is way too long.
-
Well, I am trying to create a test case.
Here is the code to create table.
CREATE TABLE dbo.TestTran (
TransactionID INT IDENTITY (1, 1) NOT NULL
, Amount DECIMAL(19,5) NULL
, DateInvested SMALLDATETIME NULL
, MgmtFee DECIMAL(12,5) NULL
, TypeID INT NULL
, SomeDate SMALLDATETIME NULL
)
GO
ALTER TABLE dbo.TestTran WITH NOCHECK ADD
PRIMARY KEY NONCLUSTERED
(
TransactionID
)
GO
Code to populate the table.
DECLARE @Counter INT
DECLARE @MaxCounter INT -- 2,147,483,647
DECLARE @NumberOfDays SMALLINT
DECLARE @RandomNumber DECIMAL(19,12)
DECLARE @DateInvested SMALLDATETIME
DECLARE @lDate smalldatetime
DECLARE @lYear TINYINT
SET @NumberOfDays = 365
SET @lDate = CONVERT(VARCHAR(10), getDate()-day(getDate()), 101)
SET @MaxCounter = 500000
SET @Counter = 1
WHILE @Counter <= @MaxCounter
BEGIN
SELECT @RandomNumber = RAND()
SELECT @lYear = RAND()*10
SET @DateInvested = CASE @lYear
WHEN 0 THEN @lDate-@NumberOfDays
WHEN 1 THEN @lDate-@NumberOfDays*2
WHEN 2 THEN @lDate-@NumberOfDays*3
WHEN 3 THEN @lDate-@NumberOfDays*4
WHEN 4 THEN @lDate-@NumberOfDays*5
WHEN 5 THEN @lDate-@NumberOfDays*6
WHEN 6 THEN @lDate-@NumberOfDays*7
WHEN 7 THEN @lDate-@NumberOfDays*8
WHEN 8 THEN @lDate-@NumberOfDays*9
WHEN 9 THEN @lDate-@NumberOfDays*10
ELSE @lDate-@NumberOfDays*11
END
--print @RandomNumber
SET NOCOUNT ON
INSERT INTO dbo.TestTran (
Amount
, DateInvested
, MgmtFee
, TypeID
, SomeDate)
VALUES (
@RandomNumber*10000000
, @DateInvested
, @RandomNumber
, CONVERT(SMALLINT, @RandomNumber*10)
, @lDate)
SET @Counter = @Counter + 1
SET NOCOUNT OFF
END
GO
-
There must be a faster way than this to generate the records.. but I don't have time to search for one. I would try to generate 1000 records in a select and see if I can expand that method to 500 k.. then I'd do a single insert select.... statement to generate your data.
-
This may be an issue with index maintenance during after each insert statement.
I ran your code as you gave it on against sql 7 and the inserts where finished in 1 min 14 secs consistantly
I then ran it against a sql 2000 instance on the same box and the inserts took 5 min 20 secs. Not looking good. but, remembering than MS added a process that would batch up the index maintenance tasks for an entire transaction, i added code to start a transaction, then commit every 100000 rows and start another transaction. The results with that version showed a consistant runtime of 1 min and 11 sec, marginally better than Sql 7, but significantly better than not using explicit transactions. I am guessing that Sql 2005 will behave more inline with sql 2000. Also, my play server here at home is just a dual pIII 600, 1GB RAM and Wide Ultra 7200 rpm non raid drives. I would expect a real server to perform much better.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp. -
Try something like this...
Set NoCount On
Declare @tbl Table(i1 Int,i2 Int,i3 Int,i4 Int)
Declare @i Int
set @i=0
While @i<50000 Begin
Set @i=@i+1
Insert @tbl values(@i,@i,@i,@i)
End
Select * into RealTable from @tbl
Create unique index IndexOnI1 on RealTable(i1)
On my personal development machine this script runs in about 3 seconds
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Hmmm,
Just noticed that you were running 500000 rows... In that case the script runs in 29 seconds
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
You might try dropping your index, run the insert, and re-create the index.
Robert
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply