Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 2 of 2 total |
DBISAM Optimizer: Can we tell it what index to use? |
Wed, Nov 8 2006 9:07 PM | Permanent Link |
Sam Jones | Hello!
We are doing some performance analysis of our app with DBISAM 4.24. Issue: DBISAM appears to choose its index based on index creation sequence, not on the smallest index that will do the job. In our work, we are looking at the 'cost' value. Our test bed is a simple 3000 row table with several indexes. However in the wild this table can have 200k or more rows, and be over a network (on a server share). So performance really matters. Here is the query we are using to investigate: SELECT OurTable.recID, OurTable.recTYPE FROM OurTable WHERE (( shipDate >= '2006-10-08' and carrier = 0 )) We get the absolute lowest cost for this query with the table has these two indexes: Create Index ix_shipment2 ON shipment (carrier); Create Index ix_shipment3 ON shipment (shipdate); But we have another index, and when it is present, the cost goes WAY up. If the index creation order is this: CREATE INDEX ix_Shipment_MarkAsUploaded ON Shipment (Carrier, Account, dtClosed, UPSServiceType, BOLD_ID); Create Index ix_shipment2 ON shipment (carrier); Create Index ix_shipment3 ON shipment (shipdate); The query cost goes WAY up, because DBISAM is using the first index as the 'carrier' index. This table has 22 indexes on it (19 of which are single column indexes). Must we follow the rule of all single column indexes are created first? (seems a bit.... interesting....) Thank you! |
Thu, Nov 9 2006 7:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Sam,
<< Issue: DBISAM appears to choose its index based on index creation sequence, not on the smallest index that will do the job. >> Just make sure that the composite indexes have at least duplicate key compression, and that should take care of most, if not all, of the actual differences in the actual I/O (provided that the composite keys are not completely unique, which is doubtful). The optimizer's costs does not take into account index key compression because it can't - the index key compression is a context-sensitive calculation and it cannot be reflected in the statistics that the optimizer uses. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |