Doing quite a bit calculations in a single step, is there a better way?

GokhanVarol 2013-05-12 01:53:04

I have a feed from forex exchange, for each transaction the amount and the timestamp is in a table. I am trying to calculate 3 moving averages (moving average 5 = average of transaction price including current transaction and all transactions in the last 300 seconds prior to this transaction, and there is moving average 20 and 100).
And then 3 ticker bars, each ticker bar represent a minute of transactions, current minute (second 00 till now) previous minute (previous minute 00 till this minute etc)
For them I need to calculate the count of transactions, the average amount, max and min, and the first transaction and last transaction in that minute range. For about 29K records in my I7 desktop this is taking about 20+ seconds. I have a row_number on descending order which is causing a sort but I tried avoiding it FIRST_VALUE or LAST_VALUE functions and they perform worst, I also changed them a singleton index seek but that added a nested loops and performed poorly.
Any suggestions to make this query faster would be appreciated.
Thank you

link text

SQLkiwi 2013-05-20 23:01:59
The text of the query is truncated in the queryanalysis file – could you upload it as a separate document please? Have you considered using a UDA for this?
GokhanVarol 2013-05-20 23:10:06
User defined Aggregate, now I got it ๐Ÿ™‚
Even with UDA what would the windows be since it's going backwards from the current row (not sure)
GokhanVarol 2013-05-20 23:07:07
My bad ๐Ÿ™
I attached the query as text file.
What is UDA ?
(I have written this in C# as part of the application, in c# it takes less than half a second as it's doing more things.)
I wanted to find out if I could create a dynamic windows in SQL kind of.

link text