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.
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 ]