SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ranking functions, views, and predicates


Ranking functions, views, and predicates

Author
Message
Sergiy
Sergiy
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24315 Visits: 12464
try to run
SELECT
BatchID,
FieldA,
FieldB,
CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrder
FROM <myDatabase>.<mySchema>.<myTable>;
WHERE BatchID = @BatchID



and check the execution plan.

It goes for Index Seek, and then for Key Lookup.

That's where is your problem.
Key Lookup is extremely expensive operation and optimizer will avoid such a plan if there is a possibility of any more or less significant number of rows to lookup for.

During my my testing on Tally table optimizer switched from Key Lookup to Index Scan somewhere around 150 rows filtered by the WHERE clause.
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27962 Visits: 18995
Sergiy (3/19/2013)
try to run
SELECT
BatchID,
FieldA,
FieldB,
CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrder
FROM <myDatabase>.<mySchema>.<myTable>;
WHERE BatchID = @BatchID



and check the execution plan.

It goes for Index Seek, and then for Key Lookup.

That's where is your problem.
Key Lookup is extremely expensive operation and optimizer will avoid such a plan if there is a possibility of any more or less significant number of rows to lookup for.

During my my testing on Tally table optimizer switched from Key Lookup to Index Scan somewhere around 150 rows filtered by the WHERE clause.


Agreed - especially if your index doesn't include BatchID AND dateEntered. I'm frankly surprised it uses a BatchID index at all, since it need BOTH columns to determine the row_number value. I think you're seeing a clustered index scan (i.e. a table scan) and it's not trying to rely on ANY index you have.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
tafountain
tafountain
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 389
@Matt & Sergiy - I'll have to repost a better example. I think the point was missed. The predicate applied by the WHERE clause is not pushed down to the view at runtime. This is evidenced by reviewing the execution plan and seeing that a "clustered index scan" is being performed on the underlying table *and* returning all the rows in the table. A few operations later the predicate is applied. My whole point is the ranking function should only execute over the returned data (i.e. the predicate being applied first), not all of the data in the table. This is further illustrated by 2 things - first I removed the ranking function from the view and second, putting the ranking function in the actual select statement over the view. Both examples show the appropriate index seek and only one record is returned.

I'll post better and more elaborate examples later.
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2580 Visits: 2582
tafountain (3/21/2013)
@Matt & Sergiy - I'll have to repost a better example. I think the point was missed. The predicate applied by the WHERE clause is not pushed down to the view at runtime. This is evidenced by reviewing the execution plan and seeing that a "clustered index scan" is being performed on the underlying table *and* returning all the rows in the table. A few operations later the predicate is applied. My whole point is the ranking function should only execute over the returned data (i.e. the predicate being applied first), not all of the data in the table. This is further illustrated by 2 things - first I removed the ranking function from the view and second, putting the ranking function in the actual select statement over the view. Both examples show the appropriate index seek and only one record is returned.

I'll post better and more elaborate examples later.


I'm not sure why you say "the ranking function should only execute over the returned data (i.e., the predicate being applied first), not all of the data in the table." The optimizer can rearrange queries into any logically equivalent form using a number of rules. Predicate pushdown is one way the optimizer can simplify a query, but the optimizer may still estimate the cost of a plan that doesn't push the predicate down to be less than one that does. As I mentioned above, the optimizer likely would estimate the cost of scanning a 60,000-row table to be very low - low enough, in fact, that it might decide that it would cost more to generate and evaluate additional execution plans than to just execute the query with the scan.

Could you post actual execution plans for both queries - with and without the ROW_NUMBER() function call?

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
tafountain
tafountain
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 389
wolfkillj (3/21/2013)

Could you post actual execution plans for both queries - with and without the ROW_NUMBER() function call?


Wolf, I will do that with the other examples. I guess another piece of the puzzle is this, we resolved this by removing the ranking function and simply maintaining a column to support this concept. In doing so the cost of the query was lowered from just under 400 to less than .01 - quite a difference - and the I/O generated was greatly reduced. This particular view is called from a very active web interface by our users constantly throughout the day.

But I will post more in the next day or two to provide a better example.
Sergiy
Sergiy
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24315 Visits: 12464
tafountain (3/21/2013)
The predicate applied by the WHERE clause is not pushed down to the view at runtime. This is evidenced by reviewing the execution plan and seeing that a "clustered index scan" is being performed on the underlying table *and* returning all the rows in the table.


I'm not sure it's totally true.
At the moment - not sure.

As I said, in my test on Tally table I was getting Index Seek + Key Lookup when the number of records selected by WHERE clause was 120 or below.
Execution plan was changed to Table Scan followed by Filter (WHERE) when the number was approaching 150.

So, it's not necessarily to get a table scan when you're doing ROW_COUNT over partition. It only happens when optimizer decides that Key Lookup would be too expensive for the recordset.

But anyway - I salute you decision to go with pre-calculated and stored ranking.
It's still waaaaaay more effective than any of the solutions within SELECT queries, as now the ranking is ccalculated once (may be several times if there are updates to stored data) rather than every time users retrieve it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search