The query and the execution plan are available at the link below.

This is from vendor application that I support. I know that there has to be a better way to write this code but the vendor says that it is the best that they can do.

I have a problem with the number of joins, the indiscriminate use of nolock, the top(1) and the use of the subqueries.

I am looking for a recommendation on how to do this better as the vendor does not appear to have the ability to improve on the query.

This query negatively impacts the performance of my production environment. Any advice would be greatly appreciated.


  • I can see that you posted the estimated execution plan. Any chance we can get to have a look at the actual execution plan? – Radu Gheorghiu Nov 7 '17 at 8:51
  • I have uploaded the actual execution plan unfortunately it is during low volume part of the day brentozar.com/pastetheplan/?id=HyCv7zJkz – Andries Vermaak Nov 7 '17 at 11:06
  • You left out using SELECT * instead of an explicit list of columns needed - or, are all of those columns actually needed? – RDFozz Nov 7 '17 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.