Hi
I would appreciate your index compression suggestions for the table structure below

SalesDate (Date)
LocationNo (SmallInt)
TerminalNo (SmallInt)
PriceLevel (SmallInt)
ProductCode (Srting) (Len18)
SalesQty
CostOf Sales
SalesValue
ChgQty (Float)
ChgCostofSales (Float)
ChgValue (Currency)
LastSale
Promo
Waste
ChgPromo
ChgWaste
TaxValue
ChgTaxValue (Currency)

The Primary key is SalesDate,LocationNo,TerminalNo,PriceLevel,ProductCode
Plus all the fileds above with the data type indicated are indexed

the table currently holds approx 4million records

Potentially there could be 13000 (Products) x 55 (Locations) sold each day although this is very unlikely.
The table is part of a system supplied to us and none of the tables have any index compression. The sql's I run are becoming slower and
slower and am wondering if the lack of compression may have some influence on this.

Regards
Malcolm

Malcolm


Index compression is a balancing act between the saving in time reading less data from disk and the additional time required to uncompress the index. Tim will have the definitive answer but my guess is that with the field types mentioned index compression is unlikely to help. It would be more likely that the necessary indices for your sql aren't there, or simply the size of the tables.

Can you run one of the slowing queries in DBSys and post the query plan?

Roy Lambert [Team Elevate]

Malcolm,

<< Potentially there could be 13000 (Products) x 55 (Locations) sold each
day although this is very unlikely. The table is part of a system supplied
to us and none of the tables have any index compression. The sql's I run are
becoming slower and slower and am wondering if the lack of compression may
have some influence on this. >>

Roy is correct - the compression is most likely not the issue, but rather a
lack of the proper indexes for the SQL optimization.  An easy way to spot
such issues is that they incrementally slow down proportionally as the size
of the table(s) involved increase.

If you want to post some of the queries and their query plans, we can take
things further.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Guys,

Mant thanks for the response. Sorry not to have come back before but been involved in other issues. Will come back with some detail as
soon as.

Many Thanks
Malcolm