Making Inserts Faster

[manmaria]

I have a huge query which used to insert data into a table. If I run that query, it runs faster but my inserts are running longer around 45 minutes for a 4mill records. I do not have any indexes or cluster or any uniqueness defined on this table. Just a table with bunch of columns.

How do I make my inserts faster?

TIA

 

[SQLDenis]

>>Just a table with bunch of columns.


how wide is the table? My guess is that it is a wide table and you DB has to expand several times during this operation to expand the datafile (I assume you have 10% autogrow)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[manmaria]

Thanks Denis.
Yes, we do have 10 % autogrow. It has around 20 columns.
Though I know which gives me a unique row but we have not set any primary key or cluster at this point. I want my initial load to run faster.

TIA

 

[SQLDenis]

4 million rows should not take more than a couple of minutes at the most. Maybe your network is messed up or something like that.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[manmaria]

What else makes an insert faster? Please pass some ideas.

 

[SQLDenis]

>>What else makes an insert faster? Please pass some ideas.


BCP or BULK INSERT, but you will have to BCP out to a file and then BCP in from a file

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[NoCoolHandle]

It sounds like you are using a standard sql insert statement.

Have you looked into BULK INSERT or BCP?

Also. Index's will slow things down. If you have lots of index's on this table, you need to add 1 row per index for each row in the table. Sometimes it is faster to drop the index's first then import and rebuild the index's.

Are the inserts being checked against a primary key in another table? I.E. does your destination table contain foreign keys or check constraints?

Disabling these might help. You can always renable them later.

Rob

 

[SQLDenis]

When you transfer a lot of data you can sometimes get better performance by increasing the packet size

lookup network packet size Option in BOL

This might not be applicable to your situation, it is just something you might want to take a look at



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Are you sure there are no triggers on the table. Sometimes it is a badly written trigger that slows down the process. Also inserts of large records coming from files are often faster if you put the file on the same server as the SQL server to avoid having to send the file data across network lines during the insert.

Also how are you inserting the records? You aren;t using a a cursors are you? If the data is being inserted from an exisitng data elsewhere in your database, please show us the code you are using to perform the insert.

"NOTHING is more important in a database than integrity." ESquared

 

[k01]

Hi,

This is the steps which I recommend.

-Configure database size to avoid auto expansion
-Create flat file of target data
-Insert flat file with BULK INSERT

Koichi

http://aboutabi.blogspot.com/

(SQLServer & Cognos Tips)