Slow Insert from Dataset | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Slow Insert from Dataset

We have sql server 2005 x64 on a server with win 2003 x64, dual core AMD 64 and 4 gb of memory. We are doing an EDI application. There is one parent table and about 70 related child tables which are being updated from a dot net 2005 dataset. We update the parent table with one record and a PK and then update the rest of the tables one at a time in a loop and then go back and do it again. The total records for the parent table in the data set is limited to 5000. The child tables may have anywhere from zero to 10000 records in the dataset. The insert performance is in the tank. What should we be doing differently? We are kind of new at this and any suggestions will be appreeciated.
The most obvious first thing I would try (as it is one of the easiest things you can do) is to ensure that you have appropriate indexes on the tables. Check the query plan of the INSERT statement to see how it is locating the rows to be updated. You should be seeing an index seek in most case (in smaller tables, an index or table scan is OK). If you don’t know how to do this, consider getting a Profiler trace of typical activity and then running in through the Index Wizard to identify missing indexes. Also, ensure that the indexes are rebuilt often. Also, using stored procedures instead of a dot net dataset will boost performance, but will require changing your application, which is a lot of work. —————————–
Brad M. McGehee, SQL Server MVP
generate the execution plan, save as xml
then open in notepad, paste the xml plan in a reply also
SET STATISTICS TIME ON run your insert script then put the messages output here the reason is that triggers may not show up in the plan, but will with the SET
Are the inserts in a loop and is it that gradually insert performance is slowing down…
Modify script to check if we can get time after every insert so that we will know if it is a gradual process or does not pick at all. Are there any insert triggers that get fired automatically and anyother constraints that may be gettig into insert performance. If inserts are waiting or taking long time what are the waitstats for it are we waiting on some resource that is rendering system slowness. Use PSSDiag to check performance of SQL Server or SQLIOSIM to check disk performance.…6A-28C2-4483-8293-76FFF67B9EB3&displaylang=en Other thing that I would check is, what account under which these insert statements are running. If they are running under using account try the same if they can use SQL Admin account. Hope this helps.