I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise.
The two tables are
Logs. Both are partitioned on their
time by day column with the appropriate indexes.
The tables are as follows:
Here is the query:
DECLARE @day as varchar(max) = N'20141007' DECLARE @dayStart as varchar(MAX) = CONCAT(@day,N' 00:00:00.000') DECLARE @dayEnd as varchar(MAX) = CONCAT(@day,N' 23:59:59.999') SELECT messageId, time, direction, hasRouting, deviceType, unitId, accountCode, clientId, data FROM Logs AS A WHERE EXISTS (SELECT * FROM Acks AS B WHERE (A.messageId = B.messageId) AND (B.direction = 0) AND (B.isNack = 0)) AND (A.unitId LIKE 'TEST') AND (A.direction = 1) AND (A.time BETWEEN @dayStart AND @dayEnd) ORDER BY time ASC
I must admit that I have not had a lot of experience with queries from two tables i.e. joins. The way I interpret this query is that it is going to first filter out all of the entries for the log table, and then go and do a "where" query on the entire log table for each row in the log table.
Now, each table is about 50GB and you can imagine if it's not utilising the partitioning scheme correctly this query is going to iterate through the entire table.
My concerns with the query are:
- Performance. The query is taking way too long to execute
- It is not fully utilising the partition function when searching
I have noticed that it looks in every single file group when executing the query, even when the query only spans 1 day. I'm not sure if this is due to the
Some advice and tips on how to structure this query would be greatly appreciated. And possibly also explain what is wrong with my current one so I can learn from my mistakes.
DECLARE @day as datetime2(2) = N'20141007' DECLARE @dayStart as datetime2(2) = CONCAT(@day,N' 00:00:00.000') DECLARE @dayEnd as datetime2(2) = CONCAT(@day,N' 23:59:59.999') SELECT messageId, time, direction, hasRouting, deviceType, unitId, accountCode, clientId, data FROM Logs INNER JOIN Acks ON Logs.messageId = Acks.messageId WHERE (Acks.direction = 0) AND (Acks.isNack = 0) AND (Logs.unitId LIKE 'DEVMB1') AND (Logs.direction = 1) AND (Logs.time BETWEEN @dayStart AND @dayEnd) AND (Acks.time BETWEEN @dayStart AND @dayEnd) ORDER BY Logs.time ASC
Added execution plan for reworked query: