Need advice to optimize this query
in [DB2]
|
Prev: DDL file usage
Next: DB2 transaction problem
From: xeqister on 2 Dec 2009 05:10 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 !!
From: Luuk on 2 Dec 2009 07:23 xeqister schreef: > 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 !! 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 is a lot more readible then your query.... -- Luuk
From: Serge Rielau on 2 Dec 2009 08:39 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
From: --CELKO-- on 3 Dec 2009 18:05 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;
From: dunleav1 on 7 Dec 2009 13:41
On Dec 2, 5:10 am, xeqister <xeqis...(a)gmail.com> wrote: > 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 !! Post the explain plan for the query. |