October 25, 2010 at 5:23 pm
I was just looking through one of my queries quickly, and it seems that the application of a windowing function in a view doesn't allow the server to make use of the index? Is this correct?
My example is that I have two select statements which are UNION'd together. Each select statement pulls from table where the employee ID is indexed. When I try to take this UNION'd result and apply a rank() function to it, and query based on a single employee it seems to sort the entire UNION'd select statement and then query the entire result set based on an employeeId, rather than Filtering the UNION'd select statement and then ranking it. This seems to take a heck of a long time, and I am wondering if my syntax is wrong.
Here is the query I am currently using, and then the one that goes much faster.
SLOW ONE:
SELECT
*
FROM
(
SELECT
EmployeeID,
ReadingTypeID,
ReadingTypeRank,
ReadingValue,
ResultDate,
RANK() OVER(PARTITION BY EmployeeID,ReadingTypeID,ResultDate ORDER BY ReadingTypeRank ASC) AS Rnk
FROM
(
SELECT
EmployeeID,
ReadingTypeID,
ReadingTypeRank,
ReadingValue,
ResultDate
FROM
View_MethodOne
UNION ALL
SELECT
EmployeeID,
ReadingTypeID,
ReadingTypeRank,
ReadingValue,
ResultDate
FROM
View_MethodTwo
)AS UnionedResults
)AS FinalSelect
WHERE
EmployeeID = 123 AND Rnk = 1
FAST ONE:
SELECT
*
FROM
(
SELECT
EmployeeID,
ReadingTypeID,
ReadingTypeRank,
ReadingValue,
ResultDate,
RANK() OVER(PARTITION BY EmployeeID,ReadingTypeID,ResultDate ORDER BY ReadingTypeRank ASC) AS Rnk
FROM
(
SELECT
EmployeeID,
ReadingTypeID,
ReadingTypeRank,
ReadingValue,
ResultDate
FROM
View_MethodOne
WHERE EmployeeID = 123
UNION ALL
SELECT
EmployeeID,
ReadingTypeID,
ReadingTypeRank,
ReadingValue,
ResultDate
FROM
View_MethodTwo
WHERE EmployeeID = 123
)AS UnionedResults
)AS FinalSelect
WHERE
Rnk = 1
You can see the distinctive difference is where I put the 'WHERE EmployeeID = 123'. Putting it inside the UNION'd selects makes this extremely fast. But I can't do this as it is a view which I must query by different sets of employees. Also, the MethodOne and MethodTwo would be views that pull from indexed tables and join to a couple other things.
Any ideas or thoughts on this?
October 25, 2010 at 5:45 pm
I recently ran into this one head first.
The view using ROW_NUMBER() has to compute all the rownumbers before the where clause from the outer call gets involved.
So SELECT * FROM view WHERE fld1 = 1... computes the ENTIRE view for Row_number()... then filters on fld1 = 1.
I don't think there's a solution for this, but I yanked the row_number() out and forced it into the select from view to remove the issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy