1

I have a multi-tenant database that returns vastly different numbers of rows depending on which tenant is being queried. Lately we are experiencing a parameter sniffing problem where Entity Framework (EF) queries executed against one tenant (TenantID = 1) take much longer than the same query against another tenant (TenantID = 2). I've done some research and determined that EF doesn't support query hints (see this question) that would allow me to force the query to recompile each time. Now I'm wondering if I can intercept the Sql query that is generated by EF and manually append "OPTION (OPTIMIZE FOR UNKNOWN)" before it is executed. Is this possible? Is EF pluggable such that I can modify the generated Sql before it is executed? Are there any examples of how to do this?

Community
  • 1
  • 1
Mike
  • 7,500
  • 8
  • 44
  • 62

1 Answers1

1

Have you tried working around the problem? You may be able to force EF to not use a param, e.g.:

var q = Context.Tenants.Where(t => t.TenantID == tenantId);

...will use a param, but:

var r = Context.Tenants.Where(t => t.TenantID == 1);

...won't, and I'll bet that:

var s = Context.Tenants.Where("it.TenantID = 1");

...won't, either.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • Thanks for your suggestion. Unfortunately, building sql queries on-the-fly as you are doing here (although indirectly) is considered poor practice due to the high risk of sql injection attacks. Parameterized sql is the best and easiest approach for keeping sql injection out of your application. – Mike Mar 29 '12 at 13:26
  • 3
    Well, that's why the EF uses parameters by default. However, injection is only a risk if you don't sanitize your inputs. In the specific case you give, allowing only integer inputs will remove all risk of injection (though not risk of direct object references, but params won't protect you from that, either...). – Craig Stuntz Mar 29 '12 at 17:03
  • And if you are using Fortify scans, it's not smart enough to know you've sanitized your inputs before generating the SQL. ;) – John Kaster Aug 12 '13 at 17:42