wood burning stoves
The moose likes JDBC and Relational Databases and the fly likes Performance issues Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of The Software Craftsman this week in the Agile forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Performance issues" Watch "Performance issues" New topic

Performance issues

HS Thomas
Ranch Hand

Joined: May 15, 2002
Posts: 3404
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.
Rudy Dakota
Ranch Hand

Joined: Jul 27, 2002
Posts: 54
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,
HS Thomas
Ranch Hand

Joined: May 15, 2002
Posts: 3404
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;
book by Shasha and Bonnet (see below);
Saake's book, second volume, Chapter 4;
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.
[ October 07, 2003: Message edited by: HS Thomas ]
HS Thomas
Ranch Hand

Joined: May 15, 2002
Posts: 3404
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.

[ October 07, 2003: Message edited by: HS Thomas ]
Have you tried LearnNowOnline? http://www.learnnowonline.com/
subject: Performance issues