Re: My design - where did I go wrong?
- From: Gert-Jan Strik <sorry@xxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 29 Jul 2005 21:25:08 +0200
See inline
Rich wrote:
>
> CREATE TABLE Sales1
> (
> varchar (10) CustID,
> varchar (10) TransID,
> datetime SaleDate,
> money S1,
> money S2,
> money S3,
> money S4,
> numeric V1
> )
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.
> CREATE NONCLUSTERED INDEX Sales1_CustID ON Sales1 (CustID)
>
> CREATE NONCLUSTERED INDEX Sales1_SaleDate ON Sales1 (SaleDate)
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.
> "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'
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.
> 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
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.
> 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.
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
> I am testing with SQL 2000 Developer. XEON CPU, fast SATA hard drive and 2GB
> of memory.
>
> Thanks
.
- References:
- My design - where did I go wrong?
- From: Rich
- My design - where did I go wrong?
- Prev by Date: My design - where did I go wrong?
- Next by Date: All Roads Lead to Rome but which One is Most Desirable?
- Previous by thread: My design - where did I go wrong?
- Next by thread: Re: My design - where did I go wrong?
- Index(es):
Relevant Pages
|
