CODEWITHSUNDEEP
sqloracle-database
user12323380
user12323380

Reputation:

How to optimize SELECT query?

I have a table that houses all of the logs of the system. It houses the insertion and update logs of an item. My query checks the latest updates based on the last time the system checked for updates.

Here is my query:

SELECT LCINDIV.CLIENTID, LCINDIV.COID, BIRTHDAY, NATIONAL, ACRNO, CIVSTAT, GENDER, RELIGION, SSSID, TIN, RESCERT, EDUCLV, DEGREE, RESIDENT, LNAME, FNAME, MNAME, DTPLRES, NATIONLT, LCINDIV.SERVERUPDATE
FROM LCINDIV
LEFT OUTER JOIN LCCLIENT
    ON LCCLIENT.CLIENTID = LCINDIV.CLIENTID
WHERE
   AUDITKY IN (SELECT AUDITKY FROM LSAUDIT WHERE ENTRYDT > '04-Nov-2019' AND ENTRYTM > '15:15:10')

Here is what my query does it will get the client information if the AUDITKY is in the AUDITKY of the table LSAUDIT WHERE the ENTRYDT is greater than the current date and ENTRYTM is greater than the current time.

My problem is when I execute this query it takes about 30-60 seconds to execute. Is there a way for me to improve my SQL Query?

Table Specs:
LCCLIENT - 5,000+ rows
LCINDIV - 5,000+ rows
LSUDIT - 5,000,000+ rows

Table Structure:

LSAUDIT Table

AUDITNO CHAR
AUDITTP CHAR
AUDITKY CHAR
ENTRYBY CHAR
ENTRYDT DATE
ENTRYTM CHAR

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would go for EXISTS. Note that your LEFT JOIN is really an INNER JOIN, so:

SELECT LCINDIV.CLIENTID, LCINDIV.COID, BIRTHDAY, NATIONAL, ACRNO,
       CIVSTAT, GENDER, RELIGION, SSSID, TIN, RESCERT, EDUCLV,
       DEGREE, RESIDENT, LNAME, FNAME, MNAME, DTPLRES, NATIONLT,
       LCINDIV.SERVERUPDATE
FROM LCINDIV JOIN
     LCCLIENT
     ON LCCLIENT.CLIENTID = LCINDIV.CLIENTID
WHERE EXISTS (SELECT 1
              FROM LSAUDIT
              WHERE LSAUDIT.AUDITKY = LCCLIENT.AUDITKEY AND
                    (LSAUDIT.ENTRYDT > TRUNC(SYSDATE) OR
                     LSAUDIT.ENTRYDT = TRUNC(SYSDATE) AND LSAUDIT.ENTRYTM > TO_CHAR(SYSDATE, 'HH24:MI:SS')
                    )
              );

For performance, an index on LSAUDIT(AUDITKY, ENTRYDT, ENTRYTM) is helpful.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13026

I have 2 suggestions.

  1. to use exists instead of IN
  2. combine your date and time
SELECT LCINDIV.CLIENTID
    , LCINDIV.COID, BIRTHDAY
    , NATIONAL, ACRNO, CIVSTAT, GENDER, RELIGION, SSSID, TIN, RESCERT
    , EDUCLV, DEGREE, RESIDENT, LNAME, FNAME, MNAME, DTPLRES, NATIONLT, LCINDIV.SERVERUPDATE
FROM LCINDIV
LEFT OUTER JOIN LCCLIENT
    ON LCCLIENT.CLIENTID = LCINDIV.CLIENTID
WHERE
   EXISTS (SELECT 1 FROM LSAUDIT WHERE
     to_date(ENTRYDT||ENTRYTM, 'dd-mon-yyyy hh24:mi:ss') > to_date('04-Nov-2019'||'15:15:10', 'dd-mon-yyyy hh24:mi:ss'))

Upvotes: 0

Related Questions