LINQ to SQL - Seriously SLOW?!?!

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!

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?

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..

^^^ 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...

---

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.

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

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

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.

 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....

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.

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.

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.

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...

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.

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.

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");

}

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");

}

}