Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Can we improve the performance of a tool by eliminating joins?

We have an SQL statement that selects data from four tables by performing a join operation. The number of rows in each table is in the millions. The query is being fired from one tool written in C++ . Since the query is taking a lot of time to execute, the performance of the tool is also getting slowed down. We think the solution is to eliminate the joins and query the individual tables and load in memory and let the tool operate on it.

We have an SQL statement that selects data from four tables by performing a join operation. The number of rows in each table is in the millions. The query is being fired from one tool written in C++ . Since the query is taking a lot of time to execute, the performance of the tool is also getting slowed down. We think the solution is to eliminate the joins and query the individual tables and load in memory and let the tool operate on it. Will this serve to enhance the performance of the tool or is there another solution?

No, I do not recommend performing your join outside of the database! Oracle is more than capable of performing such a join in an efficient fashion. Let Oracle do what it does best, rather than trying to build your own solution.

There are many options and techniques available to help you solve this problem. Have you examined the optimizer plan for the query in question? Are the tables indexed on the join columns, and any other columns that are referenced in the query predicate? Are table and index statistics up to date? Have you tried using optimizer hints to force different join methods and observing which one performs best?

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close