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
4  14573 
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
[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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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.
|
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
|
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
|
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.")
|
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.")
| | |
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
|
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 ?
|
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...
|
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...
|
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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();...
|
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...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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...
| |
