Geeks Talk

Prepare for your Next Interview




Sql join performance Query

This is a discussion on Sql join performance Query within the Database Design forums, part of the Databases category; I have a sql join 3 tables, like this: select * from tableA a left join tableB b on a.id1 = b.id1 left join tableC c on b.id2 = c....


Go Back   Geeks Talk > Databases > Database Design

Register FAQ Members List Calendar Mark Forums Read
  #1 (permalink)  
Old 07-07-2008
Junior Member
 
Join Date: Jul 2008
Location: Bombay
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
PompanaGouda is on a distinguished road
Sql join performance Query

I have a sql join 3 tables, like this:

select *
from tableA a
left join tableB b
on a.id1 = b.id1
left join tableC c
on b.id2 = c.id2

It works with the correct result, but takes so long time. For example, in each table, there is about 300 rows --> the above query takes ~ 20 seconds.
Does anyone know how to improve in this case?
Reply With Quote
Sponsored Links
  #2 (permalink)  
Old 07-07-2008
Expert Member
 
Join Date: Sep 2007
Posts: 614
Thanks: 22
Thanked 52 Times in 51 Posts
krishnaindia2007 is on a distinguished road
Re: Sql join performance Query

Create proper indexes. It will improve the performance.
Also check execution plan of your statement.
Reply With Quote
  #3 (permalink)  
Old 07-08-2008
Moderator
 
Join Date: Jun 2007
Location: Bangalore,India
Posts: 1,330
Thanks: 7
Thanked 117 Times in 106 Posts
debasisdas will become famous soon enoughdebasisdas will become famous soon enough
Re: Sql join performance Query

try to make the smaller table (as per rows) as your left most table. That might make so difference.
Reply With Quote
  #4 (permalink)  
Old 07-08-2008
Expert Member
 
Join Date: Sep 2007
Posts: 614
Thanks: 22
Thanked 52 Times in 51 Posts
krishnaindia2007 is on a distinguished road
Re: Sql join performance Query

Quote:
Originally Posted by debasisdas View Post
try to make the smaller table (as per rows) as your left most table. That might make so difference.

The very purpose of the left outer join is to get the records meets the join condition + rest of the rows from left table.

The left most table will the table that he wants to get all the records whether it satisfies the join condition or not.

Changing the smaller table as left most table may not serve its purpose.
Reply With Quote
Reply

  Geeks Talk > Databases > Database Design


Thread Tools
Display Modes


Similar Threads

Thread Thread Starter Forum Replies Last Post
Outer Join Query Example mailtos.ravi SQL 4 03-05-2008 03:34 AM
Query Performance krishnaindia2007 Oracle 4 12-12-2007 03:21 AM
calculating the sum of a field in a query with join 3roses Oracle 2 09-05-2007 12:17 PM
Writing query with LEFT OUTER JOIN N SRIRAM K Data Warehousing 5 09-03-2007 05:08 AM
Join Query without using NOT IN subquery Geek_Guest SQL 1 03-07-2007 03:24 PM


All times are GMT -4. The time now is 06:19 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Copyright © 2008 GeekInterview.com. All Rights Reserved