Any Obvious ways to improve DB2 performance?

[Adam Flinton]

Dear All,

We are replacing an Access based system (complete with Local files / files
held on a server) with a Db2 one.

At the mo the users are a little unhappy re speed. We are using the JDBC
drivers against Db2 V5.2 on NT. Having let one of them that knows a bit help
design queries by letting him a an Install of the Control / Command Center on
his desktop he's a little miffed at the speed vs Access.

I take it that creating Indexes will help (not done so yet)? Any other
obvious ploys? It should be serving 10 - 25 people (depending on time of
day). The install is the basic "out of the Box" push here install at the mo
with no optimisations at all.

Adam

[Ken North]

Adam,

Try educating your users to the fact that DB2 is markedly different
from Access. It writes data to a log file that helps you recover when
you have errors. Writing to the log file adds overhead, but you'll be
thankful if you get into a situation where you have to recover a
database that has been corrupted. Access does not provide a log file
and transaction journaling.

Certainly you should review your database design to see where you need
indexes.

Adam Flinton wrote in message ...

[Baer]

Increase the Buffer Pool Size if possible, thís will increase the chance to
find frequently used data in memory instead on disk. Eventually you may try
to use two or more bufferpools, so you could assign a small bufferpool to
frequently updated data, and one to tables used for lookups.

Using indexes may give you enormous speed gains if done right. Keep in mind
that indexes will consume some overhead time for inserts (eventually also
for updates, if the updated value belongs to an indexed column).
But indexing will change DB2's access strategies from sequential scanning
(table space scans) to more direct access forms (this is eventually not true
if it's a very small table, as the optimizer may consider a table space scan
faster on these (normaly he's right)).

The optimizer will only decide on facts known to him, so you should update
your db statistics from time to time. (if you have indexes).

But the most crucial speed gain can be gained by a good design of your data
base.

For testing purposes you may want to activate the show 'access plan' feature
in the command center.

Mike

[rwaddington]

As is coming out on this thread good database design is important. For
example we have nomalized data to a great degree , having information
occuring in only one table. But a certain batch run constantly
accessed 7 of the tables, and became very slow. We created a new
de-nomalized table containing rows from all seven of the previous
tables for the batch job to use. This new table speeded things up
greatly.

-Russell-
--
Posted via Talkway - http://www.talkway.com
Surf Usenet at home, on the road, and by email -- always at Talkway.

[norma...@poboxes.com]

Key performance tips: - put log files on different physical drive from data
and indexes - put index and associated table on different physical drives -
use as much memory as you can with buffer spaces without causing system
paging - for large tables that are scanned a lot, split it up on multiple
containers with different containers on different physical drives - after you
have built your indexes (more for read only, minimal amount if lots of
insert), run statistics! - if this is a small database, lower your
optimization level so that only nested loop joins are used...this reduces
time db2 thinks about how to access the data - cluster (sort) tables in the
order users order by ....

Norm

In article <nqnzfbsgsnopbz....@news.news.demon.net>,

"Adam Flinton" <ad...@softfab.com> wrote:
> Dear All,
>
> We are replacing an Access based system (complete with Local files / files
> held on a server) with a Db2 one.
>
> At the mo the users are a little unhappy re speed. We are using the JDBC
> drivers against Db2 V5.2 on NT. Having let one of them that knows a bit help
> design queries by letting him a an Install of the Control / Command Center on
> his desktop he's a little miffed at the speed vs Access.
>
> I take it that creating Indexes will help (not done so yet)? Any other
> obvious ploys? It should be serving 10 - 25 people (depending on time of
> day). The install is the basic "out of the Box" push here install at the mo
> with no optimisations at all.
>
> Adam
>
>

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[DOUG-...@worldnet.att.net]

In article <nqnzfbsgsnopbz....@news.news.demon.net>,
"Adam Flinton" <ad...@softfab.com> wrote:
> Dear All,
>
> We are replacing an Access based system (complete with Local files / files
> held on a server) with a Db2 one.
>
> At the mo the users are a little unhappy re speed. We are using the JDBC
> drivers against Db2 V5.2 on NT. Having let one of them that knows a bit help
> design queries by letting him a an Install of the Control / Command Center on
> his desktop he's a little miffed at the speed vs Access.
>
> I take it that creating Indexes will help (not done so yet)? Any other
> obvious ploys? It should be serving 10 - 25 people (depending on time of
> day). The install is the basic "out of the Box" push here install at the mo
> with no optimisations at all.
>
> Adam
>
>

Index, Index, Index, Index!