Function call as INNER JOIN causes slow RID lookup

Neo 2014-02-12 13:09:08

I'm struggling with this query and it appears to be particularly slow due to my use of RANK() OVER which I need later in order to pick the highest row (within each partition) from two subsets of data.

Looking at the query plan, there are massive cardinality issues indicated by the large differences between estimated rows and actual rows.

My gut feeling is that there is nothing wrong with the query itself, but perhaps the way the statistics and/or indexes are set up on the tables. The statistics are updated daily so I know it's not a case of outdated stats.

Would appreciate any help anyone can give on this.

Edit: Based on help and advice given so far, I've attached non-anonymised execution plans for the query before and after the change that speeds it up. Here's the one with the function call as part of an INNER JOIN. Here's the one with the function call in the WHERE clause instead. The latter runs much quicker and yields the same query results. It appears that removing the RID lookup that Sujai Karnam and SQLkiwi mentioned is problematic improves the performance massively.

/storage/attachments/770-Query_Anonymized.queryanalysis

/storage/attachments/774-fncallinjoin.queryanalysis

/storage/attachments/775-fncallinwhere.queryanalysis

Dave Ballantyne 2014-02-12 13:56:10

Always hard to give any decent advice with an anonymized plan…

But, remove the table valued function.
A)dump the results of that into a temp table and use that in the query if using 100% of its output
B) find another way to to its activity if not.

The problem is TVF always estimate at one row, here it is returning 564 rows.

Table2 is also giving a relatively poor estimate, the error of which is being magnified up the tree.

HTH

Sujai Karnam 2014-02-12 16:52:18

Although the RID Lookup operator has a cost of 0.1%, the number of iterations (1,185,988 times lookups)is very high. The lookup is Column 18 of Table 14. If the Table14.Index9 is altered to include the Column 18, there should be good improvement.

Neo 2014-02-13 18:12:25

Thanks for all your help so far. I've looked at the suggestions and eliminated the columns and tables mentioned from the query by removing them completely and the query still runs very slow. As an exercise, I have whittled it down to the bare minimum so that it still runs very slow, but with only the two JOINs that cause it to take long. Without either of these (even with everything else included), it runs quickly.

The non-anonymised query analysis (see here) shows an horrendous number of rows being outputted, but it takes a long time in SSMS before it even starts outputting the rows to the 'Results' window. What I found is that the function call simply returns a single-column list and the purpose of the JOIN is to filter the results on that list. It occurred to me that this should actually go in the WHERE clause.

So, the resultant query is

SELECT * FROM F_V_IrZeroDelta (NOLOCK) IRD
    INNER JOIN dbo.D_Trade (NOLOCK) T ON T.Trade_PK = IRD.Trade_FK_LK
WHERE T.TradeTypeName_LK IN (SELECT TradeTypeName_LK FROM feeds.fn_GetTradeTypes(NULL))

This is much quicker although not perfect – it still takes several minutes, but that's better than over an hour.

/storage/attachments/771-query.queryanalysis

Dave Ballantyne 2014-02-13 20:02:06

So, two things.

What is the function feeds.fn_GetTradeType doing ?
Does that run fast in isolation ?
Cache the output to a temp table and then join , is that better ?

Second the result set is massive , plan explorer states it 268,998mb , erm 268gb.
Think how long that takes to transfer ?!

Try running the query again in SSMS using the "Discard results after execution" option on, SSMS is really slow at displaying data

Sujai Karnam 2014-02-13 21:08:34

This new query has no WHERE predicate compared to the earlier plan. The earlier plan (anonymized) indicated actual rows finally returned as 4 rows compared to this non-anonymized showing 649 million. This is like comparing apples to oranges. The performance cannot be compared here. Could you share the non-anonymized plan for the actual query?

Neo 2014-02-14 11:17:37

OK, I've edited the original question to include the non-anonymised plans for the whole query both before and after the change that improves its performance greatly. It appears that removing the RID lookup sorts it out.

Sujai Karnam 2014-02-18 03:31:07

1. The table variable (@Jobs) is one of the main culprit limiting the optimizer to generate an inefficient plan. The estimated number of executions is 1 versus actual executions 7 on the F_IrZeroDelta Clustered Index Seek in conjunction with estimated rows 331K versus 9679K indicates that seek was inefficient and sql server could have done something better. The Threads 1 to 4 on the F_IrZeroDelta Clustered Index as shown in the Plan Tree also indicates the same wherein the load was not balanced and Thread 0 had to wait unnecessarily for all the 4 threads to complete successfully. This could have increased the CXPACKET wait.

2.The Statistics of the table is "F_V_IrZeroDelta" is either outdated or is based on a small sampling

Sujai Karnam 2014-02-18 03:39:17

I see you moving the function call feeds.fn_GetTradeTypes(@TradeTypeFilter) from Inner Join to IN clause. I wonder if and how could that have help improve the performance? I would have tried the following options, but never IN Clause esp when we have such large resultset:
1. Extract the function output to a temp table and joined that temp table in the query.
2. Or, make it a sub query say, INNER JOIN (SELECT /DISTINCT/ TradeTypeName FROM feeds.fn_GetTradeTypes(@TradeTypeFilter) ) TT ON TT.TradeTypeName = T.TradeTypeName_LK

February 12, 2014

No Comments