Posted by Erland Sommarskog on 07/30/05 01:17

Rich (no@spam.invalid) writes:
> CREATE TABLE Sales1
> (
> varchar (10) CustID,
> varchar (10) TransID,
> datetime SaleDate,
> money S1,
> money S2,
> money S3,
> money S4,
> numeric V1
> )
>
> CREATE NONCLUSTERED INDEX Sales1_CustID ON Sales1 (CustID)
>
> CREATE NONCLUSTERED INDEX Sales1_SaleDate ON Sales1 (SaleDate)
>
> "money" is just the right size for my fields. This table has 9,500,000
> records.
>
> Although I need to do this select in less than a half second, it takes 1
> full minute:
> SELECT MAX(S1) FROM Sales1 WHERE SaleDate > '1/1/2005'

Add S1 to the non-clustered index. Or make the index on SaleDate clustered.

> And I need to do this select in less than a half second, but it takes 3
> minutes:
> SELECT AVG(S1 / S2) FROM Sales1 WHERE S3 > S4

That's a tough one. With that requirement for response time, it seems
that you would need to add a computed column with S3-S4 and then index
that column, and add S1 and S2 to that index. And rewrite the query as
"WHERE S3_minus_S4 > 0". Alternatively define an indexed view on this
condition.

Under the current circumstances, SQL Server will have to scan the
entire table.

> Am I supposed to create a new field with these values pre-calculated? I
> hope not, because I have several other formulas - up to 500 different
> types of selects which are all similar.

Ouch. Well, it seems that you need to do a more thorough review or
the requiremens and make a new design.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]