I have a table with ~1 billion timestamped records, and each record holds an FK to a session table (one session per day & 3-500,000 records per day), so finding the records for a given day is simply an integer join.
I am trying to analyze the data in this table (with data grouped by session), and I can run a complete analysis (every record) in 70 minutes when using a C# console app from a client machine. When I try to run a similar analysis directly in TSQL, it takes over 12 hours. I expect some penalty, as the TSQL query uses a scalar function and a custom aggregate (clr)).
My Question: In C#, I understand how to maximize and tune concurrency, so the 70 minutes is a tuned number. Is it possible to tune a query for maximal concurrency directly in SQL, or is that better left to C# apis? (I could also do this work in R, in db or outside, but the .Net concurrency APIs strike me as superior.)
Query:
SELECT TypeNumber, SessionId, dbo.udf_SessionName([timestamp]) SessionName,
CAST(max(price)-min(price) AS REAL) as Variance, sum(EventNumber) as Volume,
dbo.Direction(price,[timestamp]) as MoveDirection
INTO temp.AnalysisResults
FROM MyTable
WHERE ISNULL(price,0)<>0
GROUP BY TypeNumber, SessionId, dbo.udf_SessionName([timestamp])
Misc
- Bulk Logged enabled for this query, due to the insert
- Primary key is not used in this query (it's a composite key across three fields, which is not needed here. However, the query plan is showing that this index is being scanned, not the index I mention below (which the plan initially recommended)).
- Row-level compression is enabled
- Data spans five years, with a readonly filegroup for each month (partitioned by month); all filegroups reside on the same SSD (not great, I know)
- Index: non-clustered on SessionId asc, include TypeNumber,Timestamp,Price
- 4 CPU cores available
- The scalar function takes each timestamp, converts it to localtime with AT TIME ZONE (two calls), and looks it up in a 5-record table.
- The custom aggregate uses a custom serializer that takes in a decimal and a datetime2 and returns a string. The serializer passes strings which then need to be parsed (this is not great)
- Looking at the query plan (removed the insert), the most expensive operation by far is a sort (98% of the cost; the only sort I explicitly initiate is in the clr aggregator function):
Caveats: I know that using a CLR aggregate is going to cost me query time, as well as the compression. If I go with a console app, I can offload all analysis work onto a more powerful machine, leaving the db server to just do IO. Is this the "obvious answer", or can I keep most of this work in the database (generally, the more I can do in the database directly, the better).
I realize that with the way this database is setup, with its compression setting, it is tuned more for IO than CPU. I don't expect to be able to achieve performance on par with a pure C solution where the db is only doing IO; but there's a lot to gain by maximizing the cpu work the db can do.
udf_SessionName:
create function dbo.[udf_SessionName](@timestamp datetime2)
returns nvarchar(100)
begin
declare @localTime time = CAST(@timestamp at time zone 'UTC' at time zone 'Pacific Standard Time' as time)
declare @result nvarchar(100) = (select top 1 sessionname from MarketSessions where @localTime>=StartTime and @localTime < EndTime)
if (@result is null) set @result = 'European'
return @result
end
Table structures in SQL Fiddle
After Action Report: I've implemented @SolomonRutzky's suggestions, and the query now completes in 3 hours vs 12+.
Summary of changes
- Changed time zone manipulation from a scalar udf to a clr function (a
SAFEimplementation withoutTimeZoneInfo). - Rolled that clr function into a non-persisted computed column.
Added a new index:
CREATE NONCLUSTERED INDEX [inx_MyIndex] ON [dbo].MyTable (TypeNumber ASC, SessionId ASC) INCLUDE (SessionName,Price,Timestamp,Volume])
SessionName really would be better as a key in the index, but even though it's both precise and deterministic, because it's a CLR function, it can't be a key unless it's persisted, and that column, while mostly static, isn't sufficiently static to be persisted.
- Removed
ISNULL
The modified query
INSERT INTO temp.AnalysisResults
SELECT TypeNumber, SessionId, SessionName,
CAST(max(price)-min(price) AS REAL) as Variance, sum(EventNumber) as Volume,
dbo.Direction(price,[timestamp]) as MoveDirection
FROM MyTable
WHERE price <> 0 AND price IS NOT NULL
GROUP BY TypeNumber, SessionId, SessionName
The new execution plan
