Urgent!! Peformance Optimization 3

[shuchis]

Hello Frinds,
I am getting a major performace hit in my Stored Procedures.
Actually the story started from writing stored procedure for reporting requirements.
We started with writing big sqls in stored procedures. But ultimately Our SPs were taking long time to get executed. Then we tried using temporary tables in Db2 Stored procedures. They seemed to work but when we tested new SPs on live data, again we got a hit.
Now we are again thinking whihc way to go - whether Temporary tables or no temp Tables.
I know it would be a mix and match from case to case. But can anybosy give me some Tips and Tricks to wrire optimized Stored Procedures or Queries...

Thanks in Advance
Shuchi

 

[sathyarams]

First thing(s) first , RUNSTATS and REBIND (I mean both, in the same order) of all tables , indexes and and packages

Define proper indexes on tables (clustering/asc-desc/allow reverse scans , INCLUDE Columns) etc

Check your bufferpool size . Also, the averge number of applications

See if you can move your temp space to a different disk than your current table

Are you using DMS ? If not, consider using DMS

Define your extentsize and prefetch size to reflect your environment

Get your NUMIOCLEANERS and NUMIOSERVERS to an optimumm value

Avoid sorts as much as possible
- Clustered indexing will help
- Use UNION ALL instead of UNION if you can
- Avoid unnecessary ORDER BY Clauses



and .... ..........

The above list is in random order ... Depending on your circumstances, their impact may vary ...

Google for 'db2 sql performance' and you will find a number of articles .. Your best online resources for performance tuning are
1)

http://www7b.boulder.ibm.com/dmdd

2)

http://www.db2mag.com

3)

http://www.dbazine.com

4)

http://www.idug.org

(solutions journal)
5)

http://www.redbooks.ibm.com

and of course

http://www.ibm.com/db2/library

..

You should also use one of the Explain tools to understand what your SQLs do internally ... For a beginner, Visual Explain is a useful tool ... For command line geeks, db2exfmt, db2expln and dynexpln ...

And, if you can post more details, like which query causes problems, what is table and index structure, number of rows etc, someone in this forum may give ideas ...

You have not mentioned the platform either ... Some of my suggestions apply to all (except as400) db2 platforms ... Some apply only to Unix and windows ...

Please make it a point to post the environment information always ...

Cheers
Sathyaram



For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[balajipn]

Hi,

Couple of things you might have to take care.

1. REPAIR, REBUILD INDEX, RUNSTATS - This should mostly help.
2. Define proper indexes. Do not define indices on unnecessary columns. Having more number of indices than required will drag your performance.
3. I did not use SPs or TEMP tables till now. So no comments on these.
4. Better to have one table per tablespace.
5. Go for partitioned tablespaces. This will improve the performance.
6. If you are very sure about a column, do not incorporate any constraint on it (like lastname where everyone is advised to enter something).

Thanks,
Balaji.