Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Ranking functions, views, and predicates Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 6:08 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #1432953
Posted Wednesday, March 20, 2013 10:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 7,178, Visits: 15,763
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?
Post #1433327
Posted Thursday, March 21, 2013 1:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 6:45 AM
Points: 104, 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.
Post #1433987
Posted Thursday, March 21, 2013 1:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
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
Post #1434002
Posted Thursday, March 21, 2013 1:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 6:45 AM
Points: 104, 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.
Post #1434006
Posted Thursday, March 21, 2013 3:25 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #1434055
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse