Fastest way to perform time average of multiple calculations in SQL?

Question

I have a question about the fastest way to perform a SQL Server query on a table, TheTable, that has the following fields: TimeStamp, Col1, Col2, Col3, Col4
I don't maintain the database, I just can access it. I need to perform 10 calculations that are similar to:

Col2*Col3 + 5       
5*POWER(Col3,7) + 4*POWER(Col2,6) + 3*POWER(Col1,5)       

Then I have to find the AVG and MAX of the calculation results using data from a chosen day (there is 8 months of data in the database so far). Since the data are sampled every 0.1 seconds, 864000 rows go into each calculation. I want to make sure that the query runs as quickly as possible. Is there a better way than this:

SELECT AVG(Col2*Col3 + 5), 
    AVG(5*POWER(Col3,7) + 4*POWER(Col2,6) + 3*POWER(Col1,5)), 
    MAX(Col2*Col3 + 5), 
    MAX(5*POWER(Col3,7) + 4*POWER(Col2,6) + 3*POWER(Col1,5)) 
FROM TheTable 
WHERE TimeStamp >= '2010-08-31 00:00:00:000' 
    AND TimeStamp < '2010-09-01 00:00:00:000'

Thanks!

Answer 1

How about doing these calculations when you insert the data rather than when you select it? Then you will only have to do calcs for a given day on those values.

TableName
---------
 TimeStamp
 Col1
 Col2
 Col3
 Col4
 Calc1
 Calc2
 Calc3

and insert like so:

INSERT INTO TableName (...)
VALUES
(...,AVG(@Col2Val*@Col3Val + 5),...)

Answer 2

You could create those as computed (calculated) columns, and set Is Persisted to true. That will persist the calculated value to disk on insert, and make subsequent queries against those values very quick.

Alternately, if you cannot modify the table schema, you could create an Indexed View that calculates the values for you.

Answer 3

your only bet is to calculate the values ahead of time, either Computed Columns or persisted columns in a view, see here Improving Performance with SQL Server 2005 Indexed Views. If you are unable to alter the database you could pull the data out of that database into your own database. Just compute the columns as you insert it into your own database. Then run your queries off your own database.