Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a request like this one

SELECT 
[EstimateId], 
[CreationUserId], 
[EstimateStatusValueId], 
[LanguageId], 
[LocationId], 
[EstimatorUserId], 
[FilterUnitSystemTypeId], 
[EstimateNumber], 
[RevisionNumber], 
[CreationDate], 
[ModificationDate], 
[ProjectDescription], 
[IsBsdq], 
[ClosingDate], 
[ClosingTime], 
[ClosingUpdatedOn], 
[DeadLineDate], 
[IsReceived], 
[Inclusion], 
[Exclusion], 
[Misc], 
[Note], 
[WorkDeadLines], 
[Comments], 
[Validity], 
[PlansLocation], 
[PlansReceivedFrom], 
[Price]
FROM [Estimate].[Estimates] 
ORDER BY [ClosingDate] ASC, [ClosingTime] ASC

When i run this query in SSMS i get a executing time of 953ms, but when i run this query from a Linq Query in my C# i get a executing time of 1813ms.

The Linq Query use the ".Net SqlClient Data Provider" and is issued against EntityFramework (EDMX file). Is this can be an issue ?

Does anybody knows why i have a big difference between execution times of thoses requests that are the same but excute from different context against the same database ?

I verified all execution plans of both request and they use the same index to satisfy their respective query.

To see the execution plan of the C# request i use the SQL profiler to trap the Show Plan XML event and i compare it to the one of SSMS and both are the same.

share|improve this question
    
just a small question - why are you selecting all table's data without any search condition? Do you really need all the data in the application without any filtering? –  Marian Mar 18 '11 at 21:51
    
Yes this is a feature that i need but this feature will not be use often. I know that is not optimal to issue a big query without where clause. –  Nico Mar 18 '11 at 22:01
    
Anyway my concern is not the request itself but the difference between executing times. I show you this query but all queries give similar results. Why ? –  Nico Mar 18 '11 at 22:25
add comment

3 Answers

Is this consistent, time after time?

I see a CPU difference which could be compile time. Are there any LINQ settings that affect this?

Edit:

  • Capture the plans in Profiler
  • Are you sure the SQL is the same in Profiler?
share|improve this answer
    
Yes it's consistent time after time. I don't know for linq settings. but i found this link codeproject.com/KB/cs/linqsql2.aspx –  Nico Mar 18 '11 at 21:18
    
You can see the plan in picture above for both query. Yes i am sure the SQL is the same in profiler. SQL, Profiler, SSMS and C# app are all hosted on my computer for development purpose. –  Nico Mar 18 '11 at 22:03
    
Capture the actual plan in XML from Profiler. Not from cache. You have different responses but you show a different plan = wrong plan shown above maybe –  gbn Mar 19 '11 at 6:38
add comment
up vote 4 down vote accepted

I think that the problem is in the EDMX file use to generate queries from the C# app.

I found those links that explain the case.

Code Project

Stackoverflow-1

Stackoverflow-2

share|improve this answer
add comment

You will want to look at the Execution plans for the two queries and see where they are different.

share|improve this answer
    
i just edit my post... and i already verify that both query use same plan. –  Nico Mar 18 '11 at 19:29
1  
I just add the event you told me into profiler and it's the same as my last request that i post in my question. I got the same plans.. any other idea ... –  Nico Mar 18 '11 at 19:59
2  
Everything looks correct. The only thing which might explain it would be if the .NET application doesn't receive the data quickly enough. The time reported in SQL Profiler includes the amount of time to transfer the data from the server to the client. So if the client doesn't download everything quickly enough the run time reported will be longer. –  mrdenny Mar 18 '11 at 20:24
2  
Then is comes down to what the application is doing with the data, and how it is reading the data from the database. –  mrdenny Mar 18 '11 at 21:07
3  
In support to mrdenny's answer I'd add that I tested a query in 3 different SQL clients and their reported times were all different although the IO statistics and execution the plans were identical. It was all caused by the internal way of how the clients treated the data. I believe that you can get different time results by outputting to a file, to the grid in Management Studio or to the text output. Anyway, from what I remember, the documentation said that SQL will always be faster than LINQ to SQL, so this is not a surprise :-). –  Marian Mar 18 '11 at 22:10
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.