Tech Off Thread

22 posts

LINQ to SQL - Seriously SLOW?!?!

Back to Forum: Tech Off
  • User profile image
    jh71283

    Hey everyone.

    I decoded to write a little test app to compare the speed of DataSet / Table adapter to 'LINQ to SQL'.

    This test focused solely on inserts

    The app loops 10,000 times, inserting a row into a datatable (or in LTS, it adds the row to the table equivalent in the DataContext)

    Results
    =====


    It took the code 293 ms to load the rows into the DataTable, and then 16030ms to update the database using the tableadapter.

    It took 474ms to load the rows into the datacontext, and then a whopping 29550ms to complete the .SubmitChanges()


    How can this be, I was expecting LTS to win hands down,but for now it looks like I'll be sticking to my datasets!

  • User profile image
    brunomoniz

    That's a litle bit strange. Did you do just one test or more?
    Sometimes, the first time you access a database it takes longer...

    Could you post the code?

  • User profile image
    stevo_

    I don't expect LINQ to be faster since its doing runtime statement evaluation and mapping..

    But posting some of your code might help us see if your 'tests' are fair..

  • User profile image
    jh71283

    ^^^ There ya go guys.

    The database was 'warm' i.e. it had not been left for very long, and also I did try a few times to verify my findings...

  • User profile image
    jh71283

    Module Module1
        
    Dim DS As New DataSet1
        
    Dim dc As New DataClasses1DataContext

        
    Sub Main()
            
    Dim stp As New Stopwatch
            
    Dim ta As New DataSet1TableAdapters.tblTransactionItemsTableAdapter
            stp.Reset()
            stp.Start()
            
    For To 10000
                
    Dim dr As DataSet1.tblTransactionItemsRow DS.tblTransactionItems.NewtblTransactionItemsRow
                dr.TransactionID 
    -1
                
    dr.ProductID -1
                
    dr.Quantity 1
                
    dr.MeasureID 1
                
    DS.tblTransactionItems.Rows.Add(dr)
            
    Next
            
    Console.WriteLine(stp.ElapsedMilliseconds)
            stp.Reset()
            stp.Start()
            ta.Update(DS)
            Console.WriteLine(stp.ElapsedMilliseconds)
            Console.ReadLine()
        
    End Sub
        Sub 
    Main_2()
            
    Dim stp As New Stopwatch
            stp.Reset()
            stp.Start()
            
    For To 10000
                
    Dim dr As New tblTransactionItem
                dr.TransactionID 
    -1
                
    dr.ProductID -1
                
    dr.Quantity 1
                
    dr.MeasureID 1
                dc.InsertOnSubmit(dr)
            
    Next
            
    Console.WriteLine(stp.ElapsedMilliseconds)
            stp.Reset()
            stp.Start()
            dc.SubmitChanges()
            Console.WriteLine(stp.ElapsedMilliseconds)
            Console.ReadLine()
        
    End Sub
    End Module

  • User profile image
    figuerres

    jh71283 wrote:
    Hey everyone.

    I decoded to write a little test app to compare the speed of DataSet / Table adapter to 'LINQ to SQL'.

    This test focused solely on inserts

    The app loops 10,000 times, inserting a row into a datatable (or in LTS, it adds the row to the table equivalent in the DataContext)

    Results
    =====


    It took the code 293 ms to load the rows into the DataTable, and then 16030ms to update the database using the tableadapter.

    It took 474ms to load the rows into the datacontext, and then a whopping 29550ms to complete the .SubmitChanges()


    How can this be, I was expecting LTS to win hands down,but for now it looks like I'll be sticking to my datasets!


    use the right tool for the right job. 

    if you want fast bulk inserts do them in TSQL w/o any .net code.
    or use BCP
    or.... whatever.

    LINQ should be "good" perf wise but it's goals are (I think) to enable developers to write better .net code.

    most of the time most applications do not insert 10,000 rows at a time....


    there are things I will do with datasets.

    there are things I will do with DLINQ

    still other things I will do with commands and connections and data readers.

    and Stored Procedures

    no one tool does all things perfectly.

  • User profile image
    figuerres

    PS: in your sample try this:

    create a linq collection of rows to insert and call insert on submit one time outside of your loop.

    then see how that times out...

    i bet that will be faster, I bet each insert on submit creats some kind of transaction like behavior or evaluates each row as you add it.

    kind of like string Vs. String Builtder

  • User profile image
    jh71283

    Hmm. Good theory figuerres, but unfortunately yields the same results - although it turned out that the first figure ( adding the rows to the collection) was much faster  (183ms) if I add them to a generic list, and then into the datacontext using InsertAllOnSubmit

  • User profile image
    vesuvius

    Spend some time here, there may me something of use; http://blogs.msdn.com/ricom/archive/2008/01/11/performance-quiz-13-linq-to-sql-compiled-queries-cost.aspx

    I really do like the fact that people go out of their way to test performance. Very well done to jh71283.

  • User profile image
    jh71283

     haha thanks vesuvius.

    When figuerres mentioned above that I should use the right tool for the job, I do realise that, but was curious.

    In theory, however, I would have expected Linq to have structured the insert statements either the same way, or better than TableAdapter does.

    At this level, it actually has nothing to do with linq itself, this is lower level than that.

    Is there any way I can look at the SQL statements that are generated in both of these instances?

    In a perfect world, the SQL from these two loops would be identical....

  • User profile image
    Unhandled​Exception

    You can take a look at the SQL issued using SQL Profiler, which is part of SQL Server. I used it to view the SQL statement created by LINQ for joins.

  • User profile image
    figuerres

    UnhandledException wrote:
    You can take a look at the SQL issued using SQL Profiler, which is part of SQL Server. I used it to view the SQL statement created by LINQ for joins.



    http://weblogs.asp.net/scottgu/archive/2007/06/29/linq-to-sql-part-3-querying-our-database.aspx

    read that and there is a VS addin that shows the SQL for an Expression.

    that plus the above "use the profiler"

    from what I have seen so far the sql LINQ generates is often very different from what I might do "by hand".

    Oh, and "DOH" kick me for not seeing this before:

    modify your table insert sample ... 
    when  DLINQ does the "submit" it creates a transaction so that any exception will rollback the updates and inserts.

    does method #1 create a transaction behind the curtain ??
    if not then that may account for most if not all of the difference.

    PS: a quick read says "No" tableadapter is just doing a batch insert w/o a sql transaction, you can add one but you habve to do a few things to make it work right.

  • User profile image
    Stebet

    According to Rico Mariani the insert/update cases should be a lot faster with LINQ, especially during bulk inserts/updates. I'd say something has to be wrong with the code or the database configuration?

    P.S.: Here's Ricos blog about this.

  • User profile image
    figuerres

    just so happens I need to do an insert thing so I think I will check out some numbers and see what I get.

     

    results to follow...

  • User profile image
    figuerres

    figuerres wrote:
    

    just so happens I need to do an insert thing so I think I will check out some numbers and see what I get.

     

    results to follow...



    DLINQ
    rows 1000 4000 10000 50000 50000
    time 2751 5297 5290 54364 66735
    time per row 2.751 1.32425 0.529 1.08728 1.3347


    time is in milliseconds from the stopwatch.

    this is just the time to call this code

    StW.Reset();
    StW.Start();
    db.insertTests.InsertAllOnSubmit(recSet);
    db.SubmitChanges();
    StW.Stop();
    textBox2.AppendText(
    "time for InsertAll and SubmitChanges " + StW.ElapsedMilliseconds.ToString() + "\n");

    I have not done my SQL version yet. next I will see how it runs to compare with this.
    Note the time went down and then up?
    looks like some overhead is seen by the difference between 1000 rows to 10,000 rows, time is almost the same for 4,000 and 10,000 rows!
    but the time-per-row drop is huge!

    also in my code I am doing a
    using(){}  block and a for loop
    I'll post a sample of the full code later.

  • User profile image
    figuerres

    figuerres wrote:
    
    figuerres wrote:
    

    just so happens I need to do an insert thing so I think I will check out some numbers and see what I get.

     

    results to follow...



    DLINQ
    rows 1000 4000 10000 50000 50000
    time 2751 5297 5290 54364 66735
    time per row 2.751 1.32425 0.529 1.08728 1.3347


    time is in milliseconds from the stopwatch.

    this is just the time to call this code

    StW.Reset();
    StW.Start();
    db.insertTests.InsertAllOnSubmit(recSet);
    db.SubmitChanges();
    StW.Stop();
    textBox2.AppendText(
    "time for InsertAll and SubmitChanges " + StW.ElapsedMilliseconds.ToString() + "\n");

    I have not done my SQL version yet. next I will see how it runs to compare with this.
    Note the time went down and then up?
    looks like some overhead is seen by the difference between 1000 rows to 10,000 rows, time is almost the same for 4,000 and 10,000 rows!
    but the time-per-row drop is huge!

    also in my code I am doing a
    using(){}  block and a for loop
    I'll post a sample of the full code later.



    PS: my times for building my List<RecType>
    are also way less.... the OP had a huge number next to mine.
    also this is a core2duo 1.8Ghz Sony laptop
    3 gigs of ram
    sql express on laptop

    my time to make a list of data was in the 5-20 for the small sets, for 50000 it was about 200ms.

  • User profile image
    figuerres

    Code SQL insert:





    private
    void button1_Click(object sender, EventArgs e) {

    UInt64 startValue = Convert.ToUInt64(textBox1.Text);

    UInt64 num = Convert.ToUInt64(numericUpDown1.Value);

    textBox2.AppendText("test: add " + num.ToString() + " rows\n");

    using (SqlConnection Con = new SqlConnection(AddCards.Properties.Settings.Default.HMS_DB1ConnectionString)) {

    Con.Open();

    SqlTransaction Tran =Con.BeginTransaction();

    using (SqlCommand Cmd = new SqlCommand("INSERT INTO [insertTest]([FranchiseID],[Name],[Address1],[Address2],[City],[State],[PostalCode],[Phone],[Fax],[Country],[CreationTimeStamp],[WhoChanged],[LastChanged],[Active],[CardGUID],[AccountNumber],[Balance]) VALUES (@FranchiseID,@Name,@Address1,@Address2,@City,@State,@PostalCode,@Phone,@Fax,@Country,@CreationTimeStamp,@WhoChanged,@LastChanged,@Active,@CardGUID,@AccountNumber,@Balance)",Con,Tran)) {

    SqlParameter FranchiseID = new SqlParameter("@FranchiseID", SqlDbType.Int);

    SqlParameter Name = new SqlParameter("@Name", SqlDbType.NVarChar,-1);

    SqlParameter Address1 = new SqlParameter("@Address1", SqlDbType.NVarChar,-1);

    SqlParameter Address2 = new SqlParameter("@Address2", SqlDbType.NVarChar,-1);

    SqlParameter City = new SqlParameter("@City", SqlDbType.NVarChar,-1);

    SqlParameter State = new SqlParameter("@State", SqlDbType.NVarChar,-1);

    SqlParameter PostalCode = new SqlParameter("@PostalCode", SqlDbType.NVarChar,20);

    SqlParameter Phone = new SqlParameter("@Phone", SqlDbType.NVarChar,-1);

    SqlParameter Fax = new SqlParameter("@Fax", SqlDbType.NVarChar,-1);

    SqlParameter Country = new SqlParameter("@Country", SqlDbType.NVarChar,-1);

    SqlParameter CreationTimeStamp = new SqlParameter("@CreationTimeStamp", SqlDbType.DateTime);

    SqlParameter WhoChanged = new SqlParameter("@WhoChanged", SqlDbType.Int );

    SqlParameter LastChanged = new SqlParameter("@LastChanged", SqlDbType.DateTime);

    SqlParameter Active = new SqlParameter("@Active", SqlDbType.Bit);

    SqlParameter CardGUID = new SqlParameter("@CardGUID", SqlDbType.UniqueIdentifier);

    SqlParameter AccountNumber = new SqlParameter("@AccountNumber", SqlDbType.NVarChar,16);

    SqlParameter Balance = new SqlParameter("@Balance", SqlDbType.Money );

    Cmd.Parameters.Add(FranchiseID);

    Cmd.Parameters.Add(Name);

    Cmd.Parameters.Add(Address1);

    Cmd.Parameters.Add(Address2);

    Cmd.Parameters.Add(City);

    Cmd.Parameters.Add(State);

    Cmd.Parameters.Add(PostalCode);

    Cmd.Parameters.Add(Phone);

    Cmd.Parameters.Add(Fax);

    Cmd.Parameters.Add(Country);

    Cmd.Parameters.Add(CreationTimeStamp);

    Cmd.Parameters.Add(WhoChanged);

    Cmd.Parameters.Add(LastChanged);

    Cmd.Parameters.Add(Active);

    Cmd.Parameters.Add(CardGUID);

    Cmd.Parameters.Add(AccountNumber);

    Cmd.Parameters.Add(Balance);

    StW.Reset();

    StW.Start();

    for (UInt64 i = startValue; i < (UInt64)numericUpDown1.Value; i++) {

    FranchiseID.Value = 1;

    Name.Value = "";

    Address1.Value = "";

    Address2.Value = "";

    City.Value = "";

    State.Value = "";

    PostalCode.Value = "";

    Phone.Value = "";

    Fax.Value = "";

    Country.Value = "";

    CreationTimeStamp.Value = DateTime.Now;

    WhoChanged.Value = 7;

    LastChanged.Value = DateTime.Now;

    Active.Value = 1;

    CardGUID.Value = Guid.NewGuid();

    AccountNumber.Value = i.ToString("0000000000000000");

    Balance.Value = 0.0M;

    Cmd.ExecuteNonQuery();

    }

    }

    StW.Stop();

    textBox2.AppendText("time for create and insert records." + StW.ElapsedMilliseconds.ToString() + "\n");

    StW.Reset();

    StW.Start();

    Tran.Commit();

    }

    StW.Stop();

    textBox2.AppendText("Transaction Commited " + StW.ElapsedMilliseconds.ToString() + "\n");

    }


  • User profile image
    figuerres

    Code DLINQ Insert:

    private void button2_Click(object sender, EventArgs e) {

    UInt64 startValue = Convert.ToUInt64(textBox1.Text);

    UInt64 num = Convert.ToUInt64(numericUpDown1.Value);

    insertTest Rec;

    List<insertTest> recSet = new List<insertTest>();

    textBox2.AppendText("test: add " + num.ToString() + " rows\n");

    using (DataClasses1DataContext db = new DataClasses1DataContext()) {

    StW.Reset();

    StW.Start();

    for (UInt64 i = startValue; i < (UInt64)numericUpDown1.Value; i++) {

    Rec = new insertTest();

    Rec.AccountNumber = i.ToString("0000000000000000");

    Rec.CreationTimeStamp = DateTime.Now;

    Rec.Active = true;

    Rec.Address1 = "";

    Rec.Address2 = "";

    Rec.Balance = 0.0M;

    Rec.CardGUID = Guid.NewGuid();

    Rec.City = "";

    Rec.Country = "";

    Rec.Fax = "";

    Rec.FranchiseID = 1;

    Rec.LastChanged = DateTime.Now;

    Rec.Name = "";

    Rec.Phone = "";

    Rec.PostalCode = "";

    Rec.State = "";

    Rec.WhoChanged = 7;

    recSet.Add(Rec);

    }

    StW.Stop();

    textBox2.AppendText("time for create records list." + StW.ElapsedMilliseconds.ToString() + "\n");

    StW.Reset();

    StW.Start();

    db.insertTests.InsertAllOnSubmit(recSet);

    db.SubmitChanges();

    StW.Stop();

    textBox2.AppendText("time for InsertAll and SubmitChanges " + StW.ElapsedMilliseconds.ToString() + "\n");

    }

    }

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.