CODEWITHSUNDEEP
sql-serverquery-performancesql-viewquery-hints
Andreas Reiff
Andreas Reiff

Reputation: 8404

Put query hint (OPTION) into view in SQL Server

I have an SQL query on a view using several joins that is occasionally running really slow - a lot slower than normal, making the query nearly unusable.

I copied the query out of the view and experimented and found a solution at https://dba.stackexchange.com/a/60180/52607 - if I add

OPTION (MERGE JOIN, HASH JOIN)

to the end of the query, it is running ~6x faster.

I now tried to adapt the OPTION to the original view, but SQL Server/SSMS tells me

Incorrect syntax near the keyword 'OPTION'.

How can I add this option to the view so that the resulting query of the view is just as fast?

(Adding the option to the query on the view did not result in any speedup. This looked like this:

select * from vMyView
where SomeDate >= CONVERT(Datetime, '2017.09.20')
OPTION (MERGE JOIN, HASH JOIN)

I think I would have to use this option directly for the vMyView - if possible.)

Upvotes: 5

Views: 10047

Answers (1)

Ray
Ray

Reputation: 11

You could add a local hint in the joins in the view

select X, Y from tab1 inner merge JOIN tab1 on tab1.id = tab2.id

Upvotes: 1

Related Questions