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

DB2 Views

P: n/a
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.

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
Post the view, the DDL for table(s) and all index(es).

Nov 12 '05 #3

P: n/a
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?

Nov 12 '05 #4

P: n/a
tempted to suggest MQT (Materialized Query Table)

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.