any help with this runs 45 min

dyard 2014-12-02 23:58:38

SQLkiwi 2014-12-03 08:08:07
In rough order of priority:

  1. Get rid of the scalar user-defined function dbo.fnGetNumericOnly.
  2. Avoid the Lazy Spool over the heap scans by providing a suitable index.
  3. Consider rewriting the view or CTE as an in-line table-valued function so you can position parameterized predicates directly.
  4. Remove the RID lookups by providing covering indexes.
  5. Check you really need UNION rather than UNION ALL.
  6. Eliminate the implicit conversions.

Break the query up if possible. Many of the problems in this query plan exist because row count estimates are incorrect. Assuming base statistics are representative, this is purely down to the complexity of the query, and opaque nature of some of the operations.