How to optimize SELECT query?

Question

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

And what table is audit key in? What are the data types of the date and time columns? — Gordon Linoff
– Gordon Linoff, Commented Nov 5, 2019 at 1:04

Gordon Linoff · Accepted Answer · 2019-11-05 02:17:13Z

1

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.

edited Nov 5, 2019 at 2:17

answered Nov 5, 2019 at 1:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barbaros Özhan Over a year ago

perhaps it would be SYSDATE :) @Blank317

Ed Bangga · Accepted Answer · 2019-11-05 00:53:01Z

0

I have 2 suggestions.

to use exists instead of IN

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'))

answered Nov 5, 2019 at 0:53

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

8 Comments

user12323380 Over a year ago

there is an error in the query "Hour must be between 0 to 23"

Ed Bangga Over a year ago

@Blank317, seems you have data issue. tried your sample date and time seems good. see sqlfiddle sqlfiddle.com/#!4/c0be1c/30823/0

user12323380 Over a year ago

I am using Oracle 9i is that ok?

Ed Bangga Over a year ago

it should, to_date existed in oracle in later vesions

user12323380 Over a year ago

Is there a way to eliminate time that exceeds 24 and below 0?

|

Collectives™ on Stack Overflow

How to optimize SELECT query?

2 Answers 2

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related