What's a complete list of issues that affect performance arounfd databases? If possible re-order them by importance. 1. connection pooling 2. locking performance a: blocking b: hot spots There are options to balance these for better performance. Does JDBC give more tunable options or are there only hard and fast rules ? Are there new network related tunable options ? A reference would be appreciated. regards regards
Hi Thomas, The number one and two determinants for database performance would still be: 1. query formulation (as soon as you do anything more complicated than a straightforward select), 2. database tuning, including creation and evaluation of indexes. Only when these two points are in order, it makes sense to look at the more arcane issues like locking (which of course is a terrible performance killer if you run into it), and java/JDBC issues. An excellent introduction to database performance tuning has recently been written by Shasha and Bonnet: 'Database Tuning', ISBN 1-55860-753-6. Hope this helps, Rudy.
Thanks Rudy. That was useful. Database Tuning. I found these references also:
Introduction: any textbook on databases will do, e.g., Saake's book (which is in German), first volume. Database Architectures: Saake's book, second volume (which is in German), Chapters 2 and (partly) 3 Schema and Index Tuning: Oracle online documentation; http://www.comp.nus.edu.sg/~cs3223/3-AccessMethods.pdf; book by Shasha and Bonnet (see below); Saake's book, second volume, Chapter 4; http://www.acm.org/sigmod/vldb/conf/2000/P285.pdf Query Tuning: Saake's book, second volume, Chapters 6 and 7; book by Shasha and Bonnet; Oracle online documentation; K. Ono and GM Lohmann. "Extensible Enumeration of Feasible Joins for Relational Query Optimization". In Proc. of the 16th VLDB Conf., August 1990. Lock Tuning: Bernstein, P. A., Hadzilacos, V., Goodman, N.: Concurrency Control and Recovery in Database Systems, Addison Wesley, Reading,1987. You can download it for free at: http://research.microsoft.com/pubs/ccontrol/ Chapters 1 to 3; book by Shasha and Bonnet Self-Tuning Histograms: http://citeseer.nj.nec.com/bruno01stholes.html
They seem pretty current. regards [ October 07, 2003: Message edited by: HS Thomas ]
Re: the latter half of my question this is pertinent: "From a slightly different perspective, database technology tends to be depicted in a somewhat idealistic fashion. For instance, "Give a declarative query to a database and it will always find a good plan." is not exactly true, and this is unlikely to change in the foreseeable future. Consequently, database developers must be familiar with respective tuning features offered by DBMSs. Furthermore, we observe that DBMSs are becoming more and more workload-adaptive and feature self-tuning mechanisms. I.e., a good setting of those tuning knobs is found without help of the administrator/user. The second half of the course will cover these exciting new facets of database technology. Development of such technology is not yet a 'closed book'. Instead, self-configuration and self-tuning techniques and adaptation mechanisms for databases are a current topic of research that is both important and interesting." Having said this, the isn't only aimed at a 'practical audience', but also at people interested in shaping and designing new information systems.
regards [ October 07, 2003: Message edited by: HS Thomas ]