I want to move to one of my earlier questions in this thread regarding mixing indexed and non-indexed dictionary items, and if unidata is able to use the indexed items at all in this circumstance:
Simple test: One fairly big file: Items - 473,000 Item size - 695 bytes on average Indexed dictionary items - PRINT.DATE 1. Selection using an indexed dictionary item - repeated three times, average score: SELECT file WITH PRINT.DATE GT "01/04/2008" 34,993 records selected 190ms 2. Selection using NO-INDEX: (this very slow, I killed my test program after one pass) SELECT file NO.INDEX WITH PRINT.DATE GT "01/04/2008" 55174 records selected 234 seconds Selection using indexed and non-indexed dict item, ANDed together - this should be a simple optimisation case: SELECT file WITH PRINT.DATE GT "01/04/2008" AND WITH ADDR LIKE "'A'0X" 109 records selected 945ms 3. Selection using indexed and non-indexed dict item, ORed together - there would not be much point using the index here as every record needs to be read in anyway: SELECT file WITH PRINT.DATE GT "01/04/2008" OR WITH ADDR LIKE "'A'0X" 56752 records selected 146 seconds This is interesting! It proves that on unidata 7.1 at least the database engine is able to optimise queries that are built of indexed and non-indexed dictionary items. It means that there isn't much point doing an initial selection based on indexed dictionary items and then further reductions in code, where that is done simply to avoid a mix of the two. It also means that indexing a subset of frequently used dictionary items *is* worthwhile, even if the majority of select commands performed day-to-day use additional dictionary items - so long as they are AND'd together. Other observations - the file is large but clearly getting cached by the OS as it is repeatedly scanned, which is why the last set of selects is faster than the no-index select. Also, my indexes look to be out of date, as the non-indexed and indexed selects return different numbers. Ed ------------------------------------------------------------------------------------------- Please remember to recycle wherever possible. Reduce, reuse, recycle, think do you need to print this e-mail? ------------------------------------------------------------------------------------------- This e-mail and any attachment(s), is confidential and may be legally privileged. It is intended solely for the addressee. If you are not the addressee, dissemination, copying or use of this e-mail or any of its content is prohibited and may be unlawful. If you are not the intended recipient please inform the sender immediately and destroy the e-mail, any attachment(s) and any copies. All liability for viruses is excluded to the fullest extent permitted by law. It is your responsibility to scan or otherwise check this email and any attachment(s). Unless otherwise stated (i) views expressed in this message are those of the individual sender (ii) no contract may be construed by this e-mail. Emails may be monitored and you are taken to consent to this monitoring. Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 01628868 Both companies are registered in England and Wales and each has its registered office at 2 Burston Road, Putney, London, SW15 6AR. ------------------------------------------------------------------------------------------- _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
