CODEWITHSUNDEEP
sqlsql-servert-sqldatetimequery-optimization
user10157300
user10157300

Reputation:

SQL query runs very slow when using format date function

Using SQL Server 2016. I have the following table that has a large number of records (30 mil+).

CREATE TABLE [dbo].[TABLE1]
(
    [DATE_TIME] [DATETIME] NULL,
    [TEXT] [VARCHAR](500) NULL,
    [MSG] [VARCHAR](500) NULL,
    [MSGID] [INT] NULL,
    [SEVERITY] VARCHAR(50) NULL
)

CREATE NONCLUSTERED INDEX [TABLE1_IX1] 
ON [dbo].[TABLE1] ([DATE_TIME] ASC, [MSGID] ASC, [SEVERITY] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
                      SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
                      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The following query is very slow:

SELECT 
    [DATE_TIME], [TEXT], [MSG], [MSGID], [SEVERITY] 
FROM      
    [TABLE1] 
WHERE
    FORMAT([DATE_TIME], 'yyyy-MM-dd') IN ('2019-06-25', '2019-06-24',etc.....) 
    AND [MSGID] IN (8016, 11, 3072, 23, 3062, etc....) 
    AND [SEVERITY] <> 'Medium' 
ORDER BY 
    [DATE_TIME] DESC

Please help to optimize.

Upvotes: 2

Views: 5735

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use direct date comparisons. Because you seem to have a range, I would recommend:

WHERE [DATE_TIME] >= ? AND
      [DATE_TIME] < '2019-06-26' AND
      [MSGID] IN (8016, 11, 3072, 23, 3062, etc....) AND
      [SEVERITY] <> 'Medium'

This can make optimal use of an index and partitions, if available. You can also convert to a date. That is the one use of a function that will also use an index. I don't know if convert will prune partitions.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

Format has some great functionality, but performance can suffer.

Try

WHERE convert(date,[DATE_TIME]) in (...)

Upvotes: 4

Related Questions