Slow response of DB2 on multiple JOINs in query by JDBC

Toad for IBM DB2
1

I have 3 different tables (without any primary or foreign key) in DB2 database as

*    CREATE TABLE XW (
SRCLB VARCHAR(10) NOT NULL,
SRCFL VARCHAR(10) NOT NULL,
SRCMB VARCHAR(10) NOT NULL,
NAME VARCHAR(30) NOT NULL,
VARSQ DOUBLE NOT NULL,
UNIQUE (SRCLB, SRCFL, SRCMB, NAME, VARSQ) );
CREATE TABLE XO (
LOBJ VARCHAR(10) NOT NULL,
LTYPE VARCHAR(10) NOT NULL,
ATTR VARCHAR(10) NOT NULL,
LTEXT VARCHAR(50),
UNIQUE (LOBJ, LTYPE, ATTR) );
CREATE TABLE XM (
LIB VARCHAR(10) NOT NULL,
FILE VARCHAR(10) NOT NULL,
MBR VARCHAR(10) NOT NULL,
SEQ DOUBLE NOT NULL,
DTA VARCHAR(132),
RECN INTEGER,
UNIQUE (LIB, FILE, MBR, SEQ)
);*

Each table having 2 lacs (appx) records. When I execute this query


*        SELECT
DISTINCT XW.SRCMB
,XM.SEQ
,XM.DTA
,XM. FILE
,XM.LIB
,XO.TEXT
,XO.ATTR
FROM
(
XW INNER JOIN XM
ON (XW.VRECN = XM.RECN)
AND (XW.SRCMB = XM.MBR)
AND (
XW.SRCFL = XM. FILE
)
AND (XW.SRCLB = XM.LIB)
)
LEFT OUTER JOIN XO
ON (XW.SRCMB = XO.LOBJ)
WHERE
(XW.NAME = 'DB-NAME-A')
ORDER BY
XW.SRCMB
,XM.SEQ;*

It returns the result in more than 15 seconds. But when I specify more columns in WHERE condition like


*        SELECT
DISTINCT XW.SRCMB
,XM.SEQ
,XM.DTA
,XM. FILE
,XM.LIB
,XO.TEXT
,XO.ATTR
FROM
(
XW INNER JOIN XM
ON (XW.VRECN = XM.RECN)
AND (XW.SRCMB = XM.MBR)
AND (
XW.SRCFL = XM. FILE
)
AND (XW.SRCLB = XM.LIB)
)
LEFT OUTER JOIN XO
ON (XW.SRCMB = XO.LOBJ)
WHERE
(XW.NAME = 'DB-NAME-A')
AND XW.SRCMB = 'CLCR0751'
AND XW.SRCFL = 'CBSRC'
AND XW.SRCLB = 'THPCOD_NEW'
ORDER BY
XW.SRCMB
,XM.SEQ;*

Then the result comes in very quick time e.g. 2 seconds. Can you suggest me what are the flaws in my Tables/Queries?

And how can I improve the performance of 1st query?

Would, using the Stored Procedure in place of SQL query be an advantage in this case???

Thanks in advance

Kishore
2

You might need to interrogate the access plans of the two statements to determine why one yields a better response time. If you have not already done so, I would recommend downloading a trial of Toad DB2 LUW Xpert Edition. That suite contains the DB2 LUW SQL Optimizer which can be used to explain and/or rewrite SQL to determine the best performing SQL. For more details and to download a trial, please visit:

www.quest.com/toad-for-db2

Note that there might be other factors affecting response time as well (such as database activity and buffer-pool hit ratios) but starting the SQL Optimizer might be the way to go.