What is the fastest way...

[Dominik Czechowski]

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

[Knut Stolze]

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

[Dominik Czechowski]

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

[Serge Rielau]

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

[--CELKO--]

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?

[Dominik Czechowski]

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?