472,993 Members | 3,188 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Improving a SQL statement

Is there any way to improve this statement? A is the header table while B has 0 to many records for each record in A. I'm trying to pick only the latest record in B for the associated seq in A.

Expand|Select|Wrap|Line Numbers
  1. select * from A left outer join B on B.seq = A.seq
  2. and B.order = (select max(order) from B where B.seq = A.seq)
Sep 14 '09 #1
6 3383

I'm a bit surprised that this query is working in its current form. When I tried to run it i got the following:
SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement is not valid.


An ON clause associated with a JOIN operator or in a MERGE
statement is not valid for one of the following reasons.

o The ON clause cannot include any subqueries.
o Column references in an ON clause must only reference columns
of tables that are in the scope of the ON clause.
o Scalar fullselects are not allowed in the expressions of an
ON clause.
o A function referenced in an ON clause of a full outer join
must be deterministic and have no external action.
o A dereference operation (->) cannot be used.
o A SQL function or SQL method cannot be used.
o The ON clause cannot include an XMLQUERY or XMLEXISTS

1. Are you sure this is a DB2 query? if not then this might be in the wrong place.

2. Assuming your tables are like that below.

Expand|Select|Wrap|Line Numbers
  1. A                                        B                         
  2. Seq         Order                      Seq    order
  3. 1               1                      1         1
  4. 2               2                      1         2 
  5.                                        1         3
  6.                                        1         4
  7.                                        2         1 
  8.                                        2         2
  9.                                        2         3
  10.                                        2         4 
  12. And the desired output is
  14. Seq         Order
  15. 1                4
  16. 2                4
  18. try..
  19. -------------------------------------------
  20. select  r2.seq, r2.ord from 
  21. (
  22.       select r1.seq, r1.ord  from
  23.       (
  24.     select distinct b.seq, max(b.ord)  from b   
  25.                    group by b.seq
  26.        ) r1
  27. left outer join a on r1."ord" = a."ord" 
  28. )r2;
  29. -------------------------------------------
The advantage here is that you are not doing subselects for each row satisfying the join condition. Subselects are expensive their use should be minimized where possible.
Oct 30 '09 #2
I tried something similar, but it runs slower than the original. I'm guessing its because these temporary tables don't use indexes. The query has to return the rows back from both tables, and TABLEA has to be on the left of the outer join which was why I couldn't just use what you wrote above.
Expand|Select|Wrap|Line Numbers
  1. select *
  2. from TABLEA
  3. left outer join (select * from TABLEB r2                  
  4.               left outer join (select ceseq mseq, max(ceeord) mord 
  5.               from TABLEB group by ceseq) r1              
  6.               on r2.ceseq = r1.mseq and r2.ceeord = mord           
  7.                             ) r3 on cmseq = ceseq and ceeord = mord
Oct 30 '09 #3
Are you using the DB2 Control Center to run this query? If so can you do a visual explain and attach that to the thread, that should show which indexes are used by the db2 optimizer as well as costs of doing joins, sorts etc to help find ways to optimize this query.

If you do not have access to the DB2 Control Center you can use the command line dynexpln or db2exfmt tools to gather the same information.
Oct 31 '09 #4
There is no control center and those commands don't work on the iSeries and as/400.
Nov 2 '09 #5
Info on gathering explain data for AS/400:

Nov 2 '09 #6
In general, I have found that the OLAP functions perform well in this regard. You might want to try something like the following sample code:

Expand|Select|Wrap|Line Numbers
  1. create table A(seq int, id char(1));
  2. insert into A values (1,'A');
  3. insert into A values (2,'B');
  4. insert into A values (3,'C');
  6. create table B(seq int, order int);
  7. insert into B values (1,1);
  8. insert into B values (1,2);
  9. insert into B values (1,3);
  10. insert into B values (1,4);
  11. insert into B values (2,2);
  12. insert into B values (2,3);
  13. insert into B values (2,4);
  14. insert into B values (2,5);
  16. select distinct A.seq, A.id, max(B.order) over(partition by B.seq) as max_order
  17.   from A
  18.   left outer join
  19.        B
  20.     on A.seq = B.seq;
Nov 11 '09 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Wensheng | last post by:
Hi, I wrote a small template engine called spytee. Like any template enigne, it take a text(html) template file as input, process the variable tags in the file, and display the resulted text. The...
by: Andy Tran | last post by:
I built a system using mysql innodb to archive SMS messages but the innodb databases are not keeping up with the number of SMS messages coming in. I'm looking for performance of 200 msgs/sec where...
by: Brian Basquille | last post by:
Hello all. Have been working on the Air Hockey game on and off for a couple of weeks now.. but have had plenty of other assignments to keep me busy along with it. But i would like some...
by: Robin | last post by:
For an asp.net project that is deployed to a load balanced web servers, are there any performance changes that can be made in .Net runtime or IIS 6? Also are there any additional tips for...
by: Irfan Akram | last post by:
Hi Guys, I am in search of some innovative ideas in improving the interface of Online Marking System. At the moment it has some good functionality, but lacks a professional interface. Is...
by: - | last post by:
I have only 1m records in my database running on a laptop of speed 1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk. I use 'LIMIT x,10' for the query to utilise record paging. When the...
by: Jo | last post by:
Hi there: I m wondering what can I do to improve my code, everytime I am coding I feel like it could be done better. I started on c# a good months ago and feel conformtable but sometimes I Need...
by: Michel Esber | last post by:
Hello, Environment: DB2 LUW v8 FP15 / Linux I have a table with 50+ Million rows. The table structure is basically (ID - Timestamp). I have two main applications - one inserting rows, and the...
by: Alexzive | last post by:
Hello there :) , I am a python newbie and need to run following code for a task in an external simulation programm called "Abaqus" which makes use of python to access the mesh (ensamble of nodes...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.