sql server – How can I improve query performance with filter on datetime column using an indexed view?

I have this table:

CREATE TABLE transactions
(
    id                                    NUMERIC(20, 0)              NOT NULL PRIMARY KEY,
    amount                                NUMERIC(18, 2) DEFAULT NULL NULL,
    -- Some 100 columns
    customer_msisdn                       VARCHAR(255)   DEFAULT NULL              NULL,
    customer_email                        VARCHAR(255)   DEFAULT NULL              NULL,
    payment_date                          DATETIME2                   NOT NULL
);

CREATE NONCLUSTERED INDEX msisdn_idx ON transactions (customer_msisdn, payment_date, id);
CREATE NONCLUSTERED INDEX phone_idx ON transactions (customer_phone, payment_date, id);

I’m indexing around ~1m rows a month. Very frequently, I need to select last 3 month’s transactions per customer_msisdn or per customer_email which is 99% time 50 – 1000 records.

Here’s my query for a bit more insight:

SELECT t.*
FROM transactions t
         JOIN (SELECT t.id
               FROM transactions t
                        WITH (FORCESEEK)
               WHERE t.customer_email = :customerEmail
                 AND t.payment_date >= :startDate
                 AND t.payment_date < :endDate
               UNION
               SELECT t.id
               FROM transactions t
                        WITH (FORCESEEK)
               WHERE t.customer_msisdn = :customerMsisdn
                 AND t.payment_date >= :startDate
                 AND t.payment_date < :endDate) AS filtered_transactions
              ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

And I feel like since the :endDate is always now (and when not, can tolerate fault) and :startDate is always three months back, I have some room for improvement. Here’s what I thougt:

Create an indexed view with a filter on payment_date:

CREATE VIEW (dbo).transactions_iv
    WITH SCHEMABINDING AS
SELECT (t).id,
       -- All the rows
       (t).customer_msisdn,
       (t).customer_phone,
       (t).payment_date
FROM (dbo).(transactions) (t)
WHERE (t).payment_date >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP);

and my indexes:

CREATE NONCLUSTERED INDEX msisdn_iv_idx ON transactions_iv (customer_msisdn, id);
CREATE NONCLUSTERED INDEX phone_iv_idx ON transactions_iv (customer_phone, id);

and drop the AND t.payment_date >= :startDate AND t.payment_date < :endDate clauses altogether from the query. The query becomes:

SELECT t.*
FROM transactions_iv t
         JOIN (SELECT t.id
               FROM transactions_iv t
                        WITH (FORCESEEK)
               WHERE t.customer_email = :customerEmail
               UNION
               SELECT t.id
               FROM transactions_iv t
                        WITH (FORCESEEK)
               WHERE t.customer_msisdn = :customerMsisdn) AS filtered_transactions
              ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

Since the view only has last 3 month’s transactions, I’m assuming so will the indexes. Is this assumption correct correct and would I get my performance boost?


Another alternative is:

  1. Create another identical table,
  2. Populate it with a trigger on the main table
  3. With a cron job, delete records older than 3 months every night.

How would this option compare to the previous one?