CODEWITHSUNDEEP
sql-serverlinq-to-sqlexecutequery
Juan Carlos Velez
Juan Carlos Velez

Reputation: 2940

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

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

  1. Index 1(id_tank, date asc)
  2. 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.

Upvotes: 3

Views: 733

Answers (4)

amdmax
amdmax

Reputation: 761

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.

Upvotes: 0

Juan Carlos Velez
Juan Carlos Velez

Reputation: 2940

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 });

Upvotes: 3

Denis Valeev
Denis Valeev

Reputation: 6015

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

Upvotes: 0

Yahia
Yahia

Reputation: 70369

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...

Upvotes: 1

Related Questions