User complaining the query taking more than 50 sec to execute

avatar image By rockerz2155 1 asked Jan 10, 2017 at 03:17 PM
more ▼
(comments are locked)
avatar image rockerz2155 Jan 11, 2017 at 03:38 PM

Hello, Thanks for the response. I have attached the actual plan this time. Can you please recommend now. Much text

10|10000 characters needed characters left

1 answer: sort voted first

Well, immediately I'm drawn to the Table Scan on Object11 that's feeding a Hash Match (Inner Join),and then later, a Table Scan on Object11 that's feeding a Hash Match (Aggregate). With an index on Column42 (which is being used for the Probe into the Hash Match), you'd be a lot better off. But your plan also has some Missing Index recommendations, which I'd suggest you consider.

  Object11(Column42, Column54) INCLUDE (Column17, Column53, Column52, Column51)
  Object11(Column54) INCLUDE (Column42, Column17, Column53, Column52, Column51)

The Scan that's feeding the Aggregate might be worth considering for a ColumnStore Index if the data is mostly insert-only, or having an indexed view to have the aggregates pre-calculated.

There are also a bunch of TVFs that I can't see the detail of. And without the statements, which have been shrunk right down to simply "Statement1" and "Statement3", I can't suggest rewrites. But sorting out Object11 is definitely going to be useful. Start with creating the first of those indexes to see if the impact is significant.

avatar image By Rob Farley 196 answered Jan 11, 2017 at 02:02 AM
more ▼
(comments are locked)
avatar image Rob Farley Jan 13, 2017 at 06:51 AM

Oh! Now that I can see the Actual plan, I see you have TVFs that need work.

FN_NEXTGEN_PGM_CLAIM_LOSS_UDD_COMBINED looks like it's defined as a multistatement TVF but should be an inline TVF.

FN_NEXTGEN_CLAIM_LOSS_UDD_COMBINED does a bunch of looping, and shouldn't! I'd recommend you explore other options for doing that.

Oh, and FN_AEG_MVParam is bad. That should be an inline function if possible.

You could get some amount of improvement by having an index on POLICY_ANCHOR_DIM (POLICY_NBR, RECORD_TYPE_CD) INCLUDE (PREVIOUS_POLICY_NBR) - but you really should rework those functions a lot.

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.