Linq performance: Two queries, the first response immediately and the second is very slow

Question

I have two queries very similars, using the Linq ExecuteQuery method the first take 30 seconds, while the second query is inmediate.

I execute the queries too in Microsoft SQL Server Management Studio and the two queries have a response time of 0 seconds.

Query 1 (slow)

IEnumerable<ViewDataTanksDataDevice> res= 
this.ExecuteQuery<ViewDataTanksDataDevice>(
   "SELECT TOP 1 * FROM ViewDataTanksDataDevicesSB WHERE id_tank = {0} AND date >= {1} order by date", 
   new object[] { tankId, date });

Query 2 (fast)

IEnumerable<ViewDataTanksDataDevice> res= 
this.ExecuteQuery<ViewDataTanksDataDevice>(
   "SELECT TOP 1 * FROM ViewDataTanksDataDevicesSB WHERE id_tank = {0} AND date <= {1}     order by date desc", 
   new object[] { tankId, date });

Note 1: ViewDataTanksDataDevicesSB is a view with SCHEMA BINDING, and it has two index

Index 1(id_tank, date asc)
Index 2(id_tank, date desc)

Note 2: If I execute first the second query the result is identical: Query1 slow and Query 2 fast.

Note 3: The view have millions of registers, and the the results are identical for differents dates and tanks.

You might consider having a look at SQL Server's Profiler tool while executing both of the queries from code and also at the excution plans. It smells a lot like an index issue (ASC vs. DESC) — Krumelur, Mar 09 '12 at 20:24
For a given date, is the working set significantly larger for the first query? In addition, does it respond faster some date input than others? — Brian Dishaw, Mar 09 '12 at 20:31
Have you cleared all the [SQL cached data](http://stackoverflow.com/questions/2243591/how-to-clear-down-query-execution-statistics-in-sql-server-2005-2008)? — DaveShaw, Mar 09 '12 at 21:38
From the symptoms you describe the indexes are being ignored for the slow query, so you're getting a table scan. You shouldn't require both indexes. What happens if you remove one? Also can we see the queries being generated? ExecuteQuery may be doing something odd when generating the SQL. — Phil, Mar 10 '12 at 09:14
Have you tried updating the statistics? `EXEC sp_updatestats`. Because it sound like the query plan is being generated the wrong way — Arion, Mar 10 '12 at 22:17

score 3 · Accepted Answer · answered Mar 14 '12 at 17:38

I resolved it after one week, viewing the execution plan (thanks Yahia by the suggestion)!!!

In the two queries I specified the INDEX (thanks Denis) and I had to specify the hint NOEXPAND.

NOEXPAND explanation can be found at: Table hints

So, the final queries are:

Query 1

IEnumerable<ViewDataTanksDataDevice> res= 
this.ExecuteQuery<ViewDataTanksDataDevice>(
   "SELECT TOP 1 * FROM ViewDataTanksDataDevicesSB with (index(IX_ViewDataTanksDataDevicesSB_TankIdDate) noexpand) WHERE id_tank = {0} AND date >= {1} order by date", 
   new object[] { tankId, date });

Query 2

IEnumerable<ViewDataTanksDataDevice> res= 
this.ExecuteQuery<ViewDataTanksDataDevice>(
   "SELECT TOP 1 * FROM ViewDataTanksDataDevicesSB with (index(IX_ViewDataTanksDataDevicesSB_TankIdDate) noexpand) WHERE id_tank = {0} AND date <= {1} order by date desc", 
   new object[] { tankId, date });

score 1 · Answer 2 · answered Mar 13 '12 at 08:29

Without a comparison of the query plans etc. this is hard to answer...

BUT from your description it seems that both queries are fast when run in SSMS and one of them is slow when run fron .NET - the reason for such a different behaviour might be in the settings used for the DB session, SSMS has defaults that differ from the defaults used in ADO.NET (which is what LINQ uses). A very detailed explanation including several hints on how to solve certain situations in this regard can be found here.

Please post more details, esp. query plans to get more specific help...

score 0 · Answer 3 · answered Mar 13 '12 at 08:42

0

Try adding option(recompile) to the end of the queries.

Update

Your Index 1 and Index 2 are almost identical, you can remove any one of them. It is also possible to hint the engine which index to use with the with(index(ix_index1)) option on the table like so:

SELECT TOP 1 * 
FROM ViewDataTanksDataDevicesSB with(index(ix_index1))
WHERE id_tank = 123 AND date <= '20120313'
order by date desc

answered Mar 13 '12 at 08:42

Denis Valeev

5,975
35
41

Yes... I recompiled and I cleared the execution plan cache too. – Juan Carlos Velez Mar 14 '12 at 17:53

score 0 · Answer 4 · answered Mar 18 '12 at 21:43

0

Could you provide more information from MS SQL Server Profiler? Query plans are the preferred to figure out if it's on MS SQL Server side or on CLR side.

answered Mar 18 '12 at 21:43

amdmax

771
3
14

Linq performance: Two queries, the first response immediately and the second is very slow

4 Answers4