473,703 Members | 3,759 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Can someone help me with multiple "Left Outer Joins"?

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.")

Looking for suggestions (and a lesson or two!) This is my first attempt
at SQL.

Current (working, albeit slowly) Query Below

TIA

SELECT

LEDGER_ENTRY.en try_amount,
LEDGER_TRANSACT ION.credit_card _exp_date,
LEDGER_ENTRY.en try_datetime,
LEDGER_ENTRY.em ployee_id,
LEDGER_ENTRY.vo ucher_explanati on,
LEDGER_ENTRY.ca rd_reader_used_ ind,
STAY.room_id,
GUEST.guest_las tname,
GUEST.guest_fir stname,
STAY.arrival_ti me,
STAY.departure_ time,
STAY.arrival_da te,
STAY.original_d eparture_date,
STAY.no_show_st atus,
STAY.cancellati on_date,
FOLIO.house_acc t_id,
FOLIO.group_cod e,
LEDGER_TRANSACT ION.original_re ceipt_id

FROM

mydb.dbo.LEDGER _ENTRY LEDGER_ENTRY,
mydb.dbo.LEDGER _TRANSACTION LEDGER_TRANSACT ION,

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_F OLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.foli o_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay _id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

WHERE

LEDGER_ENTRY.tr ans_id = LEDGER_TRANSACT ION.trans_id
AND FOLIO.folio_id = LEDGER_TRANSACT ION.folio_id
AND LEDGER_ENTRY.pa yment_method='3 737******6100'
AND LEDGER_ENTRY.pr operty_id='abc1 23'

ORDER BY

LEDGER_ENTRY.en try_datetime DESC

Sep 15 '05 #1
7 31559
What is actually your question :-) ?

Jens Suessmeyer.

Sep 15 '05 #2
My question is, Can this query be further optimized for speed?

I have tried creating temporary databases, to break-up the outer joins
into different select commands, but I couldn't get it to work properly.
I'm using VB6 and ADO, invoking the execute method of the adodb.command
object to return the recordset.

Sep 15 '05 #3
To take access to different databases and tables you may
use for example syntax like this:
DatabaseName.Ta bleName.ColumnN ame

I do not see why you would need to create a
temporal db and why this would help you
with performance.

I wonder if you meant a temporal table instead.

In general I experienced, that views (depending on what the do) may slow
down the whole query.
also ORDER BY.

I suggest you break your select statement in three peaces so you may
see with the profiler wich join would take the most of time.
maybe by applying an index to specific columns you get a bit more
performance.

if you watch the query from your VB-application, you wil have to
differ between the time thats used by your application and ADO
and the time the Database itself needs.
the bottleneck could also be at the application-side!

Hope this gave some hints.

Sonja

"Steve" <bu********@yah oo.com> schrieb im Newsbeitrag
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .
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.")

Looking for suggestions (and a lesson or two!) This is my first attempt
at SQL.

Current (working, albeit slowly) Query Below

TIA

SELECT

LEDGER_ENTRY.en try_amount,
LEDGER_TRANSACT ION.credit_card _exp_date,
LEDGER_ENTRY.en try_datetime,
LEDGER_ENTRY.em ployee_id,
LEDGER_ENTRY.vo ucher_explanati on,
LEDGER_ENTRY.ca rd_reader_used_ ind,
STAY.room_id,
GUEST.guest_las tname,
GUEST.guest_fir stname,
STAY.arrival_ti me,
STAY.departure_ time,
STAY.arrival_da te,
STAY.original_d eparture_date,
STAY.no_show_st atus,
STAY.cancellati on_date,
FOLIO.house_acc t_id,
FOLIO.group_cod e,
LEDGER_TRANSACT ION.original_re ceipt_id

FROM

mydb.dbo.LEDGER _ENTRY LEDGER_ENTRY,
mydb.dbo.LEDGER _TRANSACTION LEDGER_TRANSACT ION,

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_F OLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.foli o_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay _id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

WHERE

LEDGER_ENTRY.tr ans_id = LEDGER_TRANSACT ION.trans_id
AND FOLIO.folio_id = LEDGER_TRANSACT ION.folio_id
AND LEDGER_ENTRY.pa yment_method='3 737******6100'
AND LEDGER_ENTRY.pr operty_id='abc1 23'

ORDER BY

LEDGER_ENTRY.en try_datetime DESC

Sep 15 '05 #4
Yes, I meant that I tried to create a temporary table, not db, sorry...

Regarding the 3 joins...would putting parenthesis around any of them
help?
How are they being processed exactly?
The first join has a single table reference immediately preceding the
join statement, but the others cannot (is that correct?)
What are the next two joins being joined to exacty (since there is no
table specified before the two join statements?

The tables that I'm joining look like this:

------FOLIO---------------STAY FOLIO-----------------STAY
|
|
|___________GUE ST

All transactions have FOLIO records, but not all transactions have STAY
FOLIO, STAY, OR GUEST records.
I need to return all transactions that have a folio record.

This is the syntax I'm using to accomplish this:

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_F OLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.foli o_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay _id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

I don't understand how the order of the joins affects their processing.
Is there a better way to phrase the joins, given the table
relationships as outlined above?

Thanks!

Sep 15 '05 #5
> Yes, I meant that I tried to create a temporary table, not db, sorry...

this would be accomplished with views. Like I mentioned before
but this may not be a Solution for your problem.

As I know a lot of Select Squences with a lot more Joins
than you need here, I do not believe that your performance-problem
results from the sql statement.
Depending on the server-machine your Database is installed on,
there may be different reasons, why this query takes a long time.

1)Maybe your tables are big. Lets asume each of them has 1 000 000 tuples.
Even then the query should not last (DEPENDING ON YOUR MACHINE)
a "long" time.
If this Machine is for example the whole time working on a 70% level
it slows down everything to death.
If the machine has enough breath to acomplish your query and you are testing
just solely we leave this section ...

2)the dbms tries to optimize sql -queries by itself, to make them faster, if
you want to
optimize more, use only the lines and columns you seek. It makes the whole
thing
a little faster if you simply snip columns and rows that you do not need.

3) it may help with performance to apply indexes to columns that will be
joined

4) Your application is getting all data over network one by one and
everything
slows down. Then its not a database or query -problem

5) use the SQL Profiler to see where the bottleneck is.

If you like, create for each join a view and then simply join the view with
folio
like this for example
-------------------------------------
CREATE VIEW stay_test AS
Select Stay_folio.foli o_id from
STAY_FOLIO left outer join STAY
ON
stay_folio.stay _id = stay.stay_id
-----------------------------------
SELECT * FROM
folio LEFT OUTER JOIN stay_test
ON
folio.folio_id = stay_test.folio _id
LEFT OUTER JOIN guest
ON
folio.guest_id = guest.guest_id
-----------------------------------
At the SQL profiler you can view each selection that is made and how long it
takes to get result


Regarding the 3 joins...would putting parenthesis around any of them
help?
How are they being processed exactly?
The first join has a single table reference immediately preceding the
join statement, but the others cannot (is that correct?)
What are the next two joins being joined to exacty (since there is no
table specified before the two join statements?

The tables that I'm joining look like this:

------FOLIO---------------STAY FOLIO-----------------STAY
|
|
|___________GUE ST

All transactions have FOLIO records, but not all transactions have STAY
FOLIO, STAY, OR GUEST records.
I need to return all transactions that have a folio record.

This is the syntax I'm using to accomplish this:

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_F OLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.foli o_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay _id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

I don't understand how the order of the joins affects their processing.
Is there a better way to phrase the joins, given the table
relationships as outlined above?

Thanks!

Sep 15 '05 #6
On 14 Sep 2005 20:19:28 -0700, Steve wrote:
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.
Hi Steve,

That need not be the case. I guess that adding the right indexes would
help a lot.

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.")
I could help you with solving this problem, but I won't. Breaking a
query in smaller pieces with temp tables has a fair chance to hurt your
performance, and very limited chance to do any good.

The query optimizer can use all the tricks that you can use, and then
some. Better to trust that the optimizer will pick the right execution
plan from the flock of available options instead of forcing it to do the
way you think is best. There ARE cases where the optimizer does need
some guidance, but they are the exception rather than the rule.
Current (working, albeit slowly) Query Below


Thanks for posting the query, but you'll have to provide a lot more
information to enable us to help you. We need to know the structure of
your tables (posted as CREATE TABLE statements, including all properties
and constraints, but excluding irrelevant columns), the indexes you have
defined for your tables, if any (posted as CREATE INDEX statements), a
few rows of sample data (posted as INSERT statements) and the expected
results from that sample data to give us an idea what you're trying to
achieve. Including a short description of your actual business problem
is a great idea too. See www.aspfaq.com/5006 for some useful pointers on
hjow to assemble the information we need, in the best format.

Oh, and we'd also like to know how many rows (approximately) you have in
each of your tables - and the execution plan that is currently used for
your query (you can get the execution plan if you run the query with SET
SHOWPLAN_ALL ON.

With that information, we can try to find out why your current query is
running slow, and how to remedy that.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Sep 15 '05 #7
Steve (bu********@yah oo.com) writes:
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.")

Looking for suggestions (and a lesson or two!) This is my first attempt
at SQL.


As Hugo pointed out, it is impossible to give very precise advice from
from the information you have posted. Assuming that there is an index
on (payment_method , property_id) on LEDGER_ENTRY, and that all other
tables have indexes on the columns you join on, I would expect the query
to perform well. Then again, there can be several reasons to why it does
not.

I analysed your query, and I think that I found one flaw. Here is a
rewritten version:

SELECT LE.entry_amount , LT.credit_card_ exp_date, LE.entry_dateti me,
LE.employee_id, LE.voucher_expl anation, LE.card_reader_ used_ind,
S.room_id, G.guest_lastnam e, G.guest_firstna me, S.arrival_time,
S.departure_tim e, S.arrival_date, S.original_depa rture_date,
S.no_show_statu s, S.cancellation_ date, F.house_acct_id ,
F.group_code, LT.original_rec eipt_id
FROM mydb.dbo.LEDGER _ENTRY LE
JOIN mydb.dbo.LEDGER _TRANSACTON LT ON LE.trans_id = LT.trans_id
JOIN mydb.dbo.FOLIO F ON F.folio_id = LT.folio_id
LEFT JOIN (mydb.dbo.STAY_ FOLIO SF
JOIN mydb.dbo.STAY S ON SF.stay_id = S.stay_id)
ON F.folio_id = SF.folio_id
LEFT JOIN mydb.dbo.GUEST G ON F.guest_id = G.guest_id
WHERE LE.payment_meth od='3737******6 100'
AND LE.property_id= 'abc123'
ORDER BY LE.entry_dateti me DESC

This alters the semantics of the query slightly, and I guess to the
good. Whether it affects performance, I don't know.

One potential problem is if the joins from FOLIO to STAY_FOLIO and GUEST
could hit multiple rows in the latter tables. In such case you get too
many rows back, which also could cause poor performance.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

Sep 15 '05 #8

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

Similar topics

3
10052
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
4214
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.")
0
501
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.")
3
19476
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 ?
14
5712
by: mike | last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly. There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key of Item, and is also a field in LogEvent. Some ItemIDs in LogEvent do not correspond to ItemIDs in Item, and periodically we need to purge the non-matching ItemIDs from LogEvent. The query is: delete from LogEvent where EventType != 'i' and ItemID in
1
1439
by: Kevin Frey | last post by:
I know this is a way-out question, and I'm not confident of a positive answer, but I'd like to know whether it is possible to protect/secure a View so that it can only be used as a TARGET in a join operation, as opposed to being used for direct SELECT. for example: SELECT * FROM MyView -- would fail but
2
3280
by: Michael Scott | last post by:
This is driving me nuts. Can anyone help? I have a program which searches for and analyses Access databases across a corporate network. At one point in the code I am looping quickly through all the querydefs in the target database to give an idea of what we're dealing with: For Each qdf In dbsDiscoveredDatabase.QueryDefs If Left(qry.Connect, 4) = "ODBC" Then mblnODBC = True
1
3275
by: nico3334 | last post by:
I have a query that currently pulls data from a main table and a second table using LEFT OUTER JOIN. I know how to do make another LEFT OUTER JOIN with the main table, but I want to add another LEFT OUTER JOIN to the second table. So I want the third table to be joined through the second table, not the main table. Here is my original code that joins the main table and the second table SELECT t1.supply, t2.inventory, FROM MAIN_TABLE...
0
1980
by: CatchSandeepVaid | last post by:
Product and ProductBasic has one-to-one relationship Product ---> ProductID <<PK>> ProductBasic ----> ProductId, useCode, StartTime as composite key..... this startTime's value will be known as runtime. Now i want to define mapping between Product & ProductBasic, how will i do it ? Moreover suppose i define their mapping as product.productID = productBasic.productID and useCode='a' as :
0
8761
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
8672
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9257
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...
0
8969
tracyyun
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...
0
7876
agi2029
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...
1
6594
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...
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2462
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2070
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.