How to improve the performance- fine tunning?

vinay_care · New User Joined: 22 Sep 2006 Posts: 17

HI,

I have a simple query retrieving all the records using a cursor from a particular table without a where clause. ex:
EXEC SQL DECLARE STORE-CURSOR CURSOR WITH HOLD FOR
SELECT STORE,
COUNTRY_CODE
CLIMATE_CODE,
FROM STORE
END-EXEC.
and followed by relevant fetch and close cursor.
The table is indexed on STORE KEY,
what I want to know is, ---Does it makes any sense or improves the performance using additional where clause, and what where clause am I suppose to use and how?
As such the query is taking a long time. Plz do SUGGEST.

enrico-sorichetti · Posted: Thu Jul 05, 2007 8:09 pm

impossible to reply without knowing the underlaying business/application requirements/specifications.

regards

e.s

dick scherrer · Posted: Thu Jul 05, 2007 8:27 pm

Hello,

If the requirement is to process every row in the table, the time it will take will vary with the volume of data in the table. What is the indication that this needs "tuning"?

If you post the actual requirement (not the existing query), we may be able to offer suggestions.

vijikesavan · Posted: Thu Jul 05, 2007 8:37 pm

Hi,
As everyone said, its really difficult to give you the solution you are looking for.
I faced something like this before. If you want to select all the rows in the table, you can try one of these options
1) Check if there is any BMC or unload copies already exists for the table. You can use the file as input instead of making DB2 calls.
2) Sometimes just a rebind of the program or re-org of the table might help.
3) Try adding where clause or any new indexes after discussing with your DBA, business and other ppl who knows the application.

These are general tips for you to try, not sure of the results.

Thanks,
Viji

wanderer · Posted: Thu Jul 05, 2007 10:30 pm

Yes, it will improve performance using WHERE clause with STORE KEY.

But then, if you need to see all the rows in table anyway, you can't use WHERE clause.

prasadvrk · Posted: Fri Jul 06, 2007 6:23 pm

TG Murphy · Posted: Fri Jul 06, 2007 7:58 pm

Your SQL is so simple that I fear there's nothing much you can do.

When we need to tune an SQL statement, it generally looks like a monster. For example, 8 table joins with correlated subqueries. It can take hours to analyse the SQL - examine the EXPLAIN access path - consider alternatives.

When I look at your SQL it takes me about 2 seconds to conclude that not much can be done to the SQL itself to improve on things. Any improvements will have to come - as was recommened above - by tweaking the data (ie. reorg or purging dead data).

dick scherrer · Posted: Fri Jul 06, 2007 8:17 pm

Hello,

To repeat

vinay_care · New User Joined: 22 Sep 2006 Posts: 17

dick scherrer · Posted: Tue Jul 10, 2007 7:07 pm

Hello,

If your requirement is to use all of the rows in a table, there is not much to tune within the database.

If you have many millions of rows, you might unload the data and run your process(es) sequentially and not read the data via sql.

There is no way to define one or more new keys to speed up a full traversal.

It would be a good idea to test how long the unload/sequential run would take versus using sql. If the times are even similar, you might want to use the sequential appraoch as it could remove the unacceptable amount of processing from db2 and make other processes run better by reducing the impact of this large process.