12

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 those requests that are the same but execute 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.

Improve this question
3
  • 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
    Commented Mar 18, 2011 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
    Commented Mar 18, 2011 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
    Commented Mar 18, 2011 at 22:25

3 Answers 3

Reset to default
6

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?
Improve this answer
3
  • 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
    Commented Mar 18, 2011 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
    Commented Mar 18, 2011 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
    Commented Mar 19, 2011 at 6:38
4

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

Improve this answer
3

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

Improve this answer
8
  • i just edit my post... and i already verify that both query use same plan.
    – Nico
    Commented Mar 18, 2011 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
    Commented Mar 18, 2011 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
    Commented Mar 18, 2011 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
    Commented Mar 18, 2011 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
    Commented Mar 18, 2011 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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