Accelerate Sql Express?

Hi,

we're planning to migrate an existing dataset cache mechanism to Sql Server Express. The dataset isn't really able to handle and search one million recordsets any more :-)

Once a day the Cache Sql Database wil be filled with fresh data. In comparison with the dataset takes up to fourty times longer to build the cache on the same machine!! Ok, I expected that i will take longer than creating the objects in a database with all its "overhead" like atomic transactions etc. But not so much.

The current throughput on SQL Server ist nearly 400 inserted records per second in the same table. The dataset stores up to 17.000 of the same data.

I'm wondering about the the following fact. Allthough I'm testing the Mass-Inserts with generating synthetic records in a unlimited for-loop, the server CPU load is allways less than 3 %. For all I care it could be much higher while the nightly process of initilializing the cache. Could there be a unnecessary throttle that limits the amount of inserts per second?

Any ideas how to accelerate the database engine or optimizing bulk-inserts?

P.S. I tested some different options and played around with connection string options like packekt size, Named Pipes connections, recycling the last commando and parameters and so on. Without any significant effects..

Marcus

Could you post how you are doing the inserts are the moment? Are you using BULK INSERT or bcp, or are you doing regular inserts? Also, what is the transaction size you are using, and what are the specifications or your machine, disks, etc.|||Im inserting each record in a single regular insert statement via ADO.NET (2.0) ExecuteComand. As far as I know, buldinsert are only for files available, right?

The Maschine is a 4 x XEON 3,6 Ghz, 32 bit Win2003 Server Sp1, 4GB Ram and a fast SCSI Raid

What is the transaction size? Is this a configurable value? At the moment all values are unchanged
installation defaults.

Thanks
Marcus|||There are several ways to speedup:

1) Use a SqlTransaction in your ADO.NET code, and issue a commit after every N rows that you insert. If you don't do this, a commit will be done for each row, which is very expensive.

2) To get big perf improvements, you should use BulkInsert or BCP to bulk load your data in your database. These methods are much faster than doing individual inserts.

Thanks,