Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 4 of 4 total |
Suggested index compression |
Wed, Sep 16 2009 9:50 AM | Permanent Link |
Malcolm | 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 |
Wed, Sep 16 2009 11:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Sep 16 2009 2:47 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Sep 21 2009 3:29 AM | Permanent Link |
Malcolm | 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 |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |