CODEWITHSUNDEEP
sql-server
MITHIYA MOIZ
MITHIYA MOIZ

Reputation: 29

SQL Server 2008 Insert performance issue

is there any way to increase performance of SQL server inserts, as you can see below i have used below sql 2005, 2008 and oracle. i am moving data from ORACLe to SQL.

while inserting data to SQL i am using a procedure.

insert to Oracles is very fast in compare to SQL, is there any way increase performance. or a better way to move data from Oracle to SQL (data size approx 100000 records an hour)

please find below stats as i gathered, RUN1 and RUN2 time is in millisecond.

http://tinypic.com/view.php?pic=23v19px&s=5

Upvotes: 0

Views: 1348

Answers (4)

Andrew Florko
Andrew Florko

Reputation: 7750

If it's migration (that is done once), I suppose not to write code yourself but try out spesialized utilities.

Take a look at Sql Data Examiner. We use it to synchronize developers and production database structure changes as well as changes in data. Data Examiner both supports Oracle and Sql Server

Upvotes: 1

Cowan
Cowan

Reputation: 37533

Hard to say without more detail, but are you inserting the rows one at a time via the procedure? If so, the overhead of transactions, locking, connection setup + teardown, etc can provide a certain amount flat-rate penalty to each individual INSERT. If possible, batch your inserts to insert multiple rows at once.

For example, if you end up doing something like (each row is one execution):

INSERT INTO Animals (ID, Name) VALUES (1, 'Dog')
INSERT INTO Animals (ID, Name) VALUES (2, 'Cat')
INSERT INTO Animals (ID, Name) VALUES (3, 'Monkey')
...
INSERT INTO Animals (ID, Name) VALUES (9999, 'Lemur')

You an often improve performance by executing a single batch, using techniques like (say):

INSERT INTO Animals (ID, Name)
SELECT 1 AS ID, 'Dog' AS Name
UNION ALL SELECT 2, 'Cat'
UNION ALL SELECT 3, 'Monkey'
...
UNION ALL SELECT 9999, 'Lemur'

Upvotes: 0

kasperjj
kasperjj

Reputation: 3664

Make sure you are doing batch inserts within a single transaction instead of single inserts each in their own transaction.

If you post the actual stored procedure you have written, it would be easier to comment on, but without knowing anything else about your code and data those numbers seems very very slow.

You might also want to have a look at actual bulk insert methods in sql server such as the "bulk insert" command or sql server integration services.

Upvotes: 0

Preet Sangha
Preet Sangha

Reputation: 65466

May be you can use Bulk Copy to ram data into the database.

Seriously though the performance is dependant on many things including but not limited to disk performance, CPUs, triggers, indexes etc. You need to profile what the insert is doing to see how to improve it.

SQL Server has a profiler utility that could help you. Once you find a bottleneck ask here on how to help with that. Otherwise your question is very very general.

Upvotes: 1

Related Questions