Sql Query Taking longer to execute than expected time

Question

I am trying to execute the query below but it's taking around 40mins.

select logentryid 
from (
     select * from logentry_payloaddata pd,
     logentry le
     where (
            pd.entryid=le.logentryid
       and
            le.eventdatetime=(trunc(sysdate) - 14)
     )
     order by le.logentryid asc
)

In table logentry there are 4,24,91,461 records and in logentry payload data there are 4,15,16,346 records (or "42,491,461" and "41,516,346" in some parts of the world, or even "42.491.461" and "41.516.346" in some other parts -- Ed.)

There are indexes on logentryid and entryid in resp tables.

Could someone please suggest what to do in this case.

Answer 1

Some suggestions:

1 - Index eventdatetime if it's not already

2 - Define a variable for (trunc(sysdate) - 14) - this will prevent the function from running for every row and will allow the use of the index from step 1

3 - Un-obfuscate your code. Nesting is unneeded here and you are using an old-style JOIN syntax which can cause problems:

SELECT logentryid 
FROM logentry_payloaddata pd
INNER JOIN logentry le
   ON pd.entryid=le.logentryid
WHERE le.eventdatetime = @MyDateVariable
ORDER BY logentryid ASC

Answer 2

SELECT
  le.logentryid
FROM
  logentry_payloaddata     pd
INNER JOIN
  logentry                 le
    ON pd.entryid=le.logentryid
WHERE
  le.eventdatetime = CAST(trunc(sysdate) - 14 AS DATETIME)
ORDER BY
  le.logentryid ASC

1). Changed the layout (using INNER JOIN, etc) for my benefit, but doubt it will change performance.

2). Added a CAST() to your date comparison. Your code requires an implicit CAST() any way. Using an explicit CAST() ensures that the constant is being cast, not the data field. This matters greatly as it dictates how any indexes can, or can't, be used.

3). Indexes - There is no single answer, it depends on the statistics of your data.

On the table logentry_payloaddata you definately need an index on entryid.

On the table logentry there are two different indexes that may help:
- (logentryid, eventdatetime)
- (eventdatetime, logentryid)

My best guess is that the latter will give the best performance. But I'd recommend creating both and checking to see which is actually used.