1

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.

Thanks!!

Improve this question
2
  • 1
    not sure the "number of calls" is the timeout reason, but what those queries are doing. How dynamic or static are these reports? Materialized views refreshed on some schedule (say once/day) would solve your problem if this would meet your refresh rate needs. This would reduce your queries to select * from matview where key='blah' and solve your timeout issues. Commented Jun 23, 2011 at 11:44
  • The queries are quite dynamic, it fetches data from about 12 tables and data stored in the tables is modified frequently. Commented Jun 24, 2011 at 4:24

2 Answers 2

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your solution. I would some directions in the regard, like sending list of data as input to a stored proc and retrieving the set of combined response in the result-set. Examples, samples or links would help a lot. Thanks!!
2

there are a few ways of combining results from multiple queries.

  1. UNION ALL -- lets you literally combine results between query1 UNION ALL query2
  2. Make 1 more general query. -- this is the best answer if it can be done.
  3. Join Sub queries and print the data horizontally if you can join them. select a., b. from (querya) a join (queryb) b on (id)
  4. There are probably other ways as well. Such as a stored procedure etc.
Improve this answer

1 Comment

Thanks for your response - The count of reports is dynamic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.