• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Single Shot executeQuery for more than one Different SQL statements

 
Jack Numen
Ranch Hand
Posts: 42
Hibernate Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How can i reduce the number of calls to the Database when i am having nearly 10-20 different sql SELECT statements to execute.
Do we have a solution where we can hit the DB only once and fetch all the ResultSets for all these SELECT statements instead of writing PreparedStatements individually and executing them everytime.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, you seem to be assuming that the results of all these queries can be processed together i.e. each record will have the same structure. If this is the case, then I'm puzzled why you need "10-20 different" queries to fetch them. Can you write a single query to do this e.g. using things like SQL UNION to combine different subsets of data into one main query, or using a SQL WITH clause (NB this is only available on some databases e.g., Oracle, PostgreSQL).

Alternatively, if you can't build a single query but you still want to do it all in one call to the database, you may need to look at using a stored procedure.

But I would start by analysing the data you are querying, the results you expect to get and what you want to do with the results, because it sounds strange that you need 10-20 different queries to fetch a single set of records that will all be processed together.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I second the stored procedure. I question the goal of saving on trips to the database server though. It might not be as expensive as you think.
 
Jack Numen
Ranch Hand
Posts: 42
Hibernate Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks ,

I understand that we can use either a UNION or a stored procedure,also saving the trips to the DB might not be expensive.
I have also got the idea of using a multi-dimensional view(CUBE- thereotically speaking) to make my data retrival faster.
My record structure varies for all the queries.Its something more like different tables

(USER_DETAILS,USER_LOCATION,USER_PASSPORT_DETAILS,USER_BANK_DETAILS,BANK_LOC_DETAILS,BANK_RATING,
USER_DEPENDANT_DETAILS,USER_INSURANCE_DETAILS,USER_ESTATE_PROPERTIES and so on)

Each of these details needs to be shown in different tabs fetching all at once.
After Query tuning i found no major difference in performance.
That is the reason why i thought of querying all of them either in a Single shot OR Creating a VIEW(for faster retrival of each table individually).
I need to find the best way to increase performance in this case.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not sure how a cube is going to help you.

It sounds like you're populating a bunch of separate screen tabs with different sets of data anyway, so is the data all for one user? If so, then your queries to get the various sets of info for the current user ought to be pretty quick, provided you have an index on your User ID in the relevant tables. In any case, you should probably aim to just fetch the data you need when you actually need it i.e. don't fetch all the data for every user because you probably won't need most of it.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jack,
The best way to improve the performance is to see where the bottleneck is. Is it a slow query? Is it the network transfer of data? Is it the time for database roundtrips.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic