Optimization challenge:'Query' Requirements for J2EE application


EJB programming & troubleshooting: Optimization challenge:'Query' Requirements for J2EE application

  1. Hi,


        I have been breaking my head not knowing if I am having issues in the 'requirements' or in the 'implementation'. If any of you that might possibly have some experience in this area can please respond to this, I will greatly appreciate.

        In our project Oracle DB, we use about 7 core tables in which about 3 million records are loaded. Overall there are about 75 columns in these 7 tables.

        Our requirement says: The actor shall be allowed to query records based on any of the 25 listed attributes. (These 25 attributes are scattered in those 7 tables)
      * attr1, attr2, ... attr25

       Now, the number of possible 'search criteria' combinations using these 25 attributes are large. Does this requirement look realistic? Or should we revisit the requirement and consider for 'Operation Profile Index' or 'Most commonly used query patterns' so that queries that are most often used will be optimized best using reasonable number of indexes? Or is there any other implementation strategies(use of caching, should we use our own application caching or use DB Caching itself?, may be use of J2EE features, etc).
       Or If I am totally thinking way out, please suggest some techniques, Thanks.

    Best Regards,
  2. It's late you know... but maybe using some database views for the common queries (common denominator). So the joins are not stablished at run-time?

  3. I think it is fairly clear that any problem you are going to have is in the DB. Get yourself an expensive DB expert, who knows how to do some optimizing. I think optimizing at the J2EE level should come much later in the game.