SQL Optimize for Unknown Hint and IQueryable

Question

I have a piece of code that is selecting based on dates, using an IQueryable. The sql performance here is causing timeouts. If this was straight SQL, I'd using (OPTIMIZE FOR (@date UNKNOWN). However, with

DateTime now = DateTime.UTC Now;

var results = from item in items.Find()
where item.startDate <= now
where item.endDate > now 
select item;

return results;

Is there a good way to add this hint? Or should I move this to the database as a stored procedure?

It's not the exact code, I pulled it out and changed the variable names to simplify it. The code we has does compile and has been running for some time now. I'll clean it up to make it a little clearer, though. — Feasoron, Jan 29 '14 at 16:32
This is a simple query to stuck in the sql server. Have you set correct indexes? What is selectivity of that columns? Why do you use `Find()` without key? It will be all your items to store then filter them by predicate. — Hamlet Hakobyan, Jan 29 '14 at 16:59

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

~~I don't see how hints are going to help you with performance here.~~ You should first get your query right and see the SQL that is generated by Entity Framework on a SQL profiler. The following snippet should give a decent performance if your database is designed as i expect it is.

var results = item.Where(x=> x.startDate <= now && x.endDate > now)

Check out this related question for index hints on sql server, summarizing, can't use them with linq.

How do I get LinqToSql to pass “index hints” to sql server?

edited May 23 '17 at 12:29

Community

1
1

answered Jan 29 '14 at 16:35

Andrei Dvoynos

1,126
1
10
32

1

if the data is skewed heavily on the range, an optimize hint genuinely can really help; if the first query run has an atypical set of values, very bad things can happen – Marc Gravell Jan 29 '14 at 16:39
Yep, I was actually reading up on that right now, I'll edit my post – Andrei Dvoynos Jan 29 '14 at 16:41

score 1 · Answer 2 · answered Jan 29 '14 at 16:36

Assumptions: Find() returns an IQueryable and there are indexes on startDate and endDate

I don't know of a way to add query hints to Linq since they're provider specific, but you could try a few things:

use && instead of multiple where clauses:

DateTime now = DateTime.UTCNow;
var results = from item in items.Find()
              where item.startDate <= now 
                 && item.endDate > now 
              select item;

Execute straight SQL via ExecuteStoreQuery:

string sql = @"SELECT * FORM Items " + 
              "WHERE startDate <= @asOf
              "  AND endDate > @asOf"
var results = db.ExecuteStoreQuery<Item>(sql, new [] {now});

SQL Optimize for Unknown Hint and IQueryable

2 Answers2