Best method to insert rows into DB

[JayPak]

We currnetly have a stored procedure that inserts several hundred thousand rows(close to 1 million) and it takes a while to run. We have pretty much optimzed the stored procedure and can not do anything else to it to help performace. My question is what if the data was put into a dataset with a data adapter, worked on disconnected and them using the adapter, updated or inserted back into the database? Would this be faster by chance? And I supposed the new rows or information created would depend on the client rather than the server, but I am womdering if it would still be faster.

Any other suggestions would be helpful also. Incidentily, our server is a 4 processor 2.4 gig with 2 GB of memory - so it is pretty descent.

Thanks!

[nswan]

no i think it would be slower. If you are updating using a data adapter it build a massive long Where clause that it uses to check to see if it needs to add/update data.

How many indexes have you got on your table? Indexes on tables increases the amount of time it takes to add new rows to the database.

Please anybody let me know if i am wrong on these 2 points

nick

[BlackDeath78]

nswan, you are right. The data adapter creates an offline copy of the data so the system first has to transport the date to the clients' memory. After your modifications all the modified records have to be transported back to the server. So the network is the first bottleneck in the system. The second is the client itself, as it has in most cases a rather small RAM and some of the data has to be swapped.

The best way to modify data are Stored Procedures on the SQL Server as they run directly on the server. The only data your app has to send via the network are the parameters for the procedures.

Indexes increase the time the server needs to modify data because the index itself has to be updated as well. The best strategy can be a clustered index on the columns that are queried with the where clause.