Ralf Schneider wrote: > > my application reads about 100.000 records from the DB during > startup (it will > be even more in the future!). This takes about 20 seconds. Is > that normal for > such a number of records? For me, it seems a bit to long. > > The executed SQL statement looks like this: > > sql = "SELECT \"cid\", \"name\", \"city\", \"client\" " > "FROM company " > "WHERE \"confidentiality\" <= " + conf + " " > "AND \"name\" LIKE '" + mPattern + "' " > "ORDER BY \"name\" DESC, \"city\" DESC"; > > I created an index for the 4 selected columns. Is there > anything that is time > consuming in this statement? Or is the performance for > 100.000 records OK?
You created an index for the 4 selected columns. Ok, what do you expect this index is useful for? It cannot be used for index-only usage (and no access to the primary table) because other columns have to be checked (see your where clause). And this index is of no real use if columns are given in the sequence of the select list (see below). As was written before, the usage of an index for LIKE depends on the like-value. If a wildcard is in the beginning, the index is of no use. If the wildcard is NOT in the beginning, an index with the column in question somewhere in the middle of the index is of no real use. To restrict the access to a number of rows, this column should be the first one in the index. Otherwise the whole index had to be checked to find those primary keys which have to be read and checked because they fulfill this single condition. This is done VERY rarely. If one uses ORDER BY (order by NOT according ascending primary key or an index) then the whole resultset has to be build. Depending on its size, this will last a little bit longer. Perhaps an index according to your order by clause (incl. DESC) may help to avoid the physical preparing of the resultset (and will help to avoid some I/O). Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
