Re: Question About Productivity & Speed

->From: snd@inwest.donetsk.ua ->Subject: Question About Productivity & Speed ->Date: Thu, 19 May 94 13:49:31 GMT ->Reply-To: snd@inwest.donetsk.ua ->Organization: unknown -> -> Hello, Colleagues! -> -> Perhaps it's sound like newbie ( I'm ) questions, but ->problem I have: -> (1) Can anybody explain functional dependence of querie's ->processing speed from database tables quantity ( if exist ) and ->critical points of this function? -> (2) How about different versions/releases/engines? -> Platform is Intel-compatible ( i386+ ). -> -> Thanks in advance! -> -> Serge. ->--- ->| Serguey N. DMITRENKO, programmer Phone: (0622) 35-77-45, 92-45-20 | ->| JV "INTER-WEST" Ltd. E-mail: snd@inwest.donetsk.ua | ->| 4 Shevchenko blvd, Donetsk-50, 340050 UKRAINE | ->+____________________________________________________________________+ Hello, Sergei, (1) The following guidelines are more-or-less independent of version (or brand) of database: a. Search time in one table without using an index is linear with size of table (N). b. Search time in one table using an index is related to log(N). c. Time to join two tables, without using indexes, is related to N*N. d. Time to join two tables, using indexes on both join columns, varies between 2*log(N) and (log(N))*(log(N)), depending in part on the data and in part on how "smart" the indexing is. e. Time to join 3, 4, etc. tables follows expected extrapolation from c and d above. Based on the above, your best tuning strategy is the wise use of indexes. There are also several trade-offs to consider: f. It takes time to maintain an index (on insert/update/delete) and to read the index vs. the data (on select). For small tables this might outweigh any gains from using the index. Informix has said that the break-even point is around 200 rows, so small tables such as code lists are better off without indexes. g. Index page size is a trade-off. Large pages reduce the number of nodes in the index, reducing I/O time. Searching within an index page is normally linear, so large index pages increase the linearity of the search, partially offsetting the log-nature of the index. h. Data page size is also a trade-off, but of many more factors. It is much more version and brand dependent, so few generalities are useful. (2) In general, newer versions/releases have better optimizers, as you would expect. Occasionally a new optimizer strategy might result in worse performance in some cases, until the developers work out the last few bugs. I am not aware of any Informix versions that have optimizers that are sig- nificantly worse than the prior versions. Other users may have horror stories to tell. The OnLine engine maintains more statistics in the system catalogs than the Standard engine (SE) does, and uses more sophisticated optimizing tech- niques based on those statistics. If you UPDATE STATISTICS periodically, then this will contribute to better performance. If you update the data base frequently, but do not update statistics, then you can mislead the optimizer into using less efficient search techniques. The manuals have version/release/engine dependent tuning information. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\