CODEWITHSUNDEEP
sqloracle-databasequery-performance
user9634982
user9634982

Reputation: 579

Reduce the query response time, need to optimise the query

I wrote a query which fetches the record of NULL DAYS (Occasion,weekoff,emergency holiday), later I implemented the same in GUI, so my admin can able to see the list, it will take minutes to load the data, even in my SQL developer.

How to reduce the execution time?

Here is the query

SELECT *
FROM
  (SELECT s.null_id ,
    STRING_AGG(DISTINCT s.city) city_id ,
    STRING_AGG(DISTINCT c.name) cityName ,
    STRING_AGG(DISTINCT s.location) location_id ,
    STRING_AGG(DISTINCT l.name) locationName ,
    STRING_AGG(DISTINCT s.sublocation) sublocation_id ,
    STRING_AGG(DISTINCT sl.name) sublocationName ,
    s.department ,
    s.fromtodate ,
    s.todate ,
    s.remark ,
    s.status ,
    s.update_date ,
    s.update_by ,
    s.delete_status ,
    s.update_by_name ,
    uu.name updatedBy ,
    row_number() OVER(ORDER BY s.null_id) rnum
  FROM nullday s
  LEFT OUTER JOIN userdetail uu
  ON s.update_by = uu.user_id
  LEFT OUTER JOIN city c
  ON ','
    || s.city
    || ',' LIKE '%,'
    ||c.CITY_ID
    ||',%'
  LEFT OUTER JOIN location l
  ON ','
    || s.location
    || ',' LIKE '%,'
    ||l.LOCATION_ID
    ||',%'
  LEFT OUTER JOIN sublocation sl
  ON ','
    || s.sublocation
    || ',' LIKE '%,'
    ||sl.SUBLOCATION_ID
    ||',%'
  WHERE s.null_id = s.null_id
  GROUP BY s.null_id,
    s.location,
    s.sublocation,
    s.department,
    s.fromtodate,
    s.todate,
    s.remark ,
    s.status,
    s.update_date,
    s.update_by,
    s.delete_status,
    s.update_by_name,
    uu.name
  ORDER BY s.fromtodate ASC
  ) mytbl
WHERE rnum < :max_val
AND rnum   > :min_val

I am not able to figure it out, whether the joins or LISTAGG takes the time to load the query.

NULLDAY Table data format

enter image description here

Upvotes: 0

Views: 87

Answers (1)

Miklos Karpati
Miklos Karpati

Reputation: 101

One of the significant issues here that you join tables on expressions, not on actual columns. This rules out the usage of an index that could potentially speed up the process.

LEFT OUTER JOIN city c
  ON ','
    || s.city
    || ',' LIKE '%,'
    ||c.CITY_ID
    ||',%'

A possible solution could be to create a temporary (or helper) table where you create separate rows using the comma-separated list and the original primary key. You can use that to retrieve the primary key faster.

A generic approach could be the following:

  • Can we break down long queries into smaller chunks?
  • Can we use temporary tables instead of the join expressions?
  • Can we design the temporary tables to replace LEFT JOINs with INNER JOINs?
  • Can we reduce the number of DISTINCTs?
  • Can we remove the ORDER BY?
  • Can we remove (or rework by using temporary tables) the row_number() OVER(ORDER BY s.null_id)?
  • Can we redesign the database to save data as it should be in a relational database? (Proper columns and records can be used in an index, comma-separated strings not.)

There's not much more I could suggest without knowing more details. I hope it helps.

Edit based on the data uploaded

You should extract the comma-separated lists and create proper one-to-many relationships. Use permanent tables if you are allowed to change the database structure, otherwise use temp tables when you rework your query.

Upvotes: 3

Related Questions