DB2 Views

[harborboy76]

Hi,
I have a view that takes quite some time to run.
It's a view of a table that is quite large (over a million
rows). I notice that the runtime of the view is not what
causes the slowness, but the complile time is. Is there a
way to make it so that the view does not compile each time
it runs ?

Thanks,
N.

[fred.sobotka]

First and foremost, the Design Advisor can help you understand if the
data accessed by the view could be indexed better. Based on its
recommendations, you may end up changing the clustering of the involved
table(s), or even creating a materialized query table that contains the
current results of the query on which the view is defined.

If the problem is just with compiling the SQL, then consider converting
the query behind the view into an SQL procedure instead. The procedure
would contain one or more static SQL statements and return a result set
comparable to the current structure of the view. This will eliminate
the cost of repetitive statement compilations and query optimization.
However, this approach will require you to understand the various
predicates that users are providing when they query the view, and
provide equivalent options in the form of SQL PL input parameter
variables, which are then referenced by the statements inside the
procedure.

Hope this helps,

Fred

[m0002a]

Post the view, the DDL for table(s) and all index(es).

[Ian]

ha*********@yahoo.com wrote:

Hi,
I have a view that takes quite some time to run.
It's a view of a table that is quite large (over a million
rows). I notice that the runtime of the view is not what
causes the slowness, but the complile time is. Is there a
way to make it so that the view does not compile each time
it runs ?

Thanks,
N.

Define "long", and how are you calculating the compile time?

Are you literally talking about how the optimizer takes to
dynamically create the access plan, or are you talking about
the time it takes from when you execute your query until it
starts returning data?

[joonw]

tempted to suggest MQT (Materialized Query Table)