Forums Register Login

Single Shot executeQuery for more than one Different SQL statements

+Pie Number of slices to send: Send
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.
2
+Pie Number of slices to send: Send
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.
1
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.
sunglasses are a type of coolness prosthetic. Check out the sunglasses on this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 707 times.
Similar Threads
Whats the difference in Statement and PreperedStatement ?
Connection and PreparedStatement Relation...??
Definitive Best practices with JDBC and oracle
Get Data from access database according to today date using java
Statement, Prepared Statement, and CallableStatement
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 23:30:16.