Clever Geek Handbook

Linq performance: two requests, first response at once and second very slow

I have two queries very similar using the Linq ExecuteQuery method, the first one takes 30 seconds and the second query is intermediate.

I am also running queries in Microsoft SQL Server Management Studio and two queries have 0 second response time.

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

Request 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 indexes

  • Index 1 (id_tank, date ascending)
  • Index 2 (id_tank, date desc)

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

Note 3: The view has millions of registers and the results are identical for dates and tanks of different sides.

+3


source to share


4 answers


I resolved it after a week by reviewing the execution plan (thanks Yahia for the suggestion) !!!

In two requests, I specified INDEX (thanks Denis) and I needed to specify the NOEXPAND hint .

An explanation of NOEXPAND can be found at: Table Hints

So the final requests:



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

Request 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 });
+3


source


Without comparing query plans, etc. it's hard to answer ...

BUT from your description it seems that both requests are fast when they run in SSMS and one of them is slow when fron.NET starts up. The reason for this behavior could be in the settings used for the DB session, SSMS has default values ​​that are different from the defaults used in ADO.NET (which is what LINQ uses). A very detailed explanation, including some tips on how to resolve specific situations in this regard, can be found here .



Please write more details especially. request plans to get more specific help ...

+1


source


Try adding option(recompile)

to the end of your queries.

Update

Your index 1 and index 2 are almost identical, you can delete any of them. You can also specify the engine that is indexed for use with the option with(index(ix_index1))

in the table, for example:

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


source


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

0


source






More articles: