Performance Tuning SQL queries



IBM's flagship relational database management system

Performance Tuning SQL queries

Postby SANDHYA BUDHI » Tue Jul 22, 2008 11:24 am

Please suggest some tips for Performance Tuning the SQL queries in DB2
SANDHYA BUDHI
 
Posts: 19
Joined: Mon Jul 21, 2008 7:52 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Performance Tuning

Postby jayind » Tue Jul 22, 2008 2:46 pm

Hi Sandhya,

similar questions answered earlier in this forum, please go through those also.. you can find some info in the following link..

http://www.ibmmainframeforum.com/system-programming/topic475.html

Apart from the above, I have done performance tuning in my previous project which i am listing below..

1. Build indexes on tables whereever necessary if you are using search fields
2. Optimize the access path - Check with your DBA
3. look for unnecessary fetching of data by any query
4. WHERE clause in the queries is the place where you can improve performance a lot by giving appropriate values
5. Check for unnecessary loops related to data fetching
6. Check for data integrity
7. improper coding also sometimes cause for performance issues.. so code optimization is needed by a senior person

Hope this info helps...

Regards,
Jayind
jayind
 
Posts: 62
Joined: Wed Apr 23, 2008 1:37 pm
Location: Chennai, India
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Performance Tuning

Postby dick scherrer » Tue Jul 22, 2008 7:15 pm

Hello,

Please suggest some tips for Performance Tuning the SQL queries in DB2
Other than the very obvious (i.e. use a key to directly access data rather than a full table scan), production tuning involves identifying where time is being spent - then determining a better way to get the same result. Rather than waiting for productoin problems, this should be done as early as possible during development because often the "fix" requires some amount of re-design.

One of the biggest problems i see today is that many projects test with the barest possible data (which works acceptably) then when more realistic volumes are used, the unacceptable response time/run time is discovered.

To repeat: Tuning should not begin when there are critical production issues. . . Tuning should be part of the earliest design.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DB2 Performance Tuning

Postby jayind » Wed Jul 23, 2008 5:16 pm

In addition to my eariler posting,

8. RUNSTATS is another performance improvement task
9. Clearing the logs/cache part of weekly maintenance

I will continue adding to it if I remember few more...

Regards,
jayind
jayind
 
Posts: 62
Joined: Wed Apr 23, 2008 1:37 pm
Location: Chennai, India
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Performance Tuning

Postby SANDHYA BUDHI » Wed Jul 23, 2008 6:59 pm

In our project we are using dynamic SQL for retrieving the values and the condition for this SQL is set by the users in the front end.

In the Dynamic SQL the value in the where predicates will be assigned from the values passed through the front end
Select * from employee where
Name = '%%%%%%'
ID = '%%%%%' ;

This is causing problem when we dont specify any condition from the front end. It try getting all the values from the Table and it is creating a big perfrmamce issue..

Please let me know how can we procees with this..
SANDHYA BUDHI
 
Posts: 19
Joined: Mon Jul 21, 2008 7:52 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Performance Tuning

Postby dick scherrer » Thu Jul 24, 2008 4:01 am

Hello,

One thought is to reject the request if they do not provide some value.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DB2 Performance Tuning

Postby jayind » Thu Jul 24, 2008 11:45 am

The reason people do a blank search is either they dont know what value to enter, dont have information or just to see what values exist in the system...

I suggest

1. You can default values to the Name and ID if possible
2. Make those as mandatory fields on the screen and validate against the values entered


Regards,
jayind
jayind
 
Posts: 62
Joined: Wed Apr 23, 2008 1:37 pm
Location: Chennai, India
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Performance Tuning

Postby SANDHYA BUDHI » Thu Jul 24, 2008 4:18 pm

Thanks :)
SANDHYA BUDHI
 
Posts: 19
Joined: Mon Jul 21, 2008 7:52 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Performance Tuning

Postby SANDHYA BUDHI » Thu Jul 24, 2008 4:26 pm

We have declared a cursor and doing some process based on the value retrieved from the cursor and in the downstream process updating a table.

For doing updation we are locking the table in Exclusive mode.

We are hitting contention error -911 SQL code during locking. The table locking will be tried for 100 times and it fails even after trying for 100 times it will error out. After trying it for 10 times it locked the table successfully it does the update and doing a commit.

When I again come back and try to fetch the next record i am hitting -501. I have used WITH HOLD option in the cursor to remain OPEN.

Please help me regarding this.
SANDHYA BUDHI
 
Posts: 19
Joined: Mon Jul 21, 2008 7:52 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Performance Tuning

Postby dick scherrer » Thu Jul 24, 2008 9:57 pm

Hello,

Locking an entire table is not even permitted on many (most?) systems because of problems like you are experiencing and other impact problems. I've supported several hundred online/batch systems and none lock entire tables.

I'd suggest looking for a solution that locks only the needed rows and then only for a brief amount of time.

To do this may require considerable re-work. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post