A question about views

N. - Neil - Briggs

A question about views
Dear list

db2 v5.1 os390

I have a query that is run as part of a data warehousing process. The query
is a monster with join's joining joins within joins. For some reason when
run as just one big query DB2 took hours to resolve the resultant rows. Just
for the hell of it I split the query down into workable components. Using
the unload program dsntiaul to exeute the sql and then loading temporary
tables with the data and then joining the new tables together to finally get
the result set.

As I suspected when run in this manner the query runs many times faster. I
think this has to do with the fact that indexes can be used on the temporary
tables i created , where in a query such as

select *
from
(select *
from table1) as t1
inner join
(select *
from table2
where ?
group by xyz) as t2 (this query is simplified for the sake of the example)

When reading records for t1 or t2 in the above example always results in a
tablespace scan.

So by actually creating physical tables with the unload and load process I
have improved the queries response.

So finally to my question : rather than creating tables to store data
required to resolve the query, what if I used views. Would this improve
performance as I would no longer have to unload and load data. Or will I get
really bad performance as I experience when running just one big (albeit
very convoluted) query?

I don't understand why db2 took so long processing one big query, I guess
this must be to do with the black art of DB2 bufferpools ??? Though we have
two bufferpools. One for sorting dsndb07 and one for all application tables
and indexes.

I hope this has made sense, I look forward to and response's

Many thanks
Neil Briggs

Harish Gangadharanunni

Re: A question about views
(in response to N. - Neil - Briggs)
Hi Neil,

The significant reasons that DB2 took long your process your query could
due to:

1. Cost of materialisation - since tables in some FROM clauses are derived
from several SQLs. Indexes may not be used in when accessing such
materialized tables.

2. Costs due to non-usage of indexes due to your SQL construct - could be
with the GROUP BY clauses.

Harish.



Frank E. Giguere

Re: A question about views
(in response to Harish Gangadharanunni)
Neil,

I'm afraid that a view would not help you from a perfromance
standpoint......other than having the joins "pre-coded" to ensure you have
optimal predicate usage. The view still has to execute the same SQL to
return the same result set.

Frank Giguere



"Briggs, N. -
Neil -" To: [login to unmask email]
<[login to unmask email] cc: (bcc: Frank Giguere/NUS)
UROPA.COM> Subject: A question about views



12/20/2001 09:49
AM
Please respond to
DB2 Data Base
Discussion List







Dear list

db2 v5.1 os390

I have a query that is run as part of a data warehousing process. The query
is a monster with join's joining joins within joins. For some reason when
run as just one big query DB2 took hours to resolve the resultant rows.
Just
for the hell of it I split the query down into workable components. Using
the unload program dsntiaul to exeute the sql and then loading temporary
tables with the data and then joining the new tables together to finally
get
the result set.

As I suspected when run in this manner the query runs many times faster. I
think this has to do with the fact that indexes can be used on the
temporary
tables i created , where in a query such as

select *
from
(select *
from table1) as t1
inner join
(select *
from table2
where ?
group by xyz) as t2 (this query is simplified for the sake of the example)

When reading records for t1 or t2 in the above example always results in a
tablespace scan.

So by actually creating physical tables with the unload and load process I
have improved the queries response.

So finally to my question : rather than creating tables to store data
required to resolve the query, what if I used views. Would this improve
performance as I would no longer have to unload and load data. Or will I
get
really bad performance as I experience when running just one big (albeit
very convoluted) query?

I don't understand why db2 took so long processing one big query, I guess
this must be to do with the black art of DB2 bufferpools ??? Though we have
two bufferpools. One for sorting dsndb07 and one for all application tables
and indexes.

I hope this has made sense, I look forward to and response's

Many thanks
Neil Briggs