asked on
optimizing a sql server query
I have a table called customer_access that keeps track of what users have access to what customer.
I need to get a list of customer_access records that would give me the list of records where the approval_date is the latest for each of the user-customer combinations.
Currently, I am doing it this way, but I have a feeling that it can be done more elegantly. I am looking to improve the clarity of the sql w/o hurting the performance (or, ideally, improving performance as well).
select * from (select row_number() over (partition by ca.customerId, ca.userId ORDER BY ca.approvalDate DESC) as rownumber, ca.id from customer_access ca) ca1 where ca1.rownumber = 1
I need to get a list of customer_access records that would give me the list of records where the approval_date is the latest for each of the user-customer combinations.
Currently, I am doing it this way, but I have a feeling that it can be done more elegantly. I am looking to improve the clarity of the sql w/o hurting the performance (or, ideally, improving performance as well).
select * from (select row_number() over (partition by ca.customerId, ca.userId ORDER BY ca.approvalDate DESC) as rownumber, ca.id from customer_access ca) ca1 where ca1.rownumber = 1
Microsoft SQL Server 2005SQL
Last Comment
Raja Jegan R
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
reb73 - I forgot to mention another consideration. The approval date might be the same for a given user - customer combination. And when that happens, I want it to return just one of the matching records (doesn't matter which one). If I merely use the max function, as you suggested, I am going to get both.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
reb73,
I consider it as a discussion rather than a dispute.
Yes you are correct. Say, If you have small amount of columns in your Select clause and Group by columns ( Or a simple query like this where we fetch directly from a table )
select id from (
select ca.id, row_number() over (partition by ca.customerId, ca.userId ORDER BY ca.approvalDate DESC) as rownumber from customer_access ca) ca1
where ca1.rownumber = 1
Might have performed less compared to the equivalent subquery.
But for many complex reports in my case, I will be having several Columns in the Partition by and more columns in my SELECT clause.
Hence if I use a subquery, I have to group it out internally for the derived query first, join it again with that derived query ( which needs to be executed again) using multiple columns which is a more costlier option and in this scenario, ROW_NUMBER helped me out.
Revert if you need more detailed explanations.
I consider it as a discussion rather than a dispute.
Yes you are correct. Say, If you have small amount of columns in your Select clause and Group by columns ( Or a simple query like this where we fetch directly from a table )
select id from (
select ca.id, row_number() over (partition by ca.customerId, ca.userId ORDER BY ca.approvalDate DESC) as rownumber from customer_access ca) ca1
where ca1.rownumber = 1
Might have performed less compared to the equivalent subquery.
But for many complex reports in my case, I will be having several Columns in the Partition by and more columns in my SELECT clause.
Hence if I use a subquery, I have to group it out internally for the derived query first, join it again with that derived query ( which needs to be executed again) using multiple columns which is a more costlier option and in this scenario, ROW_NUMBER helped me out.
Revert if you need more detailed explanations.
And it also depends upon the Indexes ( ASC or DESC signifies), Primary keys involved in Group By or Order by clause since it will have a clustered index and will perform better
rrjegan17,
In other words, your statement should have read:
I assure you that this query is an elegant one as it uses ROW_NUMBER analytical function which performs far better than your regular GROUP BY and Subqueries under certain circumstances.
As in any computer related activity, "It kind of depends . . . "
In other words, your statement should have read:
I assure you that this query is an elegant one as it uses ROW_NUMBER analytical function which performs far better than your regular GROUP BY and Subqueries under certain circumstances.
As in any computer related activity, "It kind of depends . . . "
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
reb73, 8080_Diver, mark_wills,
Yes, You all are correct. I should not have made a generic statement without knowing the complete structure of the table and the queries.
aturetsky, Can you provide the complete structure of the table including all indexes so that we can suggest you the optimal approach for your case.
Yes, You all are correct. I should not have made a generic statement without knowing the complete structure of the table and the queries.
aturetsky, Can you provide the complete structure of the table including all indexes so that we can suggest you the optimal approach for your case.