473,390 Members | 1,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

DB2 8.2 Performance drop on Query compared to 7.1

We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1 FP8).
For the most part, things are faster, but there is one query that is
much much slower, and it is a query that is used all the time.

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where S_PART_NUMBER like '%KJS%'

The widlcard before and after seems to be hosing it, but for this
particular piece of the application, this type of query is neccessary.

On Version 7.1, this query takes about 1.5 seconds (for 36 returns out
of 120,000 things in the table)
On Version 8.2, this query takes 13.8 seconds. (A copy of the production
database)
Most other queries are the same or faster.
I've improved my query by using

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where posstr(S_PART_NUMBER,'KJS')>0

This query is around 3.5 seconds. Much better, but still slower than
version 7.1 at 1.5 seconds

Is there anything I can do to improve that time to get it back to 1.5
seconds? What happened in DB2 8? I was reading one article that was
saying the optimization engine was changed such that 8 out of 10 queries
will be faster. Is the double wildcard one of the 2 that is slower?

DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
Optimization value of the default 5

Ken
Nov 12 '05 #1
11 2104
Ken,

Have you compared the explains (best db2exfmt) for the two queries?
This should be the starting point.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2
"73blazer" <yo**@ma.com> wrote in message
news:Lu********************@centurytel.net...
We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1
FP8).
For the most part, things are faster, but there is one query that is much
much slower, and it is a query that is used all the time.

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where S_PART_NUMBER like '%KJS%'

The widlcard before and after seems to be hosing it, but for this
particular piece of the application, this type of query is neccessary.

On Version 7.1, this query takes about 1.5 seconds (for 36 returns out of
120,000 things in the table)
On Version 8.2, this query takes 13.8 seconds. (A copy of the production
database)
Most other queries are the same or faster.
I've improved my query by using

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where posstr(S_PART_NUMBER,'KJS')>0

This query is around 3.5 seconds. Much better, but still slower than
version 7.1 at 1.5 seconds

Is there anything I can do to improve that time to get it back to 1.5
seconds? What happened in DB2 8? I was reading one article that was saying
the optimization engine was changed such that 8 out of 10 queries will be
faster. Is the double wildcard one of the 2 that is slower?

DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
Optimization value of the default 5

Ken


First, do a reorg of the table and all its indexes.

Then make sure you execute runstats and get distribution stats on key
columns and S_PART_NUMBER if it is not an indexed column.

If that does not help, you should evaluate the bufferpools and make sure the
index is in a similar buffer pool as V7 (with same objects as before). It is
possible that in your old system, the index was more likely to be resident
in the bufferpool and not flushed out by other pages.

Also, check the tablespace attributes (extent size, prefetch, etc) of the
index (assuming that S_PART_NUMBER is indexed).
Nov 12 '05 #3
Serge Rielau wrote:
Ken,

Have you compared the explains (best db2exfmt) for the two queries?
This should be the starting point.

Cheers
Serge

In looking at the exlplains for both, the optimized statment is the
same. Bufferpools match, optimization plan is the same, everything looks
ok. I don't see anything there out of wack. Just slower. The cost on 8.2
is 32458 compared to 78334 on 7.1, so shouldn't 7.1 be slower? CPU speed
is also slower on the 7.1 database. (The 8.2 database is a 4-way PWR5
AIX machine, the 7.1 database is a 4-way pwr3 machine, I run all the
statments from db2adm on the repective machine)
Is there anything special to look for in there.

Ken
Nov 12 '05 #4
Mark A wrote:
"73blazer" <yo**@ma.com> wrote in message
news:Lu********************@centurytel.net...
We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1
FP8).
For the most part, things are faster, but there is one query that is much
much slower, and it is a query that is used all the time.

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where S_PART_NUMBER like '%KJS%'

The widlcard before and after seems to be hosing it, but for this
particular piece of the application, this type of query is neccessary.

On Version 7.1, this query takes about 1.5 seconds (for 36 returns out of
120,000 things in the table)
On Version 8.2, this query takes 13.8 seconds. (A copy of the production
database)
Most other queries are the same or faster.
I've improved my query by using

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where posstr(S_PART_NUMBER,'KJS')>0

This query is around 3.5 seconds. Much better, but still slower than
version 7.1 at 1.5 seconds

Is there anything I can do to improve that time to get it back to 1.5
seconds? What happened in DB2 8? I was reading one article that was saying
the optimization engine was changed such that 8 out of 10 queries will be
faster. Is the double wildcard one of the 2 that is slower?

DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
Optimization value of the default 5

Ken

First, do a reorg of the table and all its indexes.

Then make sure you execute runstats and get distribution stats on key
columns and S_PART_NUMBER if it is not an indexed column.

If that does not help, you should evaluate the bufferpools and make sure the
index is in a similar buffer pool as V7 (with same objects as before). It is
possible that in your old system, the index was more likely to be resident
in the bufferpool and not flushed out by other pages.

Also, check the tablespace attributes (extent size, prefetch, etc) of the
index (assuming that S_PART_NUMBER is indexed).

I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.

Ken
Nov 12 '05 #5
"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...
I've done the runstats and reorg. The tablespace attributes are the same.
Bufferpools are exactly the same. indexing is the same.

Ken


I will assume you did the reorg first, then the runstats. Reorg and runstats
syntax is different between V7 and V8, so make sure you check the syntax and
make sure you reorg the indexes also (they automatically get reorged in
version 7).
Nov 12 '05 #6
Mark A wrote:
"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...
I've done the runstats and reorg. The tablespace attributes are the same.
Bufferpools are exactly the same. indexing is the same.

Ken

I will assume you did the reorg first, then the runstats. Reorg and runstats
syntax is different between V7 and V8, so make sure you check the syntax and
make sure you reorg the indexes also (they automatically get reorged in
version 7).

Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I didn't
know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.
Nov 12 '05 #7
73blazer wrote:
Mark A wrote:
"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...
I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.

Ken


I will assume you did the reorg first, then the runstats. Reorg and
runstats syntax is different between V7 and V8, so make sure you check
the syntax and make sure you reorg the indexes also (they
automatically get reorged in version 7).

Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I didn't
know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.

Can you post the two plans?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8
Serge Rielau wrote:
73blazer wrote:
Mark A wrote:
"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...

I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.

Ken


I will assume you did the reorg first, then the runstats. Reorg and
runstats syntax is different between V7 and V8, so make sure you
check the syntax and make sure you reorg the indexes also (they
automatically get reorged in version 7).

Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I
didn't know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.


Can you post the two plans?

Cheers
Serge

I was watching in nmon, and it seems on my 8.2 instance that 1 CPU is
being pegged out for the duration of the query. Where on the 7.2
machine, that doesn't seem to happen, but it's hard to tell there
because the query comes back very quickly

here are the db2exfmt outputs for the queries:

DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 07.02.0
SOURCE_NAME: SQLC2D01
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2005-04-14-19.10.54.089552
EXPLAIN_REQUESTER: PRDTST

Database Context:
----------------
Parallelism: None
CPU Speed: 6.691544e-07
Comm Speed: 0
Buffer Pool size: 200000
Sort Heap size: 647
Database Heap size: 3422
Lock List size: 1467
Maximum Lock List: 6
Average Applications: 50
Locks Available: 9946

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 28
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
select S_PART_NUMBER,S_TYPE
from physical.part_list
where S_PART_NUMBER like '%KEN%'
Optimized Statement:
-------------------
SELECT Q1.S_PART_NUMBER AS "S_PART_NUMBER", Q1.S_TYPE AS "S_TYPE"
FROM PHYSICAL.PART_LIST AS Q1
WHERE (Q1.S_PART_NUMBER LIKE '%KEN%')

Access Plan:
-----------
Total Cost: 78834.3
Query Degree: 1
NO TABLE INFORMATION AVAILABLE (ONLY EXPLAIN SNAPSHOT)

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 08.02.1
SOURCE_NAME: SQLC2E06
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2005-04-15-09.14.16.538703
EXPLAIN_REQUESTER: DB2864

Database Context:
----------------
Parallelism: None
CPU Speed: 6.723442e-07
Comm Speed: 100
Buffer Pool size: 200000
Sort Heap size: 647
Database Heap size: 3422
Lock List size: 1467
Maximum Lock List: 6
Average Applications: 1
Locks Available: 5633

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
select S_PART_NUMBER,S_TYPE
from physical.part_list
where S_PART_NUMBER like '%KEN%'
Optimized Statement:
-------------------
SELECT Q1.S_PART_NUMBER AS "S_PART_NUMBER", Q1.S_TYPE AS "S_TYPE"
FROM PHYSICAL.PART_LIST AS Q1
WHERE (Q1.S_PART_NUMBER LIKE '%KEN%')

Access Plan:
-----------
Total Cost: 28265.9
Query Degree: 1
NO TABLE INFORMATION AVAILABLE (ONLY EXPLAIN SNAPSHOT)
Nov 12 '05 #9
Serge Rielau wrote:
73blazer wrote:
Mark A wrote:
"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...

I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.

Ken


I will assume you did the reorg first, then the runstats. Reorg and
runstats syntax is different between V7 and V8, so make sure you
check the syntax and make sure you reorg the indexes also (they
automatically get reorged in version 7).

Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I
didn't know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.


Can you post the two plans?

Cheers
Serge

I am appreciating all the help.
It seems with all the table re-orgs and index re-orgs I've done now (I
didn't realize indexes are no longer done automatically in 8, so I did
them now) that my posstr method is down to .35 seconds. The %STRING%
method is still 11.7 seconds.
0.35, I'm very happy. But still wondering why the like '%STRING%' is
drastically slower on 8.

Ken
Nov 12 '05 #10
? Well, how can we state that the optimizer plan is the same if it
wasn't dumped?

Let's start from scratch:
1. ensure you have the explain tables:
cd sqllib/misc
db2 connect to <db>
db2 -tvf EXPLAIN.DDL
now:
db2 explain plan for <sqlstatement>
db2exfmt -d <db> -o <filename>.exfmt
<accept all defaults>

You should see a graph :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #11
"73blazer" <yo**@ma.com> wrote in message
news:09********************@centurytel.net...
I am appreciating all the help.
It seems with all the table re-orgs and index re-orgs I've done now (I
didn't realize indexes are no longer done automatically in 8, so I did
them now) that my posstr method is down to .35 seconds. The %STRING%
method is still 11.7 seconds.
0.35, I'm very happy. But still wondering why the like '%STRING%' is
drastically slower on 8.

Ken


When you reorg the table, do you have a clustered index defined, or do you
specify an index name in the reorg command?
Nov 12 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: NDS Ltd | last post by:
hi, i have a website built with php / mysql the database seems to take about 30 seconds to return the result of a query. the query is "select * from table where record_id=$record_id" I...
8
by: Jean-Christian Imbeault | last post by:
I'm trying to convince another open-source project (phpOpenTracker) to modify their current INSERT sql queries. Currently they just do an INSERT into a table without first checking if their might...
1
by: Evan Smith | last post by:
My database is suffering from poor performance of late. Reports that used to run in a reasonable time, now take a while. The explain output show that the query is fully indexed, and the statistics...
5
by: Paul Reddin | last post by:
Hi, We have noticed some of our complex queries run much faster when connected as the instance owner, compared to a userA who has explcitly granted privileges on all the required Tables & SQL...
16
by: David W. Fenton | last post by:
http://www.granite.ab.ca/access/performancefaq.htm I hope Tony doesn't mind my opening a discussion of some issues on his performance FAQ page here in the newsgroup. This is not meant as...
4
by: Bob Alston | last post by:
Some more, rather specific Access performance questions. IN a split front-end & back-end Access/Jet ONLY LAN situation, and with all query criteria fields and join fields indexed: 1. Is is...
15
by: Egyd Csaba | last post by:
Hi All, I've a problem with the perfprmance of the production environment. I've two db servers. One on my laptop computer (2Ghz, 1GB, WinXP, Cygwin, Postgres 7.3.4) and one on a production server...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
2
by: AAAAA | last post by:
Hi guys I have a one question, is Linq to Sql good in pefornace? is better that traditional method? ismore than speed?? or is same? Thanks.. Regards Cesar
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.