sql server (alternate): 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'

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

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.

I am testing with SQL 2000 Developer. XEON CPU, fast SATA hard drive and 2GB
of memory.

Thanks

[quoted text, click to view]


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

Richard

See inline

[quoted text, click to view]

This is not proper DDL. In a proper CREATE TABLE statement the column
name comes first, then its data type.

The columns S1, S2, S3, S4 and V1 have very poor names. You haven't
specified what these columns represent. I sure hope your table is
properly normalized.

[quoted text, click to view]

Your table is missing a Primary Key constraint. What is the table's key?
Is it the combination of CustID and SaleDate? Please add a Primary Key.
It will automatically be uniquely indexed.

[quoted text, click to view]

If you really use a literal (as in the example above) and the index
statistics are up to date, then the query will be as fast as possible.

However, if in reality you are using a local variable or parameter (for
example ... WHERE SaleDate > @SomeDate) then SQL-Server might not know
that the index is useful. In that case you could check out the
performance if you add an Index Hint.

Please note that the preferred dateformat is '20050101' or
'2005-01-01T00:00:00', because this is a safe notation. The
interpretation of '1/1/2005' will depend on the server language.

[quoted text, click to view]

There is currently no index on (S3,S4). And even if there was, then it
is still not clear if it could be used (you would have to check that). I
doubt that you will get this query to 0.5 seconds. What does the query
mean anyway? It is a very strange query in the context of a Sales table
with a CustID column. It looks like a reporting query, not a query that
requires a performance of < 0.5 seconds.

Note that it is not safe to divide money data types, because the result
will also be a money data type. First of all, money divided by money
results in a ratio, not another money amount. Second, all precision
beyond the 4th decimal is lost in a money data type, so your ratio will
only have 4 decimals. Casting the S1 and S2 values to decimal should
solve that problem.

[quoted text, click to view]

You could have a look indexed views. They could hurt
Insert/Update/Delete performance, but it may enable you to run this
reporting type queries pretty fast.

On the other hand, have a good look at what you need to achieve, and if
you really need the performance you are asking for. It might mean you
have to redesign your solution, because processing full 9.5 million rows
will never be lightning fast...

Gert-Jan

[quoted text, click to view]