Reputation: 855
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!
Upvotes: 1
Views: 1283
Reputation: 103597
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.
Upvotes: 0
Reputation: 171421
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.
Upvotes: 2
Reputation: 85056
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),...)
Upvotes: 0