It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Slow Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Slow Database" Watch "Slow Database" New topic

Slow Database

Anselm Paulinus
Ranch Hand

Joined: Sep 05, 2003
Posts: 390
An interview question I encountered recently, the interviewer wanted to know what I could do to enhance the speed of a slow database. I mentioned the use of prepared statement and he said that was from the JDBC angle; he wanted answer from the angle of the database itself. Does anyone know what could be done to enhance the speed of a slow database?
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1141

I assume that "slow database" implies that it takes a long time for the database to return the results of queries. Usually this is due to badly written queries. By modifying the SQL of a particular query, you can sometimes change its execution time from hours to minutes.
The other way to "enhance the speed of a slow database" is to modify the database -- by defining indexes or "tweaking" the database's configuration.
In any case, almost always, the options available to you are very dependent on the DBMS you are using. For many of the leading DBMS's, there have been several books written that entirely deal only with one aspect of database tuning. For example, I have a book with over 1,000 pages that deals only with tuning SQL for an Oracle database.
But what every book will tell you is that there is no "silver bullet" -- no single "thing" you can do that is 100% guaranteed to increase the speed of your database. People who use the Oracle DBMS (jokingly) refer to the following (undocumented ;- ) configuration parameter (and setting):

Good Luck,
Anselm Paulinus
Ranch Hand

Joined: Sep 05, 2003
Posts: 390
Thanks Avi;
I wanted to know if there was one thing that could be done to enhance the speed of a database.
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

For a test-question I would expect the 'index'-answer to be the wanted answer.
But if the indexes are in best order?
Faster processor/ harddrives could help.
If the teacher didn't mention 'on the database' the network could be a bottleneck.
ER-design could be bad.
The server could be set up bad, i.e.: doing to much other jobs.
Often the programmer use bad habbits to communicate with the database (new connection for every query).
Hey - there are database-forums out there too...
I agree. Here's the link:
subject: Slow Database
It's not a secret anymore!