Posted by Rich on 07/30/05 01:40

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96A32A3CEF98Yazorman@127.0.0.1...
> 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


Thanks Erland. I've also just started reading about Real-Time OLAP cubes and
Analysis Services. Would this help me in any way?

Richard

[Back to original message]