Performance Problems with ORDER BY

[Ibach J.]

Hi,

i have a performance problem with the following query:

SELECT IMMAND,IPROD,IDESC IIM WHERE IMMAND = 188 ORDER BY IMMAND,
IPROD

I user DB2 V 8.1
The table IIM contains about 200000 records (193000 record with IMMAND
= 188).
Primary key is IMMAND, IPROD.

When im running the query it will take about 45 seconds to get the
first 50 records. I tried the following query

SELECT IMMAND,IPROD,IDESC IIM WHERE IMMAND = 188 AND IPROD > '' ORDER
BY IMMAND, IPROD

This time the query used only 0,5 seconds to give me the first 50
records.

My Question is,
what is the problem with the first query.
Any hints are great.

Juergen Ibach

[Blair Adamache]

Do you have up to date statistics on the table and its indexes? Is there
an index on IPROD? Can you have a unique index on IMMAND?

Ibach J. wrote:

Hi,

i have a performance problem with the following query:

SELECT IMMAND,IPROD,IDESC IIM WHERE IMMAND = 188 ORDER BY IMMAND,
IPROD

I user DB2 V 8.1
The table IIM contains about 200000 records (193000 record with IMMAND
= 188).
Primary key is IMMAND, IPROD.

When im running the query it will take about 45 seconds to get the
first 50 records. I tried the following query

SELECT IMMAND,IPROD,IDESC IIM WHERE IMMAND = 188 AND IPROD > '' ORDER
BY IMMAND, IPROD

This time the query used only 0,5 seconds to give me the first 50
records.

My Question is,
what is the problem with the first query.
Any hints are great.

Juergen Ibach

[PM \(pm3iinc-nospam\) CGO]

Is IPROD > '' a good filter factor?

Check your access plan, sort parameters and take a few snapshots to get more
info.

PM

"Ibach J." <ji****@guskoeln.de> a écrit dans le message de
news:40**************************@posting.google.c om...

Hi,

i have a performance problem with the following query:

SELECT IMMAND,IPROD,IDESC IIM WHERE IMMAND = 188 ORDER BY IMMAND,
IPROD

I user DB2 V 8.1
The table IIM contains about 200000 records (193000 record with IMMAND
= 188).
Primary key is IMMAND, IPROD.

When im running the query it will take about 45 seconds to get the
first 50 records. I tried the following query

SELECT IMMAND,IPROD,IDESC IIM WHERE IMMAND = 188 AND IPROD > '' ORDER
BY IMMAND, IPROD

This time the query used only 0,5 seconds to give me the first 50
records.

My Question is,
what is the problem with the first query.
Any hints are great.

Juergen Ibach