CODEWITHSUNDEEP
sqlsql-serverinsert
Andreas
Andreas

Reputation: 301

SQL Server Insert Query Slow (1500rows, 4.5sec)

I am running a local SQL Server server and i am doing a insert query of about 1500 rows and this takes such a long time that im quite amazed really. I have googled and searched in here and found quite many ideas and solutions but still find it to be way to slow, i mean its 1500 rows!

My question is this, is there any other way to do a insert of this amount? Is it really too many rows to insert so i need to load it from a csv file?

My computer is Win7 32bit, 4GB RAM, Core2Duo 1.86GHZ SQL Server Version 10.50.1617

First query

USE CustomerDB;

IF OBJECT_ID('Customer', 'U') IS NOT NULL
DROP TABLE Customer;

CREATE TABLE Customer
( 
CustomerID int PRIMARY KEY IDENTITY,
CustomerName nvarchar(16),
...about 130 more columns...
);

INSERT INTO Customer VALUES
('FirstCustomerName', ...),
...1500 more rows...
('LastCustomerName', ...)

After well over 10minutes and still not finished i stopped query since this is obviously wrong.

Second query:

USE CustomerDB;

IF OBJECT_ID('Customer', 'U') IS NOT NULL
DROP TABLE Customer;

CREATE TABLE Customer
( 
CustomerID int PRIMARY KEY IDENTITY,
CustomerName nvarchar(16),
...about 130 more columns...
);

INSERT INTO Customer VALUES
('FirstCustomerName', ...);
INSERT INTO Customer VALUES
('SecondCustomerName', ...);
...1500 more ...
INSERT INTO Customer VALUES
('LastCustomerName', ...);

Ok, down to 6.5seconds, much better but this still feels very slow for 1500rows

Third query:

USE CustomerDB;

IF OBJECT_ID('Customer', 'U') IS NOT NULL
DROP TABLE Customer;

begin transaction Insert;

CREATE TABLE Customer
( 
CustomerID int PRIMARY KEY IDENTITY,
CustomerName nvarchar(16),
...about 130 more columns...
);

INSERT INTO Customer VALUES
('FirstCustomerName', ...);
INSERT INTO Customer VALUES
('SecondCustomerName', ...);
...1500 more ...
INSERT INTO Customer VALUES
('LastCustomerName', ...);

commit transaction Insert;

Ok, down to 4.5-5 seconds, even better but still kinda slow i think

Im not a DB/SQL Server guru so this is as far as i have come on my own, help would be appreciated!

Edit: Since I'm no guru, is 4.5 seconds considered to be a ok amount of time on a normal pc running sql server for a 1500 row insert?

Upvotes: 2

Views: 11654

Answers (6)

Samith C Valsalan
Samith C Valsalan

Reputation: 139

As per my understanding your number of columns make sens. But still you check the below points that help you.

  1. If a row size crosses 8 kb (size of a page) sql forced to create 1 more page to store a single row.
  2. Check your not used char data type where variable data length values are present ( total length of the char columns would be stored in that space this would increase size).
  3. Please avoid use of Varchar(max) unless and until your expecting such huge data in the row. varchar(max),nvarchar(max), images are stored in a different page if we are not enable the text in row property.
  4. This is a huge time - based on rows and data size you have mentioned.

Please verify these steps and come back if any help required...

1500 is a very small number in terms of SQL :)

Upvotes: 1

brian
brian

Reputation: 3695

This insert is slow because your adding 1 row at a time and it's fully logged. The bottleneck is not writing the data, it's writing what you're doing in the log.

Get rid of the row by row insert and insert the set with a table lock:

INSERT TABLENAME WITH(TABLOCK) (Column1,Column2,...)
 SELECT 'Column1Row1Value','Column2Row1Value'...
 UNION ALL
 SELECT 'Column1Row2value','Column2Row2Value'...
 UNION ALL
 SELECT 'Column1Row3value','Column2Row3Value'...

Also, look into the rules of minimally logged transactions.

Upvotes: 3

Chris Townsend
Chris Townsend

Reputation: 3162

Rather than doing individual insert statements you'll most likely want to do a union like this:

INSERT INTO Customer 
SELECT 'FirstCustomerName', ...
UNION
SELECT '2ndCustomerName', ...
UNION
SELECT '3rdCustomerName', ...
...1500 more rows...

This should be much faster.

Upvotes: 1

Derrick
Derrick

Reputation: 2021

There's an alternate insert format that you could try. However, I'm not sure if it actually performs better.

INSERT INTO table_name
VALUES
 (100, 'Name 1', 'Value 1', 'Other 1'),
 (101, 'Name 2', 'Value 2', 'Other 2'),
 (102, 'Name 3', 'Value 3', 'Other 3'),
 (103, 'Name 4', 'Value 4', 'Other 4');

reference: http://www.electrictoolbox.com/mysql-insert-multiple-records/

Upvotes: 0

Simon
Simon

Reputation: 6152

Have a look at Performance Monitor in SQL Management Studio while you run the insert. This might point you in the right direction.

130 columns is pretty wide (depending on the individual field types and sizes). It might be an overhead associated with SQL Server having to manage and reallocate pages as you insert (page splitting). Performance Monitor (or run a trace) will help identify if this is the case.

Upvotes: 0

matzino
matzino

Reputation: 3564

Do you have some Indexes on the table? They slow down the insert statements because the index-table would be refreshed after every insert statement.

Upvotes: 0

Related Questions