473,722 Members | 2,352 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

complexity of nested self left joins?

Hi,

I'm curious about the computational complexity of a query I have. The
query contains multiple nested self left joins, starting with a simple
select, then doing a self left join with the results, then doing a self
left join with those results, etc. What puzzles me is that the time
required for the query seems to grow exponentially as I add additional
left joins, which I didn't expect. I expected the inner select to
return about 25 rows (it does), then I expected the self join to result
in about 25 rows (it does), etc. Each join just adds another column; it
doesn't add more rows. So the left part of the join is staying the same
size, and so is the right part of the join, since I'm always joining
with the same table.

So I would think the time for this query should be (time to join 25
rows against the source table) * (num joins), but it seems to be
something like (num rows) ^ (num joins). Any ideas? I'm just trying to
understand the system a little better. (But if you have any ideas about
improving the query, I'm always open to those, too.)

The execution plan is what you'd expect: an index seek loop-joined with
another index seek, the results of which are merge-joined with another
index seek, the results of which are merge-joined with another index
seek, ad nauseum, until a final "compute scalar cost (39%)" and "select
(0%)"

For the brave and curious, I've pasted the query below.

Thanks

select right(x.cp_yyyy mm, 2)+'-'+left(x.cp_yyy ymm, 4) as [Month],
table0.cp_num_l oans/1 as [AFCM9704], table1.cp_num_l oans/1 as
[AFC9104], table2.cp_num_l oans/1 as [BFAT01C], table3.cp_num_l oans/1 as
[BFAT02B], table4.cp_num_l oans/1 as [BFAT03D], table5.cp_num_l oans/1 as
[BFAT03E], table6.cp_num_l oans/1 as [BFAT03F], table7.cp_num_l oans/1 as
[BFAT04A], table8.cp_num_l oans/1 as [BFAT04C], table9.cp_num_l oans/1 as
[BFAT04D], table10.cp_num_ loans/1 as [BFAT99C] from (((((((((((sele ct
distinct cp_yyyymm from cp_deal_history where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymm
between 200304 and 200504) as x left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='AFC M9704') as
table0 on x.cp_yyyymm=tab le0.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='AFC 9104') as table1
on x.cp_yyyymm=tab le1.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T01C') as table2
on x.cp_yyyymm=tab le2.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T02B') as table3
on x.cp_yyyymm=tab le3.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T03D') as table4
on x.cp_yyyymm=tab le4.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T03E') as table5
on x.cp_yyyymm=tab le5.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T03F') as table6
on x.cp_yyyymm=tab le6.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T04A') as table7
on x.cp_yyyymm=tab le7.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T04C') as table8
on x.cp_yyyymm=tab le8.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T04D') as table9
on x.cp_yyyymm=tab le9.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFA T99C') as
table10 on x.cp_yyyymm=tab le10.cp_yyyymm order by x.cp_yyyymm

Jul 23 '05 #1
4 4105
For those of you who's cranial parser requires liberal use of indents
and line breaks, I spent about an hour cleaning this up.

BTW, did you write this query to experiment with running times or are
you actually trying to accomplish something useful?

select right(x.cp_yyyy mm, 2)+'-'+left(x.cp_yyy ymm, 4) as [Month],
table0.cp_num_l oans/1 as [AFCM9704],
table1.cp_num_l oans/1 as [AFC9104],
table2.cp_num_l oans/1 as [BFAT01C],
table3.cp_num_l oans/1 as [BFAT02B],
table4.cp_num_l oans/1 as [BFAT03D],
table5.cp_num_l oans/1 as [BFAT03E],
table6.cp_num_l oans/1 as [BFAT03F],
table7.cp_num_l oans/1 as [BFAT04A],
table8.cp_num_l oans/1 as [BFAT04C],
table9.cp_num_l oans/1 as [BFAT04D],
table10.cp_num_ loans/1 as [BFAT99C]
from (select distinct cp_yyyymm
from cp_deal_history
where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C',
'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C',
'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as x

left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='AFC M9704') as table0
on x.cp_yyyymm=tab le0.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='AFC 9104') as table1
on x.cp_yyyymm=tab le1.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T01C') as table2
on x.cp_yyyymm=tab le2.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T02B') as table3
on x.cp_yyyymm=tab le3.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T03D') as table4
on x.cp_yyyymm=tab le4.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T03E') as table5
on x.cp_yyyymm=tab le5.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T03F') as table6
on x.cp_yyyymm=tab le6.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T04A') as table7
on x.cp_yyyymm=tab le7.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T04C') as table8
on x.cp_yyyymm=tab le8.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T04D') as table9
on x.cp_yyyymm=tab le9.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFA T99C') as table10
on x.cp_yyyymm=tab le10.cp_yyyymm
order by x.cp_yyyymm

Jul 23 '05 #2
Still not entirely sure what you're trying to accomplish, but -- if
you're doing what I think you are -- why not try something like this:

select right(x.cp_yyyy mm, 2)+'-'+left(x.cp_yyy ymm, 4) as [Month],
case when y.cp_deal_id = 'AFCM9704'
then y.cp_num_loans/1 else null end as [AFCM9704],
case when y.cp_deal_id = 'AFC9104'
then y.cp_num_loans/1 else null end as [AFC9104],
case when y.cp_deal_id = 'BFAT01C'
then y.cp_num_loans/1 else null end as [BFAT01C],
case when y.cp_deal_id = 'BFAT02B'
then y.cp_num_loans/1 else null end as [BFAT02B],
case when y.cp_deal_id = 'BFAT03D'
then y.cp_num_loans/1 else null end as [BFAT03D],
case when y.cp_deal_id = 'BFAT03E'
then y.cp_num_loans/1 else null end as [BFAT03E],
case when y.cp_deal_id = 'BFAT03F'
then y.cp_num_loans/1 else null end as [BFAT03F],
case when y.cp_deal_id = 'BFAT04A'
then y.cp_num_loans/1 else null end as [BFAT04A],
case when y.cp_deal_id = 'BFAT04C'
then y.cp_num_loans/1 else null end as [BFAT04C],
case when y.cp_deal_id = 'BFAT04D'
then y.cp_num_loans/1 else null end as [BFAT04D],
case when y.cp_deal_id = 'BFAT99C'
then y.cp_num_loans/1 else null end as [BFAT99C],
from (select distinct cp_yyyymm
from cp_deal_history
where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C',
'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C',
'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as x
left outer join cp_deal_history y
on x.cp_yyyymm = y.cp_yyyymm
order by x.cp_yyyymm

Jul 23 '05 #3
That is not great surprise. LEFT OUTER JOINs are not well optimized
and thigns get flaky about five self joins in most queries. The real
proglem is that your code is awful.

Why would anyone, except an 1950's COBOL programmer, store temporal
data in strings! Why did you name a column for its format and not its
contents? Time is always stored as durations in a temporal data type.
Why would you divide by integer 1 when the column looks like a count of
loans? Tell me that you did not store that data as strings too and
have to force type conversions!! Try this:

SELECT deal_date,
MAX(CASE WHEN deal_id = 'AFCM9704'
THEN loan_cnt ELSE NULL END) AS AFCM9704,
..
MAX(CASE WHEN deal_id = 'BFAT99C'
THEN loans_cnt ELSE NULL END) AS BFAT99C
FROM DealHistory
WHERE deal_date BETWEEN '2003-04-01' AND '2005-04-30'
GROUP BY deal_date;

Jul 23 '05 #4
Sweet Jesus, ZeldorBlat, if I'd thought anyone was actually going to
look at that I'd have pretty-printed it myself. So I apologize, and
thank you for your effort. The query constructs the data for a chart,
with cp_yyyymm being the independent variable, and each row needing the
value of cp_num_loans for each month and each particular deal id. So
the result set needs exactly one row per month. Your suggested query is
definitely moving in the right direction, but it just pulls all the
data - it doesn't "filter" it and arrange it as required. After playing
around a little bit, the query I eventually got working was basically
what CELKO suggested:

select right(z.cp_yyyy mm, 2)+'-'+left(z.cp_yyy ymm, 4) as [Month],
max(AFCM9704_un filtered) as [AFCM9704],
max(AFC9104_unf iltered) as [AFC9104],
max(BFAT01C_unf iltered) as [BFAT01C],
max(BFAT02B_unf iltered) as [BFAT02B],
max(BFAT03D_unf iltered) as [BFAT03D],
max(BFAT03E_unf iltered) as [BFAT03E],
max(BFAT03F_unf iltered) as [BFAT03F],
max(BFAT04A_unf iltered) as [BFAT04A],
max(BFAT04C_unf iltered) as [BFAT04C],
max(BFAT04D_unf iltered) as [BFAT04D],
max(BFAT99C_unf iltered) as [BFAT99C]
from
(select cp_yyyymm, cp_deal_id,
case when y.cp_deal_id = 'AFCM9704'
then y.cp_num_loans/1 else null end as [AFCM9704_unfilt ered],
case when y.cp_deal_id = 'AFC9104'
then y.cp_num_loans/1 else null end as [AFC9104_unfilte red],
case when y.cp_deal_id = 'BFAT01C'
then y.cp_num_loans/1 else null end as [BFAT01C_unfilte red],
case when y.cp_deal_id = 'BFAT02B'
then y.cp_num_loans/1 else null end as [BFAT02B_unfilte red],
case when y.cp_deal_id = 'BFAT03D'
then y.cp_num_loans/1 else null end as [BFAT03D_unfilte red],
case when y.cp_deal_id = 'BFAT03E'
then y.cp_num_loans/1 else null end as [BFAT03E_unfilte red],
case when y.cp_deal_id = 'BFAT03F'
then y.cp_num_loans/1 else null end as [BFAT03F_unfilte red],
case when y.cp_deal_id = 'BFAT04A'
then y.cp_num_loans/1 else null end as [BFAT04A_unfilte red],
case when y.cp_deal_id = 'BFAT04C'
then y.cp_num_loans/1 else null end as [BFAT04C_unfilte red],
case when y.cp_deal_id = 'BFAT04D'
then y.cp_num_loans/1 else null end as [BFAT04D_unfilte red],
case when y.cp_deal_id = 'BFAT99C'
then y.cp_num_loans/1 else null end as [BFAT99C_unfilte red]
from cp_deal_history as y
where cp_deal_id in ('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B',
'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as z
group by z.cp_yyyymm
order by z.cp_yyyymm

Note the lack of a single join. Sigh - I don't know what I was
thinking. Thanks very much to both of you for your help. (I'm still
curious as to why the joins weren't working the way I expected, though.)

Jul 23 '05 #5

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

Similar topics

5
1638
by: naturalborncyborg | last post by:
Hi, I'm using nested lists as arrays and having some problems with that approach. In my puzzle class there is a swapelement method which doesn't work out. Any help and comments on the code will be appreciated. Thanks. # Puzzle.py # class for a sliding block puzzle # an starting state of a 8-puzzle could look like the following: # where X is an empty space and the move up in the example would move
1
2793
by: nuked | last post by:
I have a table that has values of variables for certain entities. The columns of interest are targetID, variableID, and valueID. A row (1, 5, 9) means that target number 1 has a value of 9 for variable 5. Being denormalized, target number one will have many possible rows in this table, one for each variable for which it has a value. My problem occurs when I want to find out what targets match a certain set of variable values. For...
7
9219
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into a table (c) re-queries another table using a subquery which references the inserted table (correlated or not)
3
23098
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but he doesn't know how to do that, or even if he can, and i don't have to time to learn DB2 from scratch right now. The following SQL Query is a trimmed sample of the full View (i.e. Logical) definition - and i would create it on an SQL based...
1
6442
by: Steve C | last post by:
Hi, I'm having problems constructing a nested join. It's quite complex, so here's a simplfied example of the problem. Any thoughts on what I'm doig wrong - or if I've got the whole approach wrong are welcome. I've two tables :- one is a contact table contacting name, addresses etc. Three of the fields represent users - 'created by', 'last modified by' and 'owner'.
1
2005
by: Grant | last post by:
Hi I have a query that left joins 2 queries. The query works in Access but i want to use the source code in ASP and need to re-write it as a nested query but im not sure how to do that. MyStaff selects all staff who fall under a specific supervisor including that supervisor: SELECT Staff.ID, Staff.Name FROM Staff WHERE (((Staff.ID)="***")) OR (((Staff.SupervisorID)="***")); LeaveTaken selects all staff under a supervisor who have taken...
2
3709
jlm699
by: jlm699 | last post by:
Greetings developers! I'm in a pickle and can't figure out how to get around this problem... Here is a complete test case of code import os, sys, wx class MainPanel(wx.Panel):
3
4384
by: codefragment | last post by:
Hi I have a chunky bit of sql that I will want to call from a number of places. It will return a few thousand rows. Whats the best way of structuring this? 1) I initially thought of using nested stored procedures and returning the result in a temporary table. However the scope of the temporary table seems to be limited to the stored procedure its created in so unless I create the temporary table in every stored
3
2123
by: Cousson, Benoit | last post by:
I don't think so; my original email was mainly a question. I do agree that they are other ways to do what I'm trying to achieve; there are always several ways to solve an issue. Few days ago, I decided to use nested class because I realized that it was the most convenient way to implement my need. Since this feature is supported in many languages, I was just surprised that Python did support it only partially, hence my original email. ...
0
9236
jinu1996
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...
1
9154
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,...
0
9088
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...
1
6681
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
5995
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
4502
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...
1
3207
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
2601
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2147
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.