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!
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 = 'DONKEY' ) AND ( Date = '2011-01-02' )
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 47 ms, elapsed time = 43 ms.