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 11 2109
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
"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).
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
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
"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).
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.
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
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)
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
? 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
"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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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...
|
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...
|
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...
|
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...
| |
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...
|
by: Együd 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...
|
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...
|
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
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |