5

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.)

TT.
  • 15,774
  • 6
  • 47
  • 88
Andreas Reiff
  • 7,961
  • 10
  • 50
  • 104
  • 1
    What's the definition of `vMyView`? – Richard Sep 27 '17 at 08:14
  • 3
    Looks like `OPTION` clause is not supported in views, just like `ORDER BY` due to internal optimizations of SQL Server when pulling rows. – BytesOfMetal Sep 27 '17 at 08:16
  • 2
    There are very rear cases when you really need hints. Try to avoid them in any possible time. I am pretty sure that you can fix your query by using right indexes. One more bad thing with your query is SELECT * .... don't do that. – Dmitrij Kultasev Sep 27 '17 at 08:16
  • 6
    The [documentation on CREATE VIEW](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql) explicitely states: `The SELECT clauses in a view definition cannot include [...] The OPTION clause`. Tough luck. Your best bet is to analyze the execution plan, see what is incorrect about the query (complexity, missing indexes, ...). – TT. Sep 27 '17 at 08:31
  • Thanks for all your answers! The indices should be correct, otherwise it would never run fast?! It is just the execution plan/statistics that have the problem? – Andreas Reiff Sep 27 '17 at 08:32
  • To overcome that limitation you can use table valued function instead of a view. https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx – Mihail Shishkov Sep 27 '17 at 08:33
  • Table value functions cannot use options either. At least not the inline ones – siggemannen Dec 22 '21 at 19:22
  • post view definition, tables schema and indexes. Index HINTS will almost always come back to bite you. – Mitch Wheat Jun 13 '22 at 03:14

1 Answers1

0

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
Vladimir Vagaytsev
  • 2,871
  • 9
  • 33
  • 36
Ray
  • 1
  • 1
    While this might answer the authors question, it lacks some explaining words and links to documentation. Raw code snippets are not very helpful without some phrases around it. You may also find [how to write a good answer](https://stackoverflow.com/help/how-to-answer) very helpful. Please edit your answer. – hellow Sep 12 '18 at 08:30