File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Optimizing a database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Optimizing a database" Watch "Optimizing a database" New topic
Author

Optimizing a database

TylerS Alexander
Greenhorn

Joined: Mar 27, 2006
Posts: 2
Hi fellas,

I have been having trouble finding quality information on the topic at hand, and I hope this thread will help everyone who reads and responds to it.

I have a database which contains a fairly large set of financial data and initially I structured the database with organization principles in mind. However, I am slowly realizing that the current structure is inefficient for the task at hand and I'd like to restructure the database with optimal data extraction as the primary goal, since the data extraction process is currently the bottleneck.

FYI, I'm using MySQL & JDBC.

Here are three general decisions one might consider when designing a database.

1. Single table with x columns VS. n tables with x/n columns.

Comments:
-Extracting data from a single table seems much more efficient than extracting data from multiple tables since mult. tables require join operations. It has been my experience that joining tables which have a considerable row count is extremely computationally intensive on MySQL.

-However, 800 columns in a single table seems a bit much and at some point I'd think you'd want to disperse the column count into multiple tables.

Questions: Is it more practical and efficient to place all data into one table with x columns or to place all data into multiple tables with x/n columns?


2. Single table with x rows VS. n identical (columns) tables with x/n rows.


Comments:
-Similar to above, if you wish to extract all rows, placing as many rows as possible (row limit) in a single table seems more efficient since it would require fewer queries (fewer connection requests and other preparations).

-Another limit seems to be how much memory can you allocated to the extracted data. Regardless, perhaps it's best not to strain MySQL so much.

-For example: Is it adequate to query a table with 1 million rows in a single table? Probably not.

Question: Is it best to store as many rows into one table as possible if the plan is to query data from all rows, or is it best to disperse the rows into multiple tables and query each of these tables separately?

3. Altering MySQL configurations and optimizing JDBC code.

Comments:
-Although I have spent little time researching how to modify MySQL configurations to perform certain operations more efficiently, it seems doing so wouldn't produce a drastic speed performance. Am I wrong here and/or are there any good reference anyone would recommend?

-Similarly should and could you optimize you JDBC commands to more efficiently perform extraction operations on a mass set of data? Obviously, we can use prepared statements and things of that nature to speed the process up, but are there certain JDBC commands you can give to MySQL to perform a query in a more efficient manner? Are there references anyone could point me to?

*****

It would be great if there are exact answers to my questions but I doubt this is the case. If anyone could give me some type of guidance however, I'd be much obliged. For example, someone might say, "Generally, in my experience it's best to do this...". Also, any references to related topics would be extremely helpful.

Best Regards,

-Tyler
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

Is it more practical and efficient to place all data into one table with x columns or to place all data into multiple tables with x/n columns?

I don't think this is going to affect performance much. Start with what is logical.

Question: Is it best to store as many rows into one table as possible if the plan is to query data from all rows, or is it best to disperse the rows into multiple tables and query each of these tables separately?

Somce commercial databases, like Oracle, allow you to introduce parallelism into a big table. You can partition the table by some logical grouping (like column value) so Oracle can query different parts of the table at once.

Similarly should and could you optimize you JDBC commands to more efficiently perform extraction operations on a mass set of data?

Yes. The biggest things are to make sure you aren't returning any unnecessary data (don't do select *) and that the query is efficient. You can tune the query for your database using the explain tool and looking for tablescans.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
TylerS Alexander
Greenhorn

Joined: Mar 27, 2006
Posts: 2
Great,
I appreciate the help Jeanne. You comments on Oracle and the JDBC commmands were fantastic.

-Tyler
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Optimizing a database