Home » SQL & PL/SQL » SQL & PL/SQL » Designing Database with reporting tables (Oracle 9i)
Designing Database with reporting tables [message #432222] Mon, 23 November 2009 01:27 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to design a database for an online application with many concurrent users. There would be many reports fetching data from transaction tables. In my previous experience, I found that when transaction tables with loads of data and loads of INSERTS/UPDATE operations, are accessed for heavy reporting...it makes the application perform very slow.

Before desiging , I thought to sort this thing first.

I thought of creating seperate tables for reporting. This would consume performance to flush data in reporting tables but reporting would be faster. The reports will be run any time and updated data would be expected at any point of time.

Options with my understanding are :-
1).Ceate trigger that will insert/update data in reporting tables when inserting/updating the transaction tables.

2).Ceate materialized views that will refresh the data in reporting tables as soon as transaction tables are updated.

Please suggest me if there are other better options or which one of the above would be better in performance.

Please advice.

Regards,
Mahi

[Updated on: Mon, 23 November 2009 01:34]

Report message to a moderator

Re: Designing Database with reporting tables [message #432226 is a reply to message #432222] Mon, 23 November 2009 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I found that when transaction tables with loads of data and loads of INSERTS/UPDATE operations, are accessed for heavy reporting...it makes the application perform very slow.

Not with Oracle.

3) create mview and refresh them only at report time.

Regards
Michel
Re: Designing Database with reporting tables [message #432230 is a reply to message #432226] Mon, 23 November 2009 02:29 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Michel,
Thanks for your suggestion. I have not worked on Materialized views ...just read about it.. I had one question---

Will (refreshing materialised views + Selecting data from materialised views ) will be faster than selecting data from transaction tables..... don't the report will come slower as refreshing the MV will take some time? I am not very familiar to MVs so asked this question.

Regards,
Mahi
Re: Designing Database with reporting tables [message #432231 is a reply to message #432230] Mon, 23 November 2009 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Will (refreshing materialised views + Selecting data from materialised views ) will be faster than selecting data from transaction tables.....

Ir depends

Quote:
don't the report will come slower as refreshing the MV will take some time?

Sure!

Regards
Michel
Re: Designing Database with reporting tables [message #432233 is a reply to message #432231] Mon, 23 November 2009 02:49 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Michel,
Yours answers are correct but I'm now confused as which would be the best way while designing database....

Please let me know if you have some other suggestion as the performance is of prime concern in this application as it would be online. So both DML as well as reporting should go fast hand in hand....
Re: Designing Database with reporting tables [message #432236 is a reply to message #432233] Mon, 23 November 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't say anything for your case with the element you gave.
The best is to make some performances tests on the different designs you imagine.

Regards
Michel
Re: Designing Database with reporting tables [message #432259 is a reply to message #432233] Mon, 23 November 2009 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
One solution that I've seen used to great effect is this.

Create a standby database based on your live database. Assuming you run your reports off overnight, then every evening you recover the standby database to match the live system,

You then open the standby database as read only, run the reports, and when you've finished, return the database to being a standby.

This lets the live db run it's overnight batch jobs without the IO load caused by the reports.
Re: Designing Database with reporting tables [message #432260 is a reply to message #432222] Mon, 23 November 2009 05:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I found that when transaction tables with loads of data and loads of INSERTS/UPDATE operations, are accessed for heavy reporting...it makes the application perform very slow.


In some databases, updates and inserts can block selects, or lead to inaccurate results from selects.

This is not the case with Oracle - Oracle guarantees that the results that you get from a query will show all of the committed changes present at the time that the query was run, and none of the changes committed afterwards, and your query will not wait on any other changes being committed.

That being said, if you are trying to do a lot of inserts/updates to tables, and are also trying to read a lot of data from the tables at the same time, you may well hit the i/o limits of you system, and that will cause you to slow down.

There's not a great deal you can do about that.
Materialized views can help, as you are just reading data from one single source, rather than from many tables.
Query tuning can help reduce the I/O caused by your queries

But, at the end of the day running lots of reports at the same time as your overnight batch jobs will cause things to run more slowly.
Re: Designing Database with reporting tables [message #432275 is a reply to message #432260] Mon, 23 November 2009 06:26 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi JRowBottom,
Thanks for looking into this.... We have to run reports at any point of time at day to see the status of many things.

So overnight batch jobs won't help. Please suggest .....
Re: Designing Database with reporting tables [message #432287 is a reply to message #432275] Mon, 23 November 2009 07:51 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It depends a lot on your system

If your reports use simple queries, then it should be possible to create On Commit Refresh materialized views that would provide a real time set of data to query on - your reports would run faster, but everything else would run slightly slower.

Otherwise, I think you're stuck with the performance hit of running the queries.
Previous Topic: drop table
Next Topic: EXECUTE IMMEDIATE 'Create table'
Goto Forum:
  


Current Time: Wed Dec 07 18:26:02 CST 2016

Total time taken to generate the page: 0.16449 seconds