473,378 Members | 1,512 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

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 14547
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.cvut.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
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...
4
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
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
1
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"...
7
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"...
6
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 (...
3
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...
0
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...
9
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

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.