LINQ performance vs. ADO.NET

Question

LINQ performance vs. ADO.NET

rosedd

Mr or Mrs. 500

Points: 570
More actions
December 16, 2008 at 2:14 pm

#195222

I have simple code that is going to be hit perhaps one million times or more. I tried writing it with LINQ, but the performance was very poor, 40 or more times slower than ADO.NET.
There are times when LINQ will not be the best technology for the job, but…
I am wondering if the huge difference in execution times seems reasonable, or am I missing something.
Is there anything that I might be able to do to speed up my LINQ code?
Code using LINQ…
using (FleetDataContext db = new FleetDataContext(fleetConnection))
{
db.ObjectTrackingEnabled = false;
try
{
engineID = (from eng in db.ENGINEs
where eng.ENG_CODE == engineCode
select eng).First().ENG_ID;
}
catch
{
throw;
}
}
Code using ADO.NET…
string sql = String.Format(
"select ENG_ID from ENGINE where ENG_CODE = '{0}';",
engineCode);
try
{
using (SqlConnection cnn = new SqlConnection(fleetConnection))
{
using (SqlCommand cmd = new SqlCommand(sql, cnn))
{
cnn.Open();
engineID = (int)cmd.ExecuteScalar();
}
}
}
catch
{
throw;
}
Thanks for any help.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

My first guess would be that you are storing your data as varchar() columns, and LINQ only creates nvarchar() parameters, so you lose a whole lot of performance in the implicit conversion up to nvarchar(). Not only that, but a table scan results because no index exists on the data as a nvarchar type. Use Profiler and catch the statement actually being sent by the LINQ code to the database engine, and then do the same with ADO.NET. You will see the difference almost immediately. Then run the two code sets with the Actual Execution Plan output turned on and compare.

rosedd Mr or Mrs. 500 Points: 570 More actions · Answer 2

Jonathan,

Yes, we are using varchars. It makes a lot of sense that the cast is expensive. I expected to take a hit with LINQ, but not as big as I have seen.

I am not familiar with Profiler. Right now I am using the Express version of SQL Server. Some of our clients are using it, so I need to make sure that things will work for them. My suspicion is that Profiler does not come with the Express version.

I do have the Developer (I think that is what it is called, comes with VS Professional) version which is less limited. We also have the real thing on our servers, but I am not sure how difficult the permission issue is. Do you know if the Developer would have Profiler, or where I could get it?

Thanks.

David

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Developer Edition of SQL would have Profiler as a part of the Workstation Components. For Express only though, you can download the following free Profiler for SQL Express that is not by Microsoft, but works:

http://sqlprofiler.googlepages.com/

The implicit conversion is expensive because you are going to see table/index scans and no use of index seeks to satisfy the query. Another problem you might have considered is that LINQ will parameterize based on the size of the string being passed to it, so the parameter one time might be a nvarchar(10) and the next time nvarchar(12) and then the next time nvarchar(8) which affects plan reuse and plan caching, so you see more compiles happening and experience a bloated procedure cache whereas using a stored procedure with a table adapter would use the same plan from cache almost every time.

BTW:

Your ADO.NET code example is a good target for SQL Injection, and will also have a worse plan caching problem than LINQ will. You need to use parameters and bind values to parameters using the SqlParameter object and SqlParameter[] collection of the SqlCommand.

florim SSC Veteran Points: 293 More actions · Answer 4

Try use Compile queryis in Linq you will get beter performance there, because you callin that code more than one time...

Code using LINQ…

public partial class FleetDataContext

{

public static Func

EnginesByEngineCode = CompiledQuery.Compile

((FleetDataContext context, int engineCode) =>

context.ENGINEs.Where(p => p.ENG_CODE == engineCode));

public IQueryable GetEnginesFast (int engineCode)

{

return EnginesByEngineCode(this, engineCode);

}

somthing like this i hope it helps...

Sorry for my bad english...

rosedd Mr or Mrs. 500 Points: 570 More actions · Answer 5

Thanks to both of you.

I do normally use parameterized queries, but was lazy in this example. However, I did not realize that not using them would affect the plan caching. Will using parameters take care of the caching problem. How does that work?

I also was not aware that you could use compiled queries in LINQ, or how to do that.

This was very helpful.

David

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Yes, Parameters allow for plan reuse by the plan cache.

rosedd Mr or Mrs. 500 Points: 570 More actions · Answer 7

rosedd

Mr or Mrs. 500

Points: 570

February 4, 2009 at 7:48 am

#937970

Jonathan,

Thanks again.

David