0

I have a view that contains multiple joins, and this makes the query run really slow. It takes more than 3 hours to execute this query via the VIEW.

I the copied the SELECT ... part of the statement from the view into a different query window and added the following query hint:

...OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' ), maxdop 0)`

Executing the stand-alone SELECT statement with the query hint reduces the execution time down to 3 minutes.

After that, I tried to adapt this query hint in the original view, and I got this error in SSMS (SQL Server Management Studio):

Incorrect syntax near the keyword 'OPTION'.

So, how can I add this query hint to the view so that the resulting query from the view becomes fast?

2
  • Please always tag your questions with your RDBMS (Microsoft SQL Server?) and version as these can be very important in giving a correct answer. p.s. welcome to the forum! :-)
    – Vérace
    Commented Feb 6, 2020 at 19:45
  • As a side, is the MAXDOP 0 helping? or is just the force legacy estimation?
    – SE1986
    Commented Feb 7, 2020 at 10:43

1 Answer 1

1

Essentially, you will apply this table hint when you are referencing the view. KenJ from SQLServerCentral posted this answer which applies to your question:

select * from your_view
option(use hint('force_legacy_cardinality_estimation'))

SQLServerCentral Thread

You can find another definition from Microsoft Documents.

[force_legacy_cardinality_estimation]...Applies the specified table hint to the table or view that corresponds to exposed_object_name. We recommend using a table hint as a query hint only in the context of a plan guide.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.