0

We have a linked server / db with asset data (not managed/owned by us) that we need to join with a local db (SQL Server 2019).

If we run the queries separately, they all run smoothly - but when doing union (all) it takes 4 minutes. It has previously been running faster but after change of db servers/network things starting to go bad (it's a real old legacy query).

As you can see in the attached execution plan it is kind of nested and probably not optimized, but since it's fast "on its own" we haven't put any effort in it.

The query is part of a view that is used from many other queries so using #temp tables doesn't work.

My usual work-around in these cases is to wrap it in a stored procedure, but then I have to rewrite everywhere the view is used.

Can you see anything obvious that makes the query go from seconds to 4 minutes when using UNION?

Plan: https://www.brentozar.com/pastetheplan/?id=SkqDhIUza

Query plans from when everything runs smoothly, for comparison:

  1. https://www.brentozar.com/pastetheplan/?id=SJsH5CUfa
  2. https://www.brentozar.com/pastetheplan/?id=SJujcRIzp

They take some seconds each to execute.

2
  • If the results are always different, you might want to try UNION ALL. This removes the part of the query where duplicates are removed.
    – Peter
    Commented Oct 27, 2023 at 17:15
  • It must have been some problems with the remote servers or network.. While I was snoozing this "fight" a couple of times and tried again. Now the UNION query is down to a couple of seconds again.
    – Rendi
    Commented Nov 8, 2023 at 9:29

1 Answer 1

0

Not a knowledgeable answer, but if in your changes you upgraded the version of MSSQL you could try appending this to your query:

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

Worth a shot if nothing else.

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.