Optimising SQL involving IF EXISTS

Question

I am trying to keep track of whether I need to update certain tables. I have an Events table which I am wanting to monitor for changes and have another table, called DictionaryRefresh, which keeps track of the changes made to that table. If the Events table gets edited, it's edittime will be saved and fall later than the last refresh time of the DictionaryRefresh table hence indicating the need to refresh. In addition, if new rows are added to the Events table, then there also needs to be associated new entries in the DictionaryRefresh table - hence the LEFT JOIN.

Here are the table structures

CREATE TABLE [dbo].[DictionaryRefresh]
(
    [LookupKey] [varchar](31) NOT NULL,
    [LookupValue] [varchar](255) NOT NULL,
    [RecordNumber] [int] NULL,
    [RefreshTime] [datetime] NULL,
    [EventKey] [varchar](31) NULL,
    [MappedLookupKey] [varchar](31) NULL
 ) ON [PRIMARY]

The indexes are (following the DBEngine Tuning Advisor)

CREATE NONCLUSTERED INDEX [idx_DictionaryRefresh2146B4EB] 
ON [dbo].[Ifx_DictionaryRefresh] ([LookupKey] ASC)

CREATE NONCLUSTERED INDEX [idx_DictionaryRefresh51EC6492] 
ON [dbo].[Ifx_DictionaryRefresh] ([MappedLookupKey] ASC, [RefreshTime] ASC, [RecordNumber] ASC, [EventKey] ASC)

CREATE NONCLUSTERED INDEX [idx_DictionaryRefreshFCDAD7FA] 
ON [dbo].[Ifx_DictionaryRefresh] ([LookupValue] ASC)

The Events table is as follows:

CREATE TABLE [dbo].[Events](
    [RecordNumber] [int] NOT NULL,
    ...
    [EventKey] [varchar](31) NOT NULL,
    ...
    [EditTime] [datetime] NULL,
    ...
PRIMARY KEY CLUSTERED([RecordNumber] ASC)

CREATE NONCLUSTERED INDEX [idxEvents299ADAC8] 
ON [dbo].[Events]([EditTime] ASC)

CREATE NONCLUSTERED INDEX [idxEvents5B151A5E] 
ON [dbo].[Events]([EventKey] ASC)

Now the SQL I am running is as follows - it is taking almost a minute to return. If I execute the subquery only it returns pretty much immediately.

IF EXISTS (
    SELECT 1
    FROM (
        SELECT 
            e.EventKey AS DictionaryKey
            ,ISNULL(e.EditTime, '1 Jan 1900 01:00') AS EditTime
            ,e.RecordNumber AS DictionaryRecordNumber
        FROM Events e) d
    LEFT JOIN DictionaryRefresh r ON r.RecordNumber = DictionaryRecordNumber
        AND r.EventKey = DictionaryKey
        AND r.MappedLookupKey = 'M18E2I501'
    WHERE r.RefreshTime < d.EditTime
        OR r.RecordNumber IS NULL)
BEGIN
    PRINT 'TRUE'
END

There are approx 130K rows in the DictionaryRefresh table, and approx 8K rows in the Events table

When the DictionaryRefresh table is empty or small it is very fast but slows down as the number of rows in DictionaryRefresh increases especially if none match the criteria.

Here is the execution plan.

and the salient stats (the index seek taking 94% of the cost - the number of rows accessed is effectively the square of the number of rows in the Events table) ...

I have tried replacing the

IF EXISTS 

with

IF (SELECT COUNT ...) <> 0

as well as

IF (SELECT TOP 1 1 ...) = 1

but none seemed any faster.

I would obviously appreciate any suggestions you might have.

Thanks in advance.

S

Answer 1

re-formatting your query a bit I come to this:

IF EXISTS ( SELECT 1 
              FROM (SELECT e.EventKey AS DictionaryKey
                          ,ISNULL(e.EditTime, '1 Jan 1900 01:00') AS EditTime
                          ,e.RecordNumber AS DictionaryRecordNumber
                      FROM Events e) d
              LEFT OUTER JOIN DictionaryRefresh r 
                           ON r.RecordNumber = d.DictionaryRecordNumber
                          AND r.EventKey = d.DictionaryKey
                          AND r.MappedLookupKey = 'M18E2I501'
             WHERE r.RefreshTime < d.EditTime
                OR r.RecordNumber IS NULL)
BEGIN
    PRINT 'TRUE'
END

I don't see a good reason for the sub-query on Events, so the equivalent query then becomes this:

IF EXISTS ( SELECT *
              FROM Events e
              LEFT OUTER JOIN DictionaryRefresh r 
                           ON r.RecordNumber = e.RecordNumber
                          AND r.EventKey = e.EventKey
                          AND r.MappedLookupKey = 'M18E2I501'
             WHERE r.RefreshTime < ISNULL(e.EditTime, '1 Jan 1900 01:00')
                OR r.RecordNumber IS NULL
         )
BEGIN
    PRINT 'TRUE'
END

First thing to notice is that you use r.RefreshTime in the WHERE clause. Since the < operator will only return true when the left side is DEFINED and is smaller than the right side, this means that each time r.RefreshTime is NULL the record will be skipped. Yet, the next line you obviously mention that you want all records where r.RecordNumber is NULL which can only happen when the value is actually NULL, or when the LEFT OUTER JOIN does not find a match. So there's a bit of a conflict here. Either you want to do an INNER JOIN, or you really want an OUTER JOIN but then need to move the r.RefreshTime < d.EditTime to the JOIN ON clause.

Now, looking at your table definitions, I think there is some room for improvement. Going by the explanation you gave above the Events table is 'the source' of all data. It gets appended over time and then once in a while you run a process that scans for 'new' and 'updated' records, does some magic and then updates the DictionorayRefresh (UPDATE existing records to a new RefreshTime and INSERT new ones as

• [dbo].[Events]

  • [EditTime] is defined as NUL-able. Maybe you consider NULL as 'record is inserted but was never updated' ? In that case I'd go for rather using '1 jan 1900' as a 'magic' value and make the field NOT NULLable, it makes life much easier later on.

• [dbo].[DictionaryRefresh]

  • I'm wondering why you'd want RecordNumber to be NULL-able? Shouldn't it always be filled in, otherwise what purpose would the record have ?
  • You should also put a FOREIGN KEY on the field pointing to the Events table, that way the server knows all values come from there
  • RefreshTime also is defined as NULL-able, again I think you'd want that to always be filled in. Otherwise how did the record get into the table?
  • pretty sure you want MappedLookupKey, but that doesn't really matter all that much for this.

Anyway, back to the query. What you want to figure out is if there are records in Events that have a matching record in DictionaryRefresh for a given MappedLookupKey and newer EditTime than the corresponding RefreshTime. Or, that simply have no such a record (for this MappedLookupKey)

Personally I would write that like this:

IF EXISTS ( SELECT *
              FROM Events e
             WHERE NOT EXISTS ( SELECT *
                                  FROM DictionaryRefresh r 
                                 WHERE r.RecordNumber = e.RecordNumber
                                   AND r.EventKey = e.EventKey
                                   AND r.MappedLookupKey = 'M18E2I501'
                                   AND r.RefreshTime >= e.EditTime )

         )
BEGIN
    PRINT 'TRUE'
END

To make this work fast, you'll need the following indexes:

CREATE INDEX idx1 ON DictionaryRefresh  (MappedLookupKey, RecordNumber, EventKey, RefreshTime)

On the Events table I think the PK will do...

Fun fact: your JOIN uses both RecordNumber and EventKey (again a NULL-able field for probably no good reason). However, we already know that RecordNumber uniquely identifies a record in [Events] (it's the PK !), so if you join on just RecordNumber that actually should do, unless you can have different EventKey values in DictonaryRefresh ? That wouldn't make sense to me tbh... in fact, it seems that the field is not really needed in DictionaryRefresh as it can be found in Events in the first place. If that assumption is correct you can remove it from the table and the thus JOIN speeding things up a bit more again.

Bit of a long-read, hope I didn't goof up too much =)

Answer 2

CREATE NONCLUSTERED INDEX ix1
    ON dbo.DictionaryRefresh (RecordNumber, EventKey, MappedLookupKey, RefreshTime)

CREATE NONCLUSTERED INDEX ix2
    ON dbo.[Events] (RecordNumber, EventKey, EditTime)

IF EXISTS (
    SELECT TOP(1) 1
    FROM dbo.[Events] e /*WITH(INDEX(ix2))*/
    LEFT JOIN dbo.DictionaryRefresh r /*WITH(INDEX(ix1))*/ ON r.RecordNumber = e.RecordNumber
        AND r.EventKey = e.EventKey
        AND r.MappedLookupKey = 'M18E2I501'
    WHERE (r.RefreshTime < e.EditTime AND e.EditTime IS NOT NULL)
        OR r.RecordNumber IS NULL
)
BEGIN
    PRINT 'TRUE'
END