SQL Insert Performance

Hello,
in a c# application I have a Datatable (with 35 columns, some varchar(>30), some DateTime and one Decimal) with 1565 rows (all rows are with state added, cause they are all news, created reading a text file containing data in NOT well formatted text - so bulk insert is impossible -). When I want to update data to SQL server express, I call DataSet.Update(DataTable). The insertion of 1565 rows takes about 40 seconds (on my 2400 QuadCore).
I cannot understand if performance are good. Does it take too long or is normal this way?  How can I improve performance? Thanx in advance
puckkkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alpha AuCommented:
If all you need is to insert data, how about building a dynamic sql with all the insert command and run in one go?

sth like

sqlstr &= "insert into thistable values (val1, val2,val3);";
sqlstr &= "insert into thistable values (val4, val5,val6);";


        Dim cmd As New SqlClient.SqlCommand( sqlstr , conn )
        Call cmd.ExecuteNonQuery()
0
puckkkAuthor Commented:
And mabe is it faster?
0
puckkkAuthor Commented:
I mean, what isaverage times for inserting rows in SQL server? If it takes 40 seconds for 1565 rows it means that sql insert about 39 rows per second (with the DataSet.Update() command). With a TSQL command it will be faster or not?
0
Alpha AuCommented:
In fact, The easiest way is to test it. As some factor will affect the speed, including number of index (cluster / non cluster) of the table.
PK/FK, etc
but, as i know, dataset.update will process the data as a array, which will be slower.


0
pnevludCommented:
Yes, AlphaAU has a good suggestion.  You need to determine how SQL Server will process your command.  Take the INSERT statement that you build in your c# application and paste it into SQL Query Analyzer.  Then go to Query -> Display Estimated Execution Path (or CTRL+L).  This will give you a graphical output of what SQL Server will do for the insert statement.  Hover your mouse over any of the operations to see more details, the most important being Physical Operation and Estimated Cost.  Physical Operation can be Table Scan (most in efficient) then Index scan, index seek, and clustered index seek (fastest).  Focus on the steps that have the highest cost (in terms of percentage).  You can create an Index that will make the operation more efficient.  

Also, if you are planning to do lots of inserting then you may want to set the "Fill factor" for your index to a low number like 50% or 60%.  This will leave physical space in the database for you to insert new rows.  Doing that allows for more rows to be inserted into the database before data needs to be physically moved around to make room for more.  On the other hand, if you do way more querying than inserting, you are better to use a high fill factor like 90%.

Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.