473,883 Members | 1,682 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

outer join and indexes

Hello,
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table
has more then million rows and execution plan shows table scan on it. I have
indexed columns
on which the join is made. If I replace LEFT JOIN with INNER JOIN, index is
used and execution
takes few seconds but with LEFT JOIN there is a table scan , so the
execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?
What is the reason for such behavior?
(I use SQL Server 2000 Developer edition SP3)

Any suggestion appretiated

eXavier
Jul 20 '05 #1
4 14576
If your using a LEFT JOIN then you just told SQL Server to return every row
from the table on the left regardless of if it has a match on the right or
not. In that case it makes no sense to use the index when it must read all
the rows anyway.

--

Andrew J. Kelly
SQL Server MVP
"eXavier" <hu***@email.cz > wrote in message
news:bi******** ***@ns.felk.cvu t.cz...
Hello,
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table
has more then million rows and execution plan shows table scan on it. I have indexed columns
on which the join is made. If I replace LEFT JOIN with INNER JOIN, index is used and execution
takes few seconds but with LEFT JOIN there is a table scan , so the
execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?
What is the reason for such behavior?
(I use SQL Server 2000 Developer edition SP3)

Any suggestion appretiated

eXavier

Jul 20 '05 #2
[posted and mailed, please reply in news]

eXavier (hu***@email.cz ) writes:
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table has more then million rows and execution plan shows
table scan on it. I have indexed columns on which the join is made. If I
replace LEFT JOIN with INNER JOIN, index is used and execution takes few
seconds but with LEFT JOIN there is a table scan , so the execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?


I think Andrew misunderstood your question. But maybe I misunderstood
Andrew. I believe this query is akin to the one you have problem with:

select *
from Customers c
left JOIN Orders o ON o.CustomerID = c.CustomerID
where c.PostalCode = '75012'

If you run this in Query Analyzer, with Show Execution Plan on, you will
see that SQL Server uses an Index Seek on Orders.

Really why the optimizer switches to table scan when you use a left
join, I cannot answer, as I don't know the tables and the indexes.

However, it is worth to keep in mind that is not always the best
strategy to use an index. Retrieval by non-clustered index, requires
SQL Server to do bookmark lookups to get the data, and this is only
effecient if you read a smaller portion of the table. At some level
table scan becomes cheaper.

When you are joining this also applies to clustered indexes. SQL
Server can use the clustered index for a nested loop join, but that
is akin to a bookmark lookup, so merge join or a hash when you scan
the table once may be better.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
It's possible I misunderstood his question but he did not provide an actual
query and he mentioned nothing of a WHERE clause. So if he uses an INNER
JOIN against a table on the right that is very small it may indeed use an
index. If he uses a LEFT JOIN with no WHERE clause it doesn't matter how big
or small the right table is, it will do a table scan unless there is an
index that is covering.

--

Andrew J. Kelly
SQL Server MVP
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
[posted and mailed, please reply in news]

eXavier (hu***@email.cz ) writes:
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table has more then million rows and execution plan shows
table scan on it. I have indexed columns on which the join is made. If I
replace LEFT JOIN with INNER JOIN, index is used and execution takes few
seconds but with LEFT JOIN there is a table scan , so the execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?


I think Andrew misunderstood your question. But maybe I misunderstood
Andrew. I believe this query is akin to the one you have problem with:

select *
from Customers c
left JOIN Orders o ON o.CustomerID = c.CustomerID
where c.PostalCode = '75012'

If you run this in Query Analyzer, with Show Execution Plan on, you will
see that SQL Server uses an Index Seek on Orders.

Really why the optimizer switches to table scan when you use a left
join, I cannot answer, as I don't know the tables and the indexes.

However, it is worth to keep in mind that is not always the best
strategy to use an index. Retrieval by non-clustered index, requires
SQL Server to do bookmark lookups to get the data, and this is only
effecient if you read a smaller portion of the table. At some level
table scan becomes cheaper.

When you are joining this also applies to clustered indexes. SQL
Server can use the clustered index for a nested loop join, but that
is akin to a bookmark lookup, so merge join or a hash when you scan
the table once may be better.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #4
Andrew J. Kelly (sq************ @shadhawk.com) writes:
It's possible I misunderstood his question but he did not provide an
actual query and he mentioned nothing of a WHERE clause. So if he uses
an INNER JOIN against a table on the right that is very small it may
indeed use an index. If he uses a LEFT JOIN with no WHERE clause it
doesn't matter how big or small the right table is, it will do a table
scan unless there is an index that is covering.


Yes, this will scan:

SELECT *
FROM bigtbl b
LEFT JOIN smalltbl s ON b.col = s.col

But I understood his question as that he had:

SELECT *
FROM unknowntbl u1
JOIN unknowntbl u2 ON ...
LEFT JOIN bigtbl b ON ...

And this could well use an index on bigtbl. (With some luck.)

But you are right that the question did not give much information, and
also he did in fact say that he had one small table, but that he had
several tables of unknown size. All we know is that the last one was big.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
4978
by: Matt | last post by:
Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and also display their corresponding entries in arb, but if there is NO entry in arb I still want it to show up as NULL or something, so that I can get the attention that there IS no language associated with that article.
4
4876
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
3
10067
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def, serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur where def.TYPEDETABLEDECODES = 4
1
4231
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
7
31577
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
6
9037
by: Thomas Beutin | last post by:
Hi, i've a speed problem withe the following statement: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p LEFT OUTER JOIN ot_kat_prod AS pz ON ( p.p_id = pz.p_id ) WHERE p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37'; This is terrible slow compared to the inner join: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
3
19481
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2 have the same type of operator, without using the OUTER JOIN syntax ?
0
4439
by: Gary Townsend | last post by:
I am using Postgres 7.4.6 i have 3 tables i want to join 3 tables and return a list of route_id to which a specified user_id does NOT belong. Table "public.vts_users" Column | Type | Modifiers ------------+------------------------+-------------------------------------- -------------------------- user_id | integer | not null default...
9
9231
by: shanevanle | last post by:
I have two tables that are pretty big. I need about 10 rows in the left table and the right table is filtered to 5 rows as well. It seems when I join the tables in the FROM clause, I have to filter my left table in the WHERE clause and cannot filter it in the FROM clause. This seems like it would cause a lot of overhead especially when my left table is ten thousand rows. Am I wrong in thinking that the two tables get joined with the...
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11121
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5793
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4210
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.