Need advice to optimize this query

[xeqister]

Hi,
We are facing performance issue with the following query. Is there
anyway that we can optimize it? Can someone advise what type of index
can be created on which column.

SELECT DISTINCT A.WFNODEID, A.WFNODEDT, C.WFSEQNUM,
D.NODEDESC,C.WFUSERNAME FROM PANTON.WFINODES AS A, PANTON.FMEDIA AS
B, PANTON.WFHNDR AS C, PANTON.NODE AS D WHERE A.WFDATNODEID =
B.FMNODEID AND A.WFDATNODEDT = B.FMNODEDT AND A.WFDATGRAPHID =
B.FMGRAPHID AND UPPER(A.WFDATANODEDESC) LIKE UPPER('%') AND UPPER
(B.FMFOFNAME) LIKE UPPER('RL_SP0%') AND A.WFNODEID = C.WFNODEID AND
A.WFNODEDT = C.WFNODEDT AND ((C.WFSTATUS = 'I') OR (C.WFSTATUS = 'R')
OR (C.WFSTATUS = 'S') OR (C.WFSTATUS = 'Q') OR (C.WFSTATUS = 'V')) AND
C.WFNODEID = D.NODEID AND C.WFNODEDT = D.NODEDT ORDER BY D.NODEDESC,
C.WFSEQNUMquery

Thanks in advance !!

[Luuk]

xeqister schreef:

AND UPPER(B.FMFOFNAME) LIKE UPPER('RL_SP0%')

AND A.WFNODEID = C.WFNODEID
AND A.WFNODEDT = C.WFNODEDT
AND ((C.WFSTATUS = 'I')
OR (C.WFSTATUS = 'R')
OR (C.WFSTATUS = 'S')
OR (C.WFSTATUS = 'Q')
OR (C.WFSTATUS = 'V'))
AND C.WFNODEID = D.NODEID
AND C.WFNODEDT = D.NODEDT
ORDER BY
D.NODEDESC,
C.WFSEQNUMquery

is a lot more readible then your query....

--
Luuk

[Serge Rielau]

AND UPPER(A.WFDATANODEDESC) LIKE UPPER('%')
AND UPPER(B.FMFOFNAME) LIKE UPPER('RL_SP0%')

... not so good.

In general having an expression on the column will prevent index
exploitation.
Now, what LIKE UPPER('%') is meant to be is beyond me..
To me this is an equivalent to
A.WFDATANODEDESC IS NOT NULL

You may consider adding generated columns for the most important columns
you do case-insensitive search for and index those.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect. It ought to be formatted so humans can read it.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

1) Unless you have only ONE node, then you want to name that table
Nodes.
2)Why did you think alphabetical aliases are a good idea?
3)Did you really need SELECT DISTINCT? If you have identifiers in the
select list, this is not needed – unless the schema is a mess
4)How is a “wf_node_xx” data elemen5s logically different from a plain
old “node_xx”? I have a feeling that you might have attribute
splitting in the schema.
5)Think about what UPPER{'%') means. Think what “x LIKE '%'” means.
To quote Homer Simpson, “DOH!”
6)SQL Programmers use IN() and not a string of ORs; this is not BASIC,
FORTRAN or COBOL.
7)Never use UPPER() like that; put this into the DDL with CHECK()
constraints

I tried to clean it up, but without DDL, there is no way to help you.
All we can do is guess. Here is mine:

SELECT N.wf_node_id, N.wf_node_dt, H.wf_seq, N.node_like_desc,
H.wf_user_name
FROM Panton.WFI_Nodes AS N,
Panton.Fmedia AS FM,
Panton.WF_Hndr AS H,
Panton.Nodes AS N
WHERE N.wf_dat_node_id = F.fm_node_id
AND N.wf_dat_node_dt = F.fm_node_dt
AND N.wf_dat_graph_id = F.fm_graph_id
AND FM.fmfo_fname LIKE 'RL_SP0%'
AND N.wf_node_id = H.wf_node_id
AND N.wf_node_dt = H.wf_node_dt
AND H.wf_status IN ('R', 'S', 'Q', 'V')
AND H.wf_node_id = N.node_id
AND H.wf_node_dt = N.node_dt;

[dunleav1]

Post the explain plan for the query.

[The Boss]

--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. If you know how, follow ISO-11179 data element naming
> conventions and formatting rules. Temporal data should use ISO-8601
> formats. Code should be in Standard SQL as much as possible and not
> local dialect. It ought to be formatted so humans can read it.

While I agree with your sentiment in general, I don't think it is a sin to
use DB2's dialect in a DB2 dedicated group

Why did you think duplicate aliases are a good idea? ...
Or, for the same table, using a different alias in the WHERE-clause as
opposed to the FROM-clause?

;-)

--
Jeroen