Performance

pts.
Tags:
Development
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
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

First, can you make sure that the data you’re selecting is properly indexed?

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Ssingh1
    Most of the columns in the tables are properly indexed
    0 pointsBadges:
    report
  • SheldonLinker
    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
    30 pointsBadges:
    report
  • Sanjay79
    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
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: