[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Optimization Inner join

Posted on 2007-10-15
17
Medium Priority
?
1,102 Views
Last Modified: 2008-01-09
Optimisation of Query

Hi all I have this query below
What is the best way to optimise it, and get it run better
from            BigA
                                                inner join      BigC
                                                on            BigC.m_id=BigA.m_id
                                                right outer join      BigB
                                                on            BigB.m_id=BigA.m_id
                                                where bla bla bla

Thanks in Advance
0
Comment
Question by:ZURINET
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 10

Expert Comment

by:lahousden
ID: 20078104
Make sure you have indexes on column m_id in all three tables.  You might be able to squeez some more performance out of it by tweaking the join order.  What are typical numbers of rows from each of the tables that will be retrieved in a typical execution of the query?

E.g. BigA - 10; BigB - 100000; BigC - 500

Also, the where clause will almost certainly have an aimpact on the performance, so we may be able to help with that if you post it here.
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20078132
from            BigA
                                                left join      BigC
                                                on            BigA.m_id=BigC.m_id
                                                left join      BigB
                                                on            BigA.m_id=BigB.m_id
                                                where bla bla bla
0
 
LVL 10

Expert Comment

by:answer_me
ID: 20078161
Indexes on BigA.m_id, BigB.m_id ,BigC.m_id should solve the problem
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 10

Expert Comment

by:answer_me
ID: 20078169
Also you can go through the execution plan to identify which part of the query is bottleneck.
0
 

Author Comment

by:ZURINET
ID: 20078200
Thanks all for the speedy repyl

Each table have +/- 1000 rows of data
if this help
0
 
LVL 10

Expert Comment

by:lahousden
ID: 20078233
Why does it need optimisation?  If you have indexes in place it should already be returning results in less than a second...
0
 

Author Comment

by:ZURINET
ID: 20078251
Sorry just forget the where condition

from            BigA
                                                inner join      BigC
                                                on            BigC.m_id=BigA.m_id
                                                right outer join      BigB
                                                on            BigB.m_id=BigA.m_id
                                                where
                                                (BigA.m_id <> 7 and
                                                BigA.m_id <> 8) and
                                                BigB.m_id = 0
0
 
LVL 9

Expert Comment

by:konektor
ID: 20078257
joins itself are not problem in queries. have you checked execution plan of query?
fo you join BigA with BigC and outer with BigB, people may expect that oracle firstly goes to BigA, use nested loops to join with BigC going on index BigC.m_id and than to BigB on index BigB.m_id
but if your "bla bla ..." contain conditions on BigC and/or BigC and not BigA, execution plan might be different than you expect.
if you use 9i or lower versions of oracle, comute statistics periodically on tables (10g does it automaticaly) and rebuild indexes periodically. first of all ensure that joined columns are indexed.
0
 
LVL 10

Expert Comment

by:lahousden
ID: 20078272
Wait... is this Oracle or SQL Server (or some other RDBMS engine altogether...)?
0
 

Author Comment

by:ZURINET
ID: 20078278
This query is just a sub query inside a query..
I trying to find out if there other ways of achieving better result
like subquery instead of join.
0
 

Author Comment

by:ZURINET
ID: 20078295
SQLServer 2000
0
 
LVL 9

Expert Comment

by:konektor
ID: 20078301
i deduce, that m_id columns have low cardinality ... build bitmap indexes instead of default b-tree indexes
0
 
LVL 10

Accepted Solution

by:
lahousden earned 1000 total points
ID: 20078325
Try this:

from BigB
left outer join BigC on BigC.m_id = BigB.m_id
left outer join BigA on BigA.m_id = BigB.m_id
where BigB.m_id = 0
and BigA.m_id not in (7, 8)
0
 
LVL 10

Expert Comment

by:answer_me
ID: 20078331
How long it takes if following is used

from            BigA
                                                inner join      BigC
                                                on            BigC.m_id=BigA.m_id
                                                right outer join      BigB
                                                on            BigB.m_id=BigA.m_id
                                                where
                                                BigB.m_id = 0
0
 

Author Comment

by:ZURINET
ID: 20078348
I believe there may be a better way of writing the Where condition
where
(BigA.m_id <> 7 and
                                                BigA.m_id <> 8) and
                                                BigB.m_id = 0

Don't you guys think so?
0
 
LVL 10

Expert Comment

by:lahousden
ID: 20078385
BigB.m_id = 0
 is your biggest asset because it explicitly identifies the BigB row.  If you can couch the rest of your join to drive from that partial result set then that is the way to build the smallest intermediate result sets with the lowest overall elapsed time...

BigA.m_id <> 7
and  BigA.m_id <> 8

are always going to be expensive because they will force full scans - however, if you can mitigate things by having them lower down the pecking order then you can minimise their impact...
0
 

Author Comment

by:ZURINET
ID: 20078500
Hi lahousden

You got a point..
I can make some performance gain from you advice.

Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question