How can I improve speed of select?

I have a table set up like this:

The DB has 10B rows.

CREATE TABLE [Tick].[X_H](
       [utcDT] [datetime2](7) NOT NULL,
       [Symbol] [nvarchar](50) NOT NULL,
       [Bid] [float] NULL,
       [Ask] [float] NULL,
       [BidSize] [float] NULL,
       [AskSize] [float] NULL
) ON [PRIMARY] 

ALTER TABLE [Tick].[X_H] ADD  CONSTRAINT [PK_Master] PRIMARY KEY CLUSTERED 
(
       [utcDT] ASC,
       [Symbol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I want to select a single day's worth of data for a particular Symbol. I am using:

SELECT *  
FROM [Tick].[X_H] 
WHERE [Symbol] = 'DONKEY' 
AND CONVERT(Date, [utcDT]) = CONVERT(Date,'2011-01-02');

This takes 3 times longer than reading the same data from a binary file and deserialising it. Binary read is 42ms and SQL read is 115ms.

What might I try to speed it up?

The suggestion was made to create a separate [Date] column, which I have and I added 2 indexes.

ALTER TABLE [Tick].[FX_HS] ADD [Date] DATE NULL;
UPDATE [Tick].[X_H] SET [Date] = CONVERT(Date,[utcDT])
CREATE INDEX I_Date ON [Tick].[X_H] ([Date]); 
CREATE INDEX I_SymbolDate ON [Tick].[X_H] ([Symbol],[Date]);

And I am using query:

SELECT *  FROM [Tick].[X_H] WHERE [Symbol] = 'DONKEY' AND [Date] = '2011-01-02';

But it takes approximately the same amount of time!

Execution plan

Paste the Plan

Query 1: Query cost(relative to batch): 100%
SELECT * FROM [Tick][X_H] WHERE [Symbol]=@1 AND [Date]=@2

As Martin Smith suggested:

```
SET STATISTICS TIME ON

DECLARE @utcDT DATETIME2(7)
DECLARE @Symbol NVARCHAR(50)
DECLARE @Bid FLOAT
DECLARE @Ask FLOAT
DECLARE @BidSize FLOAT
DECLARE @AskSize FLOAT
DECLARE @Date DATE

SELECT @utcDT = utcDT,
@Symbol = Symbol,
@Bid = Bid,
@Ask = Ask,
@BidSize = BidSize,
@AskSize = AskSize,
@Date = Date
FROM Tick.X_H
WHERE ( Symbol = 'DO

Your where condition that checks for equality for utcdt converted to a date is SARGable, but uses a dynamic seek.


The problem is that these hidden implicit conversions can result in inaccurate cardinality and distribution estimations at any stage of the plan. So, even if you get a seek, the plan might be way off overall. - Dynamic Seeks and Hidden Implicit Conversions - Paul White

Try converting your where condition to an explicit range and see if there is any improvement:

select *
from Tick.X_H
where Symbol = 'donkey'
  and utcdt >= convert(datetime2(7),'20110102')
  and utcdt <  convert(datetime2(7),'20110103')

Based on this answer by Martin Smith the explicit range may reduce unnecessary reads.

References:

• Dynamic Seeks and Hidden Implicit Conversions - Paul White

• Cast to date is sargable but is it a good idea?

• SARGable functions in SQL Server - Rob Farley

StackExchange Database Administrators Q#165826, answer score: 8