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?
Anselm, 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, Avi.
Joined: Sep 05, 2003
Thanks Avi; I wanted to know if there was one thing that could be done to enhance the speed of a database.
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...