Not sure what's wrong with my query

Plan.pesession (288.9 kB)
avatar image By Sipho Mhlanga 1 asked Dec 20, 2017 at 02:36 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

At the top left of your execution plan is a Nested Loops join. The top input has 664 rows (699 estimated). The bottom input includes a Table Spool operator. That is SQL Server saying "hey I just did a lot of work getting you this result and you are probably going to need it again, so I'll save it for you". It can do this because the work done in the lower branch is done for a specific value of the expression [CPPRAFR].[uga].[CUSTOMER].[ID].

The optimizer thinks that this branch will run 699 times, mostly for the same ID value. So it feels good about having a relatively expensive branch to get the results because they are reused and not re-evaluated.

In reality, the ID value changes on each of the 664 rows, so the effort of saving the results is wasted - and what's worse, the huge effort of producing them is repeated 664 times.

Your query text is not only quite complex, it is also littered with data type conversions. These make it incredibly hard for SQL Server to predict things like the above; this might well be the reason why the query is not performing well. If you don't need these conversions throw them out. If you do need them, take action to get your data model fixed.

Oh, and you should also reconsider your use of NOLOCK. Unless you don't care whether the results are correct or not.

avatar image By Hugo Kornelis 211 answered Dec 21, 2017 at 07:21 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

Without spending too much time on the query, here are a few things I believe I am noticing:

• From that I can tell, you are doing a left outer join below, but not using data from the Address table? Recommendation: Look at the entire query and eliminate joins on any table(s) that are not really needed.

LEFT JOIN [CPPRAFR].[uga].ADDRESS a WITH ( NOLOCK ) ON c.id = a.ADDRCUSTNR AND a.ADDREVENTNR=100

• There is another part of the query,

INNER JOIN [VSCSTAGING_AUDIT].[DBO].[DELTASTUBSUGA] AS CL ON CAST(AB.new_ibscustomernumber AS VARCHAR) = CAST(CL.ICCCUSTOMERNUMBER AS VARCHAR) WHERE new_ibscustomernumber IS NOT NULL

I recommend you convert it to a "NOT EXISTS" in the filter? You are doing an INNER JOIN, then adding a "IS NOT NULL"? Here is a good reference for review:

https://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

avatar image By DBNewbie_2007 0 answered Dec 21, 2017 at 08:22 PM
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:

x664
x474
x121
x111
x47

asked: Dec 20, 2017 at 02:36 PM

Seen: 24 times

Last Updated: Dec 21, 2017 at 08:22 PM