any help with this runs 45 min

Plan.queryanalysis (452.4 kB)
avatar image By dyard 0 asked Dec 02, 2014 at 11:58 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By SQLkiwi ♦ 6.6k answered Dec 03, 2014 at 08:08 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x109
x46

asked: Dec 02, 2014 at 11:58 PM

Seen: 139 times

Last Updated: Dec 03, 2014 at 08:08 AM