Hi,
I have a table with 10-12 columns which adds around 10-15k rows daily. I have a procedure which has simple select statement (has order by one column) with a date filter. Now the proc takes around 1.5- 2 min to give the complete result set.(10-15k rows) .

Is there any way by which i can improve the performance of this query.

Thanks

Create an index on the date column.

Andy

After that, do not forget to do runstats and rebind the SP.

Andy

Its already there.....and statistics are updated and even rebind the sp after that...

Please supply the following:

DB2 version and fixpack level
OS
the SP definition
the ddl for all the tables in the select.
The index definition for the above tables
the access plan for the SP.

Andy

Is partitioning by date an option? And make sure that other column's index is the clustering index.
That way you turn your query essentially into a single partition scan.

Below are the deatils:
DB2 Version: DB2 ESE V 9.1.5
OS: Linux
SP:
CREATE PROCEDURE MSPA_COMMON.TEST(IN IN_REPORT_DATE DATE,
IN IN_USER_ID VARCHAR(9))
SPECIFIC MSPA_COMMON.TEST
RESULT SETS 1
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
BEGIN

--------------------------------------------------------------------------------------------------------------------
-- DECLARE VARIABLES
--------------------------------------------------------------------------------------------------------------------

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE V_SQLCODE INTEGER DEFAULT 0;
DECLARE V_SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE V_TRACKER CHAR(50);
DECLARE V_EXIT_MESSAGE CHAR(70);

-------------------------------------------------------------------------------------------------
-- DECLARE THE EXIT HANDLER FOR THE SQLEXCEPTION
-------------------------------------------------------------------------------------------------


DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
VALUES (SQLCODE,SQLSTATE) INTO V_SQLCODE,V_SQLSTATE;
SET V_EXIT_MESSAGE = 'ERROR:'|| TRIM(V_SQLSTATE) || ',' || TRIM(CHAR(V_SQLCODE)) || ' AT ' || TRIM(V_TRACKER);
SIGNAL SQLSTATE VALUE V_SQLSTATE SET MESSAGE_TEXT = V_EXIT_MESSAGE;
END ;




RS:BEGIN

DECLARE CUR_POS_PROD CURSOR WITH RETURN FOR
SELECT REPORT_DATE,
NAME_ID,
NAME,
TRIM(ACCOUNT_ID),
TOTAL_VAL,
TOTAL_COUNT,
A_TOTAL_VAL,
A_COUNT,
B_VAL,
B_COUNT,
C_VAL,
c_COUNT,
D_VAL,
D_COUNT,
E_VAL,
E_COUNT,
F_VAL,
F_COUNT,
G_VAL,
G_COUNT,
H_VAL,
H_COUNT,
I_VAL,
I_COUNT,
J_VAL,
J_COUNT,
K_VAL,
K_COUNT,
L_VAL,
L_COUNT,
M_VAL,
M_COUNT,
N_VAL,
N_COUNT
FROM MSPA_COMMON.TEST
WHERE REPORT_DATE = IN_REPORT_DATE
ORDER BY NAME_ID
FOR FETCH ONLY ;

OPEN CUR_POS_PROD;

END RS;


END
;

Table DDL:
CREATE TABLE MSPA_COMMON.TEST
(
REPORT_DATE DATE,
NAME_ID VARCHAR(50),
NAME VARCHAR(120),
ACCOUNT_ID CHARACTER(50),
TOTAL_VAL DECIMAL(26,4),
TOTAL_COUNT BIGINT,
A_TOTAL_VAL DECIMAL(26,4),
A_COUNT BIGINT,
B_VAL DECIMAL(26,4),
B_COUNT BIGINT,
C_VAL DECIMAL(26,4),
c_COUNT BIGINT,
D_VAL DECIMAL(26,4),
D_COUNT BIGINT,
E_VAL DECIMAL(26,4),
E_COUNT BIGINT,
F_VAL DECIMAL(26,4),
F_COUNT BIGINT,
G_VAL DECIMAL(26,4),
G_COUNT BIGINT,
H_VAL DECIMAL(26,4),
H_COUNT BIGINT,
I_VAL DECIMAL(26,4),
I_COUNT BIGINT,
J_VAL DECIMAL(26,4),
J_COUNT BIGINT,
K_VAL DECIMAL(26,4),
K_COUNT BIGINT,
L_VAL DECIMAL(26,4),
L_COUNT BIGINT,
M_VAL DECIMAL(26,4),
M_COUNT BIGINT,
N_VAL DECIMAL(26,4),
N_COUNT BIGINT
);

CREATE INDEX MSPA_COMMON.IDX_C_TEST
ON MSPA_COMMON.TEST(REPORT_DATE,NAME_ID)
CLUSTER
ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC
;

Access Plan:
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** PACKAGE ***************************************

Package Name = "MSPA_COMMON"."P3291938" Version = ""

Prep Date = 2009/02/04
Prep Time = 03:29:19

Bind Timestamp = 2009-02-04-03.29.19.390826

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"RAJESHAG"

-------------------- SECTION ---------------------------------------
Section = 1


Statement:
DECLARE CUR_POS_PROD CURSOR
WITH RETURN
FOR
SELECT REPORT_DATE,
NAME_ID,
NAME,
TRIM(ACCOUNT_ID),

TOTAL_VAL,
TOTAL_COUNT,
A_TOTAL_VAL,
A_COUNT,
B_VAL,

B_COUNT,
C_VAL,
C_COUNT,
D_VAL,
D_COUNT,
E_VAL,

E_COUNT,
F_VAL,
F_COUNT,
G_VAL,
G_COUNT,
H_VAL,

H_COUNT,
I_VAL,
I_COUNT,
J_VAL,
J_COUNT,
K_VAL,

K_COUNT,
L_VAL,
L_COUNT,
M_VAL,
M_COUNT,
N_VAL,
N_COUNT
FROM MSPA_COMMON.TEST
WHERE REPORT_DATE =:HV00008
:HI00008
ORDER BY NAME_ID
FOR
FETCH ONLY


Section Code Page = 1208

Estimated Cost = 686.985962
Estimated Cardinality = 2425.800049

Access Table Name = MSPA_COMMON.TEST ID = 5,8202
| Index Scan: Name = MSPA_COMMON.IDX_C_TEST ID = 1
| | Regular Index (Clustered)
| | Index Columns:
| | | 1: REPORT_DATE (Ascending)
| | | 2: NAME_ID (Ascending)
| #Columns = 0
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: Inclusive Value
| | | | 1: ?
| Index-Only Access
| Index Prefetch: Eligible 65
Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 2426.000000
| | | | Row Width = 16
| | | Piped
| | | Duplicate Elimination
Sorted Temp Table Completion ID = t1
List Prefetch Preparation
| Access Table Name = MSPA_COMMON.TEST ID = 5,8202
| | #Columns = 33
| | Fetch Using Prefetched List
| | | Prefetch: 730 Pages
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
| | | Insert Into Sorted Temp Table ID = t2
| | | | #Columns = 33
| | | | #Sort Key Columns = 1
| | | | | Key 1: NAME_ID (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 2426.000000
| | | | | Row Width = 540
| | | | Piped
Sorted Temp Table Completion ID = t2
Access Temp Table ID = t2
| #Columns = 33
| Relation Scan
| | Prefetch: Eligible
Return Data to Application
| #Columns = 34

End of section


Optimizer Plan:

RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
FETCH
(----)
/ \
RIDSCN Table:
( 5) MSPA_COMMON
| TEST
SORT
( 6)
|
IXSCAN
( 7)
/ \
Index: Table:
MSPA_COMMON MSPA_COMMON
IDX_C_TEST TEST


-------------------- SECTION ---------------------------------------
Section = 2


Statement:
VALUES (:HV00000 :HI00000 , :HV00001 :HI00001 )INTO :HV00010
:HI00010 , :HV00011 :HI00011


Section Code Page = 1208

Estimated Cost = 0.000028
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Return Data to Application
| #Columns = 2

End of section


Optimizer Plan:

RETURN
( 1)
|
TBSCAN
( 2)
|
TFunc:
SYSIBM
GENROW


-------------------- SECTION ---------------------------------------
Section = 3


Statement:
-------------------- SECTION ---------------------------------------
Section = 2


Statement:
VALUES (:HV00000 :HI00000 , :HV00001 :HI00001 )INTO :HV00010
:HI00010 , :HV00011 :HI00011


Section Code Page = 1208

Estimated Cost = 0.000028
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Return Data to Application
| #Columns = 2

End of section


Optimizer Plan:

RETURN
( 1)
|
TBSCAN
( 2)
|
TFunc:
SYSIBM
GENROW


-------------------- SECTION ---------------------------------------
Section = 3


Statement:
VALUES('ERROR:' || TRIM(:HV00011 :HI00011 )|| ', ' || TRIM(CHAR(
:HV00010 :HI00010 ))|| ' AT ' || TRIM(:HV00012 :HI00012 ))
INTO :HV00013 :HI00013


Section Code Page = 1208

Estimated Cost = 0.000239
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Return Data to Application
| #Columns = 1

End of section


Optimizer Plan:

RETURN
( 1)
|
TBSCAN
( 2)
|
TFunc:
SYSIBM
GENROW

You access plan look very good. How many rows are in the table? When was the last reorg done?

Andy

Around 70k as of now...recently created the table...

Try doing a reorg and see if that helps.

Andy

Also reorg the indexes.
After the reorg, do not forget to do runstats and rebind.

Something does not look right here. DB2 has its quirks, but two minutes for 70K records...

How do you measure the elapsed time? Does the procedure run in isolation?

I would also verify that there are no lock waits involved.

I agree. 70K rows is hardly worth talking about. Each row is about 500-600 bytes long (I didn't count exactly). With a 4K tablespace, you will get 6-7 rows per page, which means you have a mere 12K pages and just 48MB memory. All this should be in the buffer pool anyway (or will be after prefetching). You should get subsecond response time. Also, even if there is a table scan, that should be much faster than 2min.

There are no lock waits...

I tested one more thing....I just redirected the result to a file and it completed within a second..So that means most of the time is spent in result io....

How can I improve that?

Thanks,
Rajesh

Don't write the output to the console.