[Solved] Very slow query when using CONNECT BY LEVEL
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Very slow query when using CONNECT BY LEVEL

Database type:

NEEDS

I want to create a table like the following in Oracle SQL:

COUNTRY  NAME  WMWHSE_ID   DATE
   US     CRD2     1      040620
   GR     WAZ      2      040620
   CN     KOL      3      040620
   FR     DEL      4      040620
                  ...      ...
   US     CRD2     1      030620
   GR     WAZ      2      030620
   CN     KOL      3      030620
   FR     DEL      4      030620
                  ...      ...

Every warehouse in WMWHSE_ID will be printed with a DATE column for today's date, and this will be repeated for DATE = yesterday, the day before yesterday, the day before that...etc until exactly 1 week ago. I have 124 warehouses total, so 124*7 = 868 rows total.

===

QUERY BLOCK

The following is my query. It uses the CONNECT BY LEVEL <= to achieve the 7 dates (in theory)

select 
SUBSTR(db_alias, 1, 2) AS COUNTRY, 
db_alias as NAME, 
To_Number(Regexp_Replace(Db_Logid, '[^0-9]', '')) As Wmwhse_Id, 
to_char(sysdate, 'yyyyMMdd')+1-level as ACTDATE 
from wmsadmin.pl_db, dual where db_alias not like '%BPV' and  db_alias not like 'PRDO%' and db_alias not like 'ENTERPRISE'
connect by level <=7
order by ACTDATE desc, WMWHSE_ID asc

(The GROUP BY is needed, because without it the table looks like:)

COUNTRY  NAME  WMWHSE_ID   DATE
   US     CRD2     1      040620
   GR     WAZ      2      040620
   CN     KOL      3      040620
   FR     DEL      4      040620
                  ...      ...
   US     CRD2     1      030620
   US     CRD2     1      030620
   US     CRD2     1      030620
   US     CRD2     1      030620
                  ...      ...

===

THE ISSUE

The query time seems to grow exponentially with n in CONNECT BY LEVEL <= n. I ran some testing and got the following:

CONNECT BY LEVEL <= n      ROWS         SECONDS
         1                 124           2-6
         2                 248           10+?
         3                 372           110

Anything n=4 and up seems to just hang sqldeveloper completely. When n=7, I left the computer running for 30+ minutes and the query was still running.

What is causing this slowness? Is there a better way to go about implementing my table? Thanks for your time.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid LIKE Searches With Leading Wildcard (query line: 15): The database will not use an index when using like searches with a leading wildcard (e.g. '%BPV'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  2. Mixed Order By Directions Prevents Index Use (query line: 21): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
  3. Use Equality Operator Over LIKE (modified query below): Equality operators (such as '\u003d') are usually better optimized and more readable. Prefer the equality operator when searching for a constant value such as `ENTERPRISE`.
The optimized query:
SELECT
        SUBSTR(db_alias,
        1,
        2) AS COUNTRY,
        db_alias AS NAME,
        To_Number(Regexp_Replace(Db_Logid,
        '[^0-9]',
        '')) AS Wmwhse_Id,
        to_char(sysdate,
        'yyyyMMdd') + 1 - level AS ACTDATE 
    FROM
        wmsadmin.pl_db,
        dual 
    WHERE
        db_alias NOT LIKE '%BPV' 
        AND db_alias NOT LIKE 'PRDO%' 
        AND db_alias <> 'ENTERPRISE' CONNECT 
    BY
        level <= 7 
    ORDER BY
        ACTDATE DESC,
        WMWHSE_ID ASC

Related Articles



* original question posted on StackOverflow here.