0

I am working on some search optimizations in a WPF application.

There is a search XAML view, that uses a SQL Server view with IQueryable.

In SQL Server, I can use OPTION(QUERYTRACEON 8649) when I get the SELECT part from the view, but in the view itself, I can't use it. This OPTION speeds up my query more than 3 times. I clear cache each time I execute it.

Because of this SQL Server view's limitation, I need to somehow add this to C# code.

So, how can I add this OPTION to be used in IQueryable?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3239349
  • 877
  • 1
  • 12
  • 33
  • 1
    EF is not the best approach for complex querying in any case. We use EF only for CRUD. For searches we build custom queries dynamically. Everything starts with taken path. I believe, you took wrong path from the start. Now, you've mentioned a view. So, if you delegated query to a view, what is the problem to create this view with all needed hints in the view? – T.S. Jul 22 '20 at 22:21
  • When I tried to add this OPTION into VIEW it says that 'alter view must be the only statement in the batch' – user3239349 Jul 22 '20 at 22:26
  • 'alter view must be the only statement in the batch' means you need to have a GO statement between the ALTER and any other statement. – Jason Jul 22 '20 at 22:31
  • But OPTION(QUERYTRACEON 8649) should be part of the SELECT in VIEW. And also, I tried to add GO, but with no luck. If you have any example where that works, it would be nice to see it. – user3239349 Jul 22 '20 at 22:32
  • No ... it means that there is another statement in your script besides the ALTER VIEW statement. – Jason Jul 22 '20 at 22:34
  • You could post the View Script you are using and I could look it over ... – Jason Jul 22 '20 at 22:35
  • If you have any working example of Alter/Create view with this option it would fix my issue. – user3239349 Jul 22 '20 at 22:38
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/218394/discussion-between-user3239349-and-jason). – user3239349 Jul 22 '20 at 22:42

1 Answers1

0

You can add query hints by using interceptor.

Read details from this sample by microsoft.

Also, you have to register interceptor class like this.

private void DependencyInjection(IServiceCollection services)
{
 services.AddDbContext<SampleDbContext>(options =>
                options.UseSqlServer("some connection string") 
                       .AddInterceptors(new TaggedQueryCommandInterceptor())
 );
}
spacekoki
  • 127
  • 1
  • 9