Why is SqlQuery a lot faster than using LINQ expression on views?

Question

I want to query data from a view, which is a view of a table contains 583,000 records. So I write a simple query to query from the view like this

var uuid = "AB1-23456";
dbSet.SingleOrDefault(x => x.UserKey == uuid);

This is the generated sql

SELECT "Extent1"."UserKey" AS "UserKey", 
       CAST("Extent1"."IsDeleted" AS number(3,0)) AS "C1", 
       "Extent1"."FirstName" AS "FirstName", 
       "Extent1"."LastName" AS "LastName", 
       "Extent1"."UserLogin" AS "UserLogin", 
       "Extent1"."AccLocationKey" AS "AccLocationKey", 
       "Extent1"."CompanyKey" AS "CompanyKey"
FROM "UsersView" "Extent1"
WHERE ('AB1-23456' = "Extent1"."UserKey")

I ran the query for 5 times. The first call took me 350ms and next calls took me 150ms on average on this query which was too slow, so I changed the query to be like this

var queryString = 
    "SELECT \"Extent1\".\"UserKey\" AS \"UserKey\", " +
            "CAST( \"Extent1\".\"IsDeleted\" AS number(3,0)) AS \"IsDeleted\", " +
            "\"Extent1\".\"FirstName\" AS \"FirstName\", " +
            "\"Extent1\".\"LastName\" AS \"LastName\", " +
            "\"Extent1\".\"UserLogin\" AS \"UserLogin\", " +
            "\"Extent1\".\"AccLocationKey\" AS \"AccLocationKey\", " +
            "\"Extent1\".\"CompanyKey\" AS \"CompanyKey\" " +
    "FROM \"UsersView\" \"Extent1\" " +
    "WHERE ('AB1-23456' = \"Extent1\".\"UserKey\")";
dbSet.SqlQuery(queryString).SingleOrDefault();

I ran it for 5 times The first call took me 40ms and next calls took me only 1ms on average!

Do anyone has any ideas what I did wrong?

Environment

Entity Framework 5.0
Oracle 11g Database
ODP.NET 11.2 Release 3
.NET Framework 4.5

Is this reproducable? What happens if you send the queries using your new query first? Will the generated query be the fast one? — nvoigt, Jan 09 '15 at 07:54
I highly doubt your "generated sql" is accurately represented here. EF would most likely use parameters, and not embed a string literal in the query like you show. — Eren Ersönmez, Jan 09 '15 at 07:54
@ErenErsönmez I used LinqPad to generate the SQL statement and it showed me that statement. — Moozz, Jan 09 '15 at 08:25
@nvoigt I've tried calling those two queries in different order, the result is still the same. — Moozz, Jan 09 '15 at 08:34
are you getting these time results from LinqPad? Because if you're using LinqPad, I don't think your linq queries will be cached. So it practically doesn't matter how many times you run (aside from query plan caching on the DB side maybe). — Eren Ersönmez, Jan 09 '15 at 08:40
@ErenErsönmez No, I capture the time spent in the program itself. I use LinqPad to only generate the SQL statement. — Moozz, Jan 09 '15 at 08:42
ok, then you might take a look at [compiled queries](http://msdn.microsoft.com/en-us/library/vstudio/bb896297(v=vs.110).aspx) to see if that makes a significant difference. — Eren Ersönmez, Jan 09 '15 at 08:46
@ErenErsönmez Seems like Compiled Query is not available in DbContext. I don't use ObjectContext. https://social.msdn.microsoft.com/Forums/en-US/2b8315b3-eb0c-4115-92e6-b63914ab0cec/how-can-compiled-query-work-with-dbcontext?forum=adodotnetentityframework — Moozz, Jan 09 '15 at 09:06
@Moozz it can be used on DataContext (a wrapper for ObjectContext)... http://msdn.microsoft.com/en-us/library/Bb399335%28v=vs.90%29.aspx .. although this will of course slow down first execution but speed up subsequent executions. — Paul Zahra, Jan 09 '15 at 09:13
@Moozz you can retrieve the ObjectContext from DbContext easily: `var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;` — Eren Ersönmez, Jan 09 '15 at 09:35
@ErenErsönmez EF5 and above automatically caches the query. http://stackoverflow.com/questions/9739925/entity-framework-compiled-query — Moozz, Jan 09 '15 at 10:08
You may be right, I didn't try this myself. However, the note on the MSDN doc is interesting `Starting with the .NET Framework 4.5, LINQ queries are cached automatically. However, you can still use compiled LINQ queries to reduce this cost in later executions and compiled queries can be more efficient than LINQ queries that are automatically cached.` — Eren Ersönmez, Jan 09 '15 at 10:31

score 4 · Answer 1 · edited May 23 '17 at 12:10

4

Isn't it that it takes that 150ms only the first time it ran?. Every consecutive call should take around that 1ms you stated. LinqToSql has to compile the query first to get SQL. Take a look at LinqToSql Precompiling queries benefit?

edited May 23 '17 at 12:10

Community

1
1

answered Jan 09 '15 at 07:56

smiech

725
4
8

No, it is 150ms every time it is run. – Moozz Jan 09 '15 at 08:19
To be more clear, I ran it for many time without restarting the process, The first call took me 350ms and next calls took me 150ms on average. – Moozz Jan 09 '15 at 08:45
I found that EF5 automatically caches the query. http://stackoverflow.com/questions/9739925/entity-framework-compiled-query – Moozz Jan 09 '15 at 10:09

score 1 · Accepted Answer · answered Feb 16 '15 at 08:33

1

This is the best answer to this question.

https://community.oracle.com/message/10481253

answered Feb 16 '15 at 08:33

Moozz

609
6
18

score 0 · Answer 3 · answered Jan 12 '15 at 07:41

0

This problem is not valid anymore.

var uuid = "AB1-23456";
dbSet.SingleOrDefault(x => x.UserKey == uuid);

The time spent is around 150ms. But if I tried

dbSet.SingleOrDefault(x => x.UserKey == "AB1-23456");

The time spent is back to 1ms. I'll ask an another question accordingly.

answered Jan 12 '15 at 07:41

Moozz

609
6
18

Odd... What if you use string uuid = "AB1-23456"; ? – Paul Zahra Jan 12 '15 at 09:03
Did you try query expression? e.g. (from x in dbSet where x.UserKey = uuid select x).SingleOrDefault(); Was there any difference? - Just curious. – Paul Zahra Jan 12 '15 at 12:04
@PaulZahra The result is the same as .SingleOrDefault(x => x.UserKey == uuid). And if I change to string literal, the result is the same as SingleOrDefault(x => x.UserKey == "AB1-23456"); – Moozz Jan 13 '15 at 09:59
Strange... this may interest you http://msdn.microsoft.com/en-gb/data/hh949853.aspx – Paul Zahra Jan 13 '15 at 11:58
I suspect that the reason maybe because of the way it constructs the cache for the query, the string literal will be defined as a constant, the variable will be parameterised, although I can't see it making that much difference. – Paul Zahra Jan 16 '15 at 13:29

Why is SqlQuery a lot faster than using LINQ expression on views?

3 Answers3