| Author |
 Topic  |
|
|
kotonikak
Yak Posting Veteran
55 Posts |
 Posted - 06/21/2012 : 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 Denominator
1 0.01 0.01
2 0.01 0.02
3 0.01 0.03
4 0.01 0.04
5 0.01 0.05
My 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 06/21/2012 : 15:32:35
|
Is the denominator just seq*1.0/100?
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
kotonikak
Yak Posting Veteran
55 Posts |
Posted - 06/21/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 06/21/2012 : 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)
JIm
Everyday I learn something that somebody else already knew |
|
|
| |
Topic |
|
Using a loop instead of an inner join
