vj#: I'm writing a program that needs to use a database and I'm inserting a lot of
information into a database all at once. My program creates about 7,000
INSERT commands that need to be preformed all at once. It's taking about 2
and a half hours to complete all of these INSERT commands. Is this normal?
Is there any way to do it faster?

(Note - it's not my programming that's causing it to take so long. The
program creates all these commands, then executes them. Creating them takes
less than one second - executing them is taking hours.)

I need to run this program about 300 times to get everything entered into

Here's an update:

I ran the program. This time it crated 22,833 "INSERT INTO" SQL Commands.
It took just over 6 hours 55 minutes to execute the program for an average of

Interesting problem.

In MS SQL Server we have the T-SQL "BULK INSERT" command that can insert the
content of a file into a table.

BULK INSERT [MyStufff].[dbo].[MyTable]
FROM 'F:\Informatikk\myFile.tbl'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

File:
1 ,hello ,hello 2
2 ,go ,go 2
3 ,cool ,col 2


You can also experiment with switching off indexes (please note it could
talk longer time to index them when you switch the index on, after the bulk
insert without indexing. Please do a performance test on this first.), set
the recovery model to bulk-logging, You should also talke a look "table
locking" in combination with bulk inserts.




IBM and MySQL supports compound statements.
Eg.
BEGIN
INSERT INTO tab1 VALUES ( 'a1', 'b', 'c', 'd');
INSERT INTO tab1 VALUES ( 'a2', 'b', 'c', 'd');
INSERT INTO tab1 VALUES ( 'a3', 'b', 'c', 'd');
INSERT INTO tab1 VALUES ( 'a'4, 'b', 'c', 'd');
END

MS SQL Server does not support this feature.


If the data is already in the database:
INSERT INTO tab1 SELECT * FROM Tab2 WHERE compensation < '$57.000'


It does help slightly to have the primary key first in the talbe column.

Please update the indexes/statistics when your done so the optimizer knows
about your new stuff in the DB.



Best Regards,
Lars-Inge Tønnessen