I have two large tables containing a common value ContactReference, and two DateTime fields (amongst a lot of other fields). One table for Contact and one for ContactAddress.

I am trying to find an efficient query to find, for a ContactReference AND between two dates, the MAX(date) when one of the values was changed.

i.e. Regardless whether it was the Contact or ContactAddress that changed between the dates, when did that happen?

I have tried:


            c.ContactReference AS ContactReference
          , c.EditDate AS EventDateTime
                                ORDER BY
                                    c.EditDate DESC
                              ) AS rn
            dbo.Contact AS c


            contactCTE c
            c.rn = 1
        UNION ALL
            contactAddressCTE ca
            ca.rn = 1

With CTEs and without.


Putting the BETWEEN predicate at the end, in the first select, after the union, etc. etc.

ROW_NUMBER was the worst at 21 seconds.

ALL of the others come back in 7 seconds...and the execution plan is pretty similar whatever I try (CTE is marginally less expensive).

The above are examples of what I have tried, not the full SQL which would be pages of "tried this, tried that".

Any ideas how I can speed this up?

There are indexes on the "key" value (ContactReference) in both tables. I thought of a composite index on (ContactReference, EditDate), but there are around 2mn rows in Contact and 25mn in Address and they both get edited regularly (by external processes) and I'm concerned about the size and performance hit of maintaining both indexes.

closed as unclear what you're asking by Max Vernon, Lennart, Vérace, RDFozz, Colin 't Hart May 19 '18 at 7:53

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    The query (or queries?) in the question appear to be incomplete. Please edit and post the whole queries you have tried. We don't mind pages of code ;) Please also add table definitions (CREATE TABLE statements) – ypercubeᵀᴹ May 16 '18 at 22:14
  • Please add a minimal, complete, and verifiable example to your question. – Max Vernon May 18 '18 at 14:59

Have you tried anything like this?

Your tables (small subset!):

CREATE TABLE table_1 (date_1 DATE);
CREATE TABLE table_2 (date_2 DATE);

Data (a sample):

INSERT INTO table_1 VALUES ('2013-05-05'), ('2002-02-02'), ('2001-01-01');
INSERT INTO table_2 VALUES ('2012-05-05'), ('2004-04-02'), ('2006-01-01');

The query:

SELECT MAX(date_1) AS the_date FROM
  SELECT date_1 FROM table_1 -- AS the_date
  SELECT date_2 FROM table_2 -- AS the_date
) AS my_tab;


05/05/2013 00:00:00

I imagine that with an index on your DATE field, this would be about as good as it gets? I may have missed something, because this seems fairly simple - but who knows? Fiddle here.

You could also try (something like):

  SELECT MAX(date_1) FROM table_1  -- AS the_date
  SELECT MAX(date_2) FROM table_2  -- AS the_date
) my_tab;

Now, this works under PostgreSQL 10 (fiddle here), but I can't for the life of me get it working in SQL Server 2014 - some sort of column naming problem (be grateful for an explanation). I've no way of testing for efficiency at my end, since I only have access to SQL Server through the fiddles.

Not the answer you're looking for? Browse other questions tagged or ask your own question.