I am having an sql query which reads data from Oracle four tables by performing a join on them. The number of rows in each table is in millions. This query gets fired from a tool which is written in C++. The data retrieved from this query will be used to reconcile in memory data base(separate DataBase).
The performance of tool is getting degraded . Its taking lot of time because of the sheer number of records in each table and also because of the join operation,
We thought that we will remove the join operation and will get all the records in memory and then do the reconcilation.
Please suggest any other ideas or tell us whether we are right in taking the data in memory and then do the reconcilation.
Thanks in advance
You'll get the best performance by doing the correlation in a single SQL statement, rather than transferring huge amounts of data. Doing that effectively relies on having an optimized index for the job.
---
Sheldon Linker
Linker Systems, Inc.
www.linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904
Provided you have sufficient space, what you could try for is :
Create a materialized view which holds the complete SQL which your tool is sending now with update options "ON Commit".
This will ensure that your result is getting updated with every transaction not only when you are running the tool.
Let us call this materialized view as MV1
Then modify your tool's SQL as
select * from MV1
This will behave as if you are querying a single table, which is what you are doing.
What I have suggested above is basically breaking up the work of your tool in 2 parts.
1. Handling over the result creation to Oracle and
2. retrieval to your tool.
Tradeoff is that all the DDL's will have a little bit of time overhead thrust on them, which is normal circumstances is negligible.
Sanjay
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 3  Replies