Executing multiple queries in a single DB Call - SQL
I'm creating pdf reports with Data retrieved from Database (Oracle). For each report I'm making a DB Call. I want to optimize the call for multiple reports ( can have max of 500 reports). In current scenario, I am making 500 DB calls and this results in timeout of the Server. I'm looking for solutions and answers. 1. Can I pass a list of data as input to a query ? (The query required 2 inputs.) 2. The entire set of data retrieval should happen in 1 DB Call not 500 separate calls. 3. The response should be accumulated result of 500 inputs. Please suggest ways to solve or directions to the solve the issue ?
It is a Java based system. The DB call is from a Web App. DB : Oracle.
If you want to get the data for an arbitrary number of "reports" in a single database call, then I would imagine you need to be calling a stored procedure that returns a very large nugget of XML or JSON text that you can then parse and display in your application. Oracle has built-in functions for constructing XML, and JSON is pretty easy to structure yourself (though I believe a 3rd party PL/SQL JSON package may be available).
there are a few ways of combining results from multiple queries.
- UNION ALL -- lets you literally combine results between query1 UNION ALL query2
- Make 1 more general query. -- this is the best answer if it can be done.
- Join Sub queries and print the data horizontally if you can join them. select a., b. from (querya) a join (queryb) b on (id)
- There are probably other ways as well. Such as a stored procedure etc.