2

I have a fairly complex query that joins several tables and views. It executes pretty quickly (~9 seconds for 2200 rows), except when I add a column from a view that is already joined in the query the query seems like it will never finish - I left it running for 33 minutes once and it had not completed.

SELECT
    table1.col1,
    table2.col2,
    view1.col3,
    view2.col4

FROM
    table1
    INNER JOIN table2 ON table1.id = table2.id
    LEFT OUTER JOIN view1 ON view1.id = table1.id
    LEFT OUTER JOIN view2 ON view2.id = view1.id
    LEFT OUTER JOIN view3 ON CONVERT(CHAR(8),view1.DateRequired,112) + LEFT(view2.ItemNumber, 4) = view3.UID

The above will run fine, but when I add any column from view3 into the SELECT list the query slows down to a crawl. I'm unable to get the full result set but if I limit the result to 50 rows it takes around 1 minute to run to completion compared to roughly 1 second with the original query.

Now, I know that the join for view3 is pretty complicated; it converts one value from a date to a string and then concatenates this to a value from another table and uses this resulting concatenated blob of info to join against the same style code in view3. However, I would expect that if this were a problem with the join itself then it would slow down the query when the view itself is joined, not just when columns from that view are added into the SELECT list.

I managed to run an execution plan when the extra column is added and it is absolutely colossal. I did manage to find one task that has a cost of 51%: RID Lookup (Heap). I'm not au fait with execution plans so not sure what this means or how to resolve it.

UPDATE

Using Scott's answer I created a non-clustered index on the row that was causing the expensive RID lookup. It improved performance quite a lot - down from over a minute to around 30 seconds to return the 50 row limit.

I think Nic is right in his suggestion that the datatype conversion from date to char(8) in the join condition is causing the bulk of the cost when querying. Going to work on another way to join view3.

  • 3
    Because it's a left join the optimizer knows that, as you aren't returning any data from view3 that it can eliminate it from the plan. As soon as you need it returned that join has to be evaluated. This means doing a data conversion on all of the rows from view1 and all the rows in view2, which, depending on row counts, is going to hurt pretty badly. – Nic Nov 28 '16 at 16:27
  • That actually makes perfect sense... thanks Nic – ryansin Nov 28 '16 at 16:32
  • Is there any way to optimise this conversion? Unfortunately the UID in view3 is the same amalgamated mess as the conversion I'm trying to use as the condition. – ryansin Nov 29 '16 at 8:49
  • Could you pre-evaluate the complex join predicate as a computed, persisted column and index it? – Michael Green Nov 29 '16 at 11:42
2

A RID Lookup is a lookup into a heap table using a Row ID. The Row ID is included in a non-clustered index in order to find the rest of a table's data in the heap table. Since a heap table is a table without a clustered index and is sorted unordered, a Row ID is required for the correlation.

My guess is that view3.UID is found easily via a non-clustered index with that column. When you actually start asking for other columns (by specifying them in the SELECT list that are not part of that non-clustered index, Sql Server has to go find the other data items in the unordered heap - that's the RID Lookup - which can be quite expensive depending on the amount of data you're dealing with.

You 'might' see improved performance if you can identify the 'source' tables referenced in view3 and possibly 'including' your SELECT columns in supporting indexes on those source tables - this is called a 'covering index' in that the index is able to 'cover' or retrieve all of the information you're asking for without having to go elsewhere (clustered-index or heap)

This link has some good information on RID Lookups.

Here is a brief summary of the main points of the link:

When you find an RID Lookup in a query plan, it’s a symptom. It indicates a database schema that breaks several rules of thumb. Those rules-of-thumb are:

  • Each table should have a clustered index (of course there are exceptions but we’re dealing with rules-of-thumb here).
  • A non-clustered index has been created indicating that someone somewhere identified an ordering on one or more columns that made sense for that data.
  • There is at least one query (i.e. the one that generated the RID Lookup) that needs columns that are not covered by the non-clustered index.

Something else to 'try' - use a Common Table Expression (CTE) in an attempt to force Sql Server to 'materialize' view3. Before your main SELECT, add the CTE - this 'assumes' that no more than 2147483648 (max int value) could be returned - adjust as needed.

with View3Materialzed as
(
select top 2147483648 UID from view3 order by UID
) 

Then, use the CTE View3Materialzed in the regular joins. I have used this technique with some success. It may or may not help you.

  • I see - so if I can identify the column that isn't indexed I should create a new non-clustered index that covers that column? – ryansin Nov 29 '16 at 8:53
  • I managed to locate the column and add the non-clustered index, which improved performance slightly (down from over a minute to just ~30 seconds for 50 rows). Still too slow to be practical though; I think it's the conversion on the join as Nic suggested that's causing the bulk of the problems. – ryansin Nov 29 '16 at 9:27
  • Is this process in a stored procedure? If so, you 'might' get some benefit by 'materializing' view3 into a temp table (if feasible) and adding appropriate indexes on the temp table and using that to join to the other views/tables. So, SELECT * INTO #TempView3 from view3, then add appropriate indexes to #TempView3 and use that in your join – Scott Hodgin Nov 29 '16 at 10:32
  • It's not in a stored procedure, and I need to display the result set in Excel so don't think that's possible to do without using a view. – ryansin Nov 29 '16 at 10:41
  • 1
    I updated my answer with a 'suggestion' that you might be able to use a Common Table Expression in an attempt to force Sql Server to 'materialze' view3. It may or may not be of help to you. – Scott Hodgin Nov 29 '16 at 12:04
0

SOLUTION

Originally in view3, the code column was a concatenation of a converted date and a slice of text from another column (a similar fashion to the join condition that was used in this view).

view3 old

SELECT CONVERT(CHAR(8), t1.DateRequired, 112) + LEFT(t2.ItemNumber, 4) code
FROM t1
INNER JOIN t2 ON t1.id = t2.id

What I did was include the unconverted date from the source tables in this view and used these for the join instead.

view3 new

SELECT t1.DateRequired, LEFT(t2.ItemNumber) ItemClass, CONVERT(CHAR(8), t1.DateRequired, 112) + LEFT(t2.ItemNumber, 4) code
FROM t1
INNER JOIN t2 ON t1.id = t2.id

Query new

SELECT
    table1.col1,
    table2.col2,
    view1.col3,
    view2.col4

FROM
    table1
    INNER JOIN table2 ON table1.id = table2.id
    LEFT OUTER JOIN view1 ON view1.id = table1.id
    LEFT OUTER JOIN view2 ON view2.id = view1.id
    LEFT OUTER JOIN view3 ON view1.DateRequired = view3.DateRequired AND LEFT(view2.ItemNumber, 4) = view3.ItemClass

The query executes to completion (2200 rows) in around 30 seconds now as opposed to 33 minutes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.