CODEWITHSUNDEEP
sqloracle-database
user2321728
user2321728

Reputation: 1373

How to improve this SQL performance?

I made a query shown as below. The purpose is for a quick search for the orders. User inputs one keyword, system returns orders whose property matches that keyword. E.g. keyword = "pan", any order record related to "pan" should be returned, like order's country="Japan",or description = "This is a pan." or order's customer's name = "Pan", or there is a product named "pan" in the order.

ORDER_TABLE, ORDER_DESCRIPTION, ORDER_COUNTRY, these tables are one to one relationship. But ORDER_TABLE and ORDER_PRODUCT is one to many (10-100) relationship. 'ORDER_TABLE' has over 300,000 rows, so is ORDER_DESCRIPTION, ORDER_COUNTRY. ORDER_PRODUCT has about 1500,000 rows.

But it is extremely slow since database stores huge data. 

SELECT ot.id, od.description, oc.country_name 
    FROM       ORDER_TABLE ot
    LEFT JOIN  ORDER_DESCRIPTION od ON ot.id = od.id
    LEFT JOIN  ORDER_COUNTRY oc     ON ot.id = oc.id
    LEFT JOIN  ORDER_PRODUCT op     ON ot.id = op.id
    WHERE ot.delete!='Y' AND od.delete!='Y' AND ot.create_date >= SYSDATE - 90
    AND
    (
        ot.id like '%KEYWORD%'
        OR UPPER(od.description) LIKE '%KEYWORD%'
        OR UPPER(ot.version) LIKE '%KEYWORD%'
        OR UPPER(oc.country_name) LIKE '%KEYWORD%'
        ...(many columns are used to match that keyword)
        OR UPPER(op.product_name) LIKE '%KEYWORD%'
    )
    GROUP BY (...)
    ORDER BY ot.id

This query is extremely slow. Usually it takes 35sec to process. Any idea to improve it? I heard that multiple join is not a good idea. So I tried to use nested subquery, but no big improvement.

Thanks.

Upvotes: 0

Views: 88

Answers (1)

Bikash Pradhan
Bikash Pradhan

Reputation: 126

Try using the AND checks in where clause directly in join

SELECT ot.id, od.description, oc.country_name
FROM ORDER_TABLE ot
    LEFT JOIN  ORDER_DESCRIPTION od ON ot.id = od.id AND ot.delete!='Y' AND od.delete!='Y' AND ot.create_date >= SYSDATE - 90
    LEFT JOIN  ORDER_COUNTRY oc     ON ot.id = oc.id
    LEFT JOIN  ORDER_PRODUCT op     ON ot.id = op.id
    WHERE
    (
        ot.id like '%KEYWORD%'
        OR UPPER(od.description) LIKE '%KEYWORD%'
        OR UPPER(ot.version) LIKE '%KEYWORD%'
    OR UPPER(oc.country_name) LIKE '%KEYWORD%'
    ...(many columns are used to match that keyword)
    OR UPPER(op.product_name) LIKE '%KEYWORD%'
)
GROUP BY (...)
ORDER BY ot.id

Upvotes: 1

Related Questions