[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL Insert Performance

Posted on 2008-02-04
5
Medium Priority
?
1,125 Views
Last Modified: 2008-02-07
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
0
Comment
Question by:puckkk
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:Alpha Au
ID: 20812998
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
 

Author Comment

by:puckkk
ID: 20813102
And mabe is it faster?
0
 

Author Comment

by:puckkk
ID: 20813150
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
 
LVL 7

Assisted Solution

by:Alpha Au
Alpha Au earned 800 total points
ID: 20814046
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
 
LVL 1

Accepted Solution

by:
pnevlud earned 1200 total points
ID: 20825252
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question