March 23, 2015 at 1:19 am
Hi All,
Need one help in the following query using adventureworks database.
Select s.businessentityid,
sum(s2008.salesquota) Total2008
,sum(s2007.salesquota) Total2007
from sales.salesperson s
left outer join sales.salespersonquotahistory s2008
on s.businessentityid=s2008.businessentityid
and year(s2008.quotadate)= 2008
left outer join sales.salespersonquotahistory s2007
on s.businessentityid=s2007.businessentityid
and year(s2007.quotadate)= 2007
group by s.businessentityid
Is it possible to write this query using sql windows function? I am not able to think of the logic .
I want to see if there are performance benefits with alternate logic.
Thanks
Aslam.
March 23, 2015 at 1:30 am
I see no way (or reason) to introduce windowing functions here.
I suspect that you would see a large performance improvement if you were to add a QuotaDateYear column to your SalespersonQuotaHistory table (possibly as a computed persisted column) and ensure that the table is indexed appropriately.
If you were to post the execution plan, we could probably confirm that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 23, 2015 at 1:40 am
Thanks Phil.
I was going through windows function and come across this query.
So just thinking if this can be done using them.
We have few queries in our environment similar to this one. May be I can apply same logic and improve performance.
Aslam.
March 23, 2015 at 1:50 am
Perhaps. If you frequently need to filter based on year.
Alternatively, if there is already a suitable index on the datetime column, change your filter from
Year(col) = 2013
to
col >= '20130101' and col < '20140101'
Coding in this way should allow the optimiser to utilise the index and avoids the scalar function.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 23, 2015 at 1:53 am
Thanks Phil.
Still if there is any alternate logic . Its welcome.
Thanks
Aslam
March 23, 2015 at 2:07 am
I just noticed another thing ...
If you do a LEFT JOIN from table A to table B, you are telling SQL to return all rows which match, plus all rows in table A which have no match. For these rows in table A which have no match in table B, return NULL in the table B columns.
In your case, you are filtering on table B.year = YYYY. This will filter out any of the unmatched rows from table A, rendering the LEFT JOIN superfluous.
You should either
1) Change your LEFT JOINs to INNER JOINs if the unmatched rows are not required, or
2) Change your selection criteria to include NULLs:
B.year = YYYY or B.year is null.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 23, 2015 at 2:33 am
Thanks .
Aslam.
March 23, 2015 at 2:42 am
aslamnepanagar (3/23/2015)
Hi All,Need one help in the following query using adventureworks database.
Select s.businessentityid,
sum(s2008.salesquota) Total2008
,sum(s2007.salesquota) Total2007
from sales.salesperson s
left outer join sales.salespersonquotahistory s2008
on s.businessentityid=s2008.businessentityid
and year(s2008.quotadate)= 2008
left outer join sales.salespersonquotahistory s2007
on s.businessentityid=s2007.businessentityid
and year(s2007.quotadate)= 2007
group by s.businessentityid
Is it possible to write this query using sql windows function? I am not able to think of the logic .
I want to see if there are performance benefits with alternate logic.
Thanks
Aslam.
Possibly, but you might benefit from exploring more conventional methods like this first:
-- Reads sales.salespersonquotahistory only once
-- Date filter is SARGable: WHERE quotadate >= '20070101' AND quotadate < '20090101'
SELECT
s.businessentityid,
Total2008 = MAX(CASE WHEN qh.[Year] = 2008 THEN [SUM_salesquota] ELSE 0 END),
Total2007 = MAX(CASE WHEN qh.[Year] = 2007 THEN [SUM_salesquota] ELSE 0 END)
FROM sales.salesperson s
LEFT JOIN (
SELECT
businessentityid,
[Year] = YEAR(quotadate),
[SUM_salesquota] = SUM(salesquota)
FROM sales.salespersonquotahistory
WHERE quotadate >= '20070101' AND quotadate < '20090101'
GROUP BY businessentityid, YEAR(quotadate)
) qh
ON qh.businessentityid = s.businessentityid
GROUP BY s.businessentityid
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2015 at 5:25 am
Hi Chris,
Though the results are not matching . I like the logic.
I keep it open for a day for any other approach else I will mark as solution.
Aslam
March 23, 2015 at 5:41 am
aslamnepanagar (3/23/2015)
Hi Chris,Though the results are not matching . I like the logic.
I keep it open for a day for any other approach else I will mark as solution.
Aslam
Can you show your existing query and the remodel based on the code I posted?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2015 at 5:57 am
Unfortunately , I can't. Hence I tried to create similar using adventureworks.
The one posted in beginning.
Aslam.
March 23, 2015 at 8:01 am
Although both queries below compute the same result set i see no reason why to go this way. 'Traditional' GROUP BY performs by far better.
select distinct [SalesOrderID], s = sum([LineTotal]) over(partition by [SalesOrderID])
from [Sales].[SalesOrderDetail];
select [SalesOrderID], s = sum([LineTotal])
from [Sales].[SalesOrderDetail]
group by [SalesOrderID];
Just for fun? 🙂
March 23, 2015 at 8:17 am
I've run into performance issues w/ windows function over time. I've stopped using them alltogether. that's just my 2 cents
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply