1

SQL Server 2016

I have the following query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    (   [ValidFrom] <= SYSDATETIMEOFFSET()
         AND SYSDATETIMEOFFSET() < COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )
     AND (   [DataEffective] <= SYSDATETIMEOFFSET() 
         AND SYSDATETIMEOFFSET() < COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )

I'm getting a table scan every time I run this query. I need an index on this table because this query will be run a lot. I've tried several different ones but I can't seem to get the server to use any of them.

I've tried indexing:

  • ValidFrom
  • ValidFrom, DataEffective
  • ValidFrom, ValidUntil, DataEffective, DataExpired

I've also tested against both an empty table and a table seeded with 10202 rows.

I've also tested with a restructured query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= SYSDATETIMEOFFSET()
     AND [DataEffective] <= SYSDATETIMEOFFSET() 
     AND SYSDATETIMEOFFSET() < COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
     AND SYSDATETIMEOFFSET() < COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))

and like this:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= SYSDATETIMEOFFSET()
     AND [DataEffective] <= SYSDATETIMEOFFSET() 
     AND (   [ValidUntil] > SYSDATETIMEOFFSET()
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] > SYSDATETIMEOFFSET()
         OR  [DataExpired] IS NULL
         )

and like this (to remove the non-deterministic SYSDATETIMEOFFSET() reference):

DECLARE @Now DateTimeOffset(7)

SET @Now = SYSDATETIMEOFFSET()

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= @Now
     AND [DataEffective] <= @Now
     AND (   [ValidUntil] > @Now
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] > @Now
         OR  [DataExpired] IS NULL
         )

Table Definition

CREATE TABLE [dbo].[Networks]( 
[ValidFrom] [datetimeoffset](7) NOT NULL, 
[ValidUntil] [datetimeoffset](7) NULL, 
[DataEffective] [datetimeoffset](7) NOT NULL, 
[DataExpired] [datetimeoffset](7) NULL, 
[UserId] [nvarchar](128) NOT NULL, 
[Id] [uniqueidentifier] NOT NULL, 
[Descriptor] [nvarchar](255) NULL ) 
ON [PRIMARY] 
GO
  • Try the 2nd version, but with a variable to hold SYSDATETIMEOFFSET(). It's not a deterministic function, so SQL Server HAS to scan. – Stu Jun 18 '18 at 19:57
  • Can you add create table statements and some sample data that matches your use case? – Erik Darling Jun 18 '18 at 19:58
  • What's the PK on Networks? If Id is the PK, then you can create a covering index by indexing on ValidFrom, DataEffective, ValidUntil, DataExpired, and including Descriptor. The fact that Descriptor, at least, needs to be retrieved from the main table probably accounts for why the index isn't used - the query engine may be calculating that the time required to look up the selected rows in the main table and to retrieve Descriptor would be more than the effort to simply find the rows in the main table directly. – RDFozz Jun 18 '18 at 19:59
  • Create table script: CREATE TABLE [dbo].[Networks]( [ValidFrom] [datetimeoffset](7) NOT NULL, [ValidUntil] [datetimeoffset](7) NULL, [DataEffective] [datetimeoffset](7) NOT NULL, [DataExpired] [datetimeoffset](7) NULL, [UserId] [nvarchar](128) NOT NULL, [Id] [uniqueidentifier] NOT NULL, [Descriptor] [nvarchar](255) NULL ) ON [PRIMARY] GO – Jason Jun 18 '18 at 20:01
  • 2
    @Jason You should edit your question to add details like this. I've done it for you here, but you should do that in the future rather than in comments. – Erik Darling Jun 18 '18 at 20:04
0

I think you can benefit from rewriting this query first, plunking it into a TVF and adding a non-clustered index on ValidFrom and DateEffective.

create nonclustered index ix_networks_validfrom_dateeffective
    on dbo.Networks (ValidFron, DateEffective)
    include (Id, Descriptor);
go

create function dbo.ValidNetworks (
    @dt datetimeoffset(7)
)
returns table
as 
    return (
        with networks as (
            select 
                Id
                ,Descriptor
                ,ValidFrom
                ,DateEffective
                ,isnull(ValidUntil, dateadd(day, 1, @dt)) as ValidUntil
                ,isnull(DateExpired, dateadd(day, 1, @dt)) as DateExpired
            from 
                dbo.Networks
        )

        select
            Id
            ,Descriptor
        from
            networks
        where
            ValidFrom <= @dt
            and DateEffective <= @dt
            and ValidUntil > @dt
            and DateExpired > @dt
    );

You could also create computed columns for ValidUntil and DateExpired and eliminate the CTE from the TVF.

I would also be curious to see this split into two separate queries and union'd. Whereby in both queries you filter on ValidFrom and DateEffective. In one you filter for null values in ValidUntil and DateExpired, and the other compare against the @dt value.

  • Interesting approach. I'm looking into this deeper, but I have a concern. My query isn't a one-off thing. There will be many, many different queries against multiple tables that follow this same pattern. I'm sort of hoping for a more generic performance-improving approach. – Jason Jun 19 '18 at 16:15
  • That doesn't exist. It will always come down to so many contextual factors which will vary from table to table, view to view etc. – pim Jun 19 '18 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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