Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kotonikak
Yak Posting Veteran
92 Posts |
 Posted - 2012-06-21 : 15:26:52
|
| I have the following code:with Denom as ( select a.seq, a.fx, sum(b.fx) as Denominator from DY a inner join DY b on a.seq >= b.seq group by a.seq, a.fx )which is performing the following action:seq fx Denominator1 0.01 0.012 0.01 0.023 0.01 0.034 0.01 0.045 0.01 0.05My actual data has 50,000 rows and along with this calculation, I'm performing other more complicated ones that take time. Because my data is so large and I have various calculations, this query was taking over 15 minutes to execute (more since I finally just stopped it). I was wondering if there's a way to simplify the calculation of "Denominator" by using a loop within a common table expression? Basically, start at 0 and keep adding 0.01 to my Denominator values as sequence increases. I don't know if this is going to make it faster, but I wanted to try and see since that's my only other option right now....I am also using a cross apply to calculate something else after this so that does not help with speed either... Any help will be appreciated. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-06-21 : 15:32:35
|
| Is the denominator just seq*1.0/100? JimEveryday I learn something that somebody else already knew |
 |
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2012-06-21 : 15:46:20
|
| Yeah I guess that would seem like it. The Denominator is technically supposed to be the sum of fx up to that sequence but performing the calculation you gave me would technically give me the same results. If I have 50K rows, my denominator would then be seq/(50000)...Should of thought of that earlier..thank you! |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-06-21 : 17:19:27
|
| Make sure you multiply by 1.0, if you don't you'll get nothing but 0's since and int divided by an int is an int (1/2 = 0, 3/2 = 1, etc)JImEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Using a loop instead of an inner join