Query rewrite and Tuning

Question

I have the below query that is the cause of performance degradation, can someone help to rewrite this query so that it could perform better.

SELECT * 
FROM   (SELECT ITEMDETAIL0_.depid AS COL_0_0_ 
        FROM   xxxxxxxxxx.rptdepitemdetails ITEMDETAIL0_ 
               cross join xxxxxxxxxx.xxxxxxxxxxorg ORGANIZATI1_ 
        WHERE  ITEMDETAIL0_.bankname =? 
               AND ITEMDETAIL0_.customerid = ORGANIZATI1_.id 
               AND ORGANIZATI1_.uniquename =? 
               AND ITEMDETAIL0_.createuser =? 
               AND ITEMDETAIL0_.createdate >=? 
               AND ITEMDETAIL0_.createdate <=? 
               AND ( ITEMDETAIL0_.captureworktype IN ( ? ) ) 
               AND ( ITEMDETAIL0_.depstatus IN ( ?, ? ) ) 
        GROUP  BY ITEMDETAIL0_.depid, 
                  ITEMDETAIL0_.createdate 
        ORDER  BY ITEMDETAIL0_.createdate ASC) 
WHERE  ROWNUM <= ?

(1) Tag your question with the database you are using. (2) Why are you expressing a `JOIN` as a `CROSS JOIN`? Use the `ON` clause! — Gordon Linoff, Dec 03 '19 at 12:48
You need to tell what you want to achieve. Looking at your query i can say making `CROSS` join is anyways going to take time — XING, Dec 03 '19 at 12:49
Many times query tunning relies not upon rewriting the query but in normalizing your data correctly and the smart use of indexes. — jean, Dec 03 '19 at 13:15
Could you add the explain plan of your query? It will be much easier to diagnose your problem correctly. — Jaisus, Dec 03 '19 at 15:22
@Gordon Linoff - can you plz help me as suggested to use ON instead of Cross Join — Dothertechie, Dec 03 '19 at 17:45

score 0 · Answer 1 · edited Dec 04 '19 at 13:23

You can write query as follow to avoid cross join and using ON.

SELECT * 
FROM   (SELECT ITEMDETAIL0_.depid AS COL_0_0_ 
        FROM   xxxxxxxxxx.rptdepitemdetails ITEMDETAIL0_ 
               join xxxxxxxxxx.xxxxxxxxxxorg ORGANIZATI1_  on ITEMDETAIL0_.customerid = ORGANIZATI1_.id 
        WHERE  ITEMDETAIL0_.bankname =? 
               AND ORGANIZATI1_.uniquename =? 
               AND ITEMDETAIL0_.createuser =? 
               AND ITEMDETAIL0_.createdate >=? 
               AND ITEMDETAIL0_.createdate <=? 
               AND ( ITEMDETAIL0_.captureworktype IN ( ? ) ) 
               AND ( ITEMDETAIL0_.depstatus IN ( ?, ? ) ) 
        GROUP  BY ITEMDETAIL0_.depid, 
                  ITEMDETAIL0_.createdate 
        ORDER  BY ITEMDETAIL0_.createdate ASC) 
WHERE  ROWNUM <= ?

Also, creating indexes on id column of xxxxxxxxxxorg and customerid and depid of rptdepitemdetails tables would help.

I've formatted your SQL code for you. That's very easy to do by the way: just highlight your query and press the `{}` button. — Thorsten Kettner, Dec 04 '19 at 11:24

Query rewrite and Tuning

1 Answers1