Florian K�rner wrote : > 1. I access a database in a c++-program via ODBC. The program > performs an > action that uses many SELECTs that return many rows. The > action takes about > 28 secs. Then I add an index to the database that contains a > foreign key > that is used in a join in the SELECTs, and the same action > takes only about > 1.5 secs. Then I disable that index, and the same action > takes about 3 secs. > How can that be? I repeated this, to be sure it isn't an effect of the > cache??
The SAP DB optimizer uses some statistic-info about the size of the tables. Depending on this known size (may differ from the current size if update statistics was not done for a long time or many inserts/deletes were done on at least one of those tables) the optimizer decides for a strategy and the sequence in which those tables are handled. If an index is created, one has to hurry through the whole primary data of the corresponding table. And the statistic info is then stored in memory and can be used for the optimizer. Even after dropping the index, this info remains as it remains after update statistics. And other statistic info may result in other strategy and sequence of tables handled. And then the time for selecting may differ. The difference between 1,5 and 3 seconds is because of the missing index, the difference between 3 and 28 because of old/wrong statistic info. See output of explain for all of your selects and you will the differences for the used strategy. And do some update statisttics from time to time. http://www.sapdb.org/htmhelp/3b/86f067458411d3a98200a0c9449261/frameset.htm Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
