Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What can I do to pre-select something from database?

 
Steve Mutanson
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I find there is a "Oracle" group but I am not using Oracle at all. So I am posting this here. Hope you can help.
I am new to stored procedure and new to Java's "PreparedStatement/CallableStatement". What I have done before is just straightforward java sql "Statatement". So I need your input here ---
In my first page, I let user pick some "product type", then in the 2nd page there is a "Product List" picklist, the content is dependent on what "Product type" user chooses in the 1st page. This involves a simple SQL, now the problem is the database contains SO MUCH data and makes it very slow. Fortunately I know there are only a few choices for "Product Type", so what I want is -- Can I pre-select out the results so it immediately shows me the "Product List" instead of going through the huge database whiel user is waiting for the 2nd page to come out ?
How to do that ?
Many thanks.
 
John Smith
Ranch Hand
Posts: 2937
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SM: This involves a simple SQL, now the problem is the database contains SO MUCH data and makes it very slow.
I have several suggestions:
1. Replace your "simple SQL" with a prepared statement, if it is not already.
2. Making a database query should be a matter of milliseconds, even if it is a large table. If you experience the performance problems, talk to your DBA. Most likely, the database has not been indexed, or the indexes have not been configured properly.
3. Use cache to hold on to the results of the database queries, -- there is no need to make the same db select over and over again.
4. Create a separate database table that maps product to product family, with no other columns. This could dramatically reduce the number of table rows.
5. Alternatively, do not make any database queries at all, -- move the product selection logic out of the database to something else (to java script, to a separate java class, etc)
6. Run your app under a profiler (such as OptimizeIt) to verify that the performance bottleneck is where you think it is. You may be surprised.
[ May 08, 2003: Message edited by: Eugene Kononov ]
 
Cindy Glass
"The Hood"
Sheriff
Posts: 8521
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to the JDBC forum.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic