By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

What is the fastest way...

P: n/a
Hi everyone!

I have two tables, T1 and T2 defined as follows:

create table T1(c1 integer not null,
primary key (c1));
create table T2(c1 integer not null,
c2 varchar(100) not null,
primary key (c1, c2));

populated with example data:

insert into t1 values (1), (2), (3);
insert into t2 values
(1, 'opt 1'),
(1, 'opt 2'),
(1, 'opt 3'),
(2, 'opt 1');

i would like to execute query like this one:
select t1.c1, GetOptions(t1.c1) as options_list from t1 left join t2 on
t1.c1=t2.c1

and get following results:
c1 options_list
------ -------------------------------
1 opt 1, opt 2, opt 3
2 opt 1
3 -

What is the fastest way of implementing GetOptions() function: plaing
with XML, recursive queries, or maybe some external code in C or Java
instead of SQL? Or maybe it's better to implement some processing on the
client side?

Lets assume that table T1 will have about 50k-100k rows, and there will
be 0-20 values of column c2 in table T2 (for each c1 column).

Thanks in advance for any info.

Sincerely,
Dominik Czechowski
Feb 23 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Dominik Czechowski wrote:
Hi everyone!

I have two tables, T1 and T2 defined as follows:

create table T1(c1 integer not null,
primary key (c1));
create table T2(c1 integer not null,
c2 varchar(100) not null,
primary key (c1, c2));

populated with example data:

insert into t1 values (1), (2), (3);
insert into t2 values
(1, 'opt 1'),
(1, 'opt 2'),
(1, 'opt 3'),
(2, 'opt 1');

i would like to execute query like this one:
select t1.c1, GetOptions(t1.c1) as options_list from t1 left join t2 on
t1.c1=t2.c1

and get following results:
c1 options_list
------ -------------------------------
1 opt 1, opt 2, opt 3
2 opt 1
3 -

What is the fastest way of implementing GetOptions() function: plaing
with XML, recursive queries, or maybe some external code in C or Java
instead of SQL? Or maybe it's better to implement some processing on the
client side?
External C/Java code gets more complicated if you really want to have it
perform the aggregation: http://tinyurl.com/3yfsur
However, you could use a READS SQL DATA function that performs the SELECT on
table T2 internally. Then your query would become:

SELECT t1.c1, GetOptions(t1.c1)
FROM t1
Lets assume that table T1 will have about 50k-100k rows, and there will
be 0-20 values of column c2 in table T2 (for each c1 column).
Is performance really an issue for you? Your tables aren't that big.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 23 '07 #2

P: n/a
W dniu: 2007-02-23 09:23 Knut Stolze napisal:
Dominik Czechowski wrote:
>>
What is the fastest way of implementing GetOptions() function: plaing
with XML, recursive queries, or maybe some external code in C or Java
instead of SQL? Or maybe it's better to implement some processing on the
client side?

External C/Java code gets more complicated if you really want to have it
perform the aggregation: http://tinyurl.com/3yfsur
However, you could use a READS SQL DATA function that performs the SELECT on
table T2 internally. Then your query would become:

SELECT t1.c1, GetOptions(t1.c1)
FROM t1
>Lets assume that table T1 will have about 50k-100k rows, and there will
be 0-20 values of column c2 in table T2 (for each c1 column).

Is performance really an issue for you? Your tables aren't that big.
Thanks for fast reply and link to your article, I will have to read it
later. As far as size of tables and performance: tables aren't big, but
queries will be running quite frequently, from many clients and that's
why I'm concerned about performance.

Dominik Czechowski
Feb 23 '07 #3

P: n/a
I have an example for string aggregation here:
http://www.iiug.org/waiug/present/Fo...rge_Rielau.ppt

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 23 '07 #4

P: n/a
Why do you wish to destroy First Normal Form (1NF) with a
concatendated list structure? It is the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just
SQL and RDBMS.

Yes, there are kludges, but would you prefer to be a good SQL
programmer instead?

Feb 24 '07 #5

P: n/a
Hi,

First of all my intention is not destroying or not 1NF, but achieving
the best possible performance with described data structures (in fact my
structure is a little more complicated). I just wonder if I can write a
UDF fast enough to handle such "reports" with many clients requesting
data even every second. In fact one part of my question was if
processing data on the client side wouldn't be a better option:
>What is the fastest way of implementing GetOptions() function: plaing
with XML, recursive queries, or maybe some external code in C or Java
instead of SQL? Or maybe it's better to implement some processing on
the client side?
When you write an application for more than one environment and have to
write similar code in different programming languages, in my example in
Delphi and php you start to wonder if it is not better to move some code
to database level.

P.S. Some time ago I had a similar problem with hierarchical (tree) data
structures presented in DB2. It worked quite ok with recursive queries
(DB2 v7), however returning raw queries from table and processing it on
the client side was much faster (about 10 times faster). Now, with DB2
v9 I know I could do it using XML, but the question remains - will it
work faster?

P.S.S. I love your books Joe.

Regards
Dominik Czechowski

--CELKO-- pisze:
Why do you wish to destroy First Normal Form (1NF) with a
concatendated list structure? It is the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just
SQL and RDBMS.

Yes, there are kludges, but would you prefer to be a good SQL
programmer instead?
Feb 24 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.