Toad World® Forums

Slow response of DB2 on multiple JOINs in query by JDBC

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

 
 
 

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.