I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise.

The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate indexes.

The tables are as follows:

Ack Table


Log Table

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')

    messageId, time, direction, hasRouting, deviceType, 
    unitId, accountCode, clientId, data
    Logs AS A
            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) 
    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 Exists.

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.


Reworked version:

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')

   messageId, time, direction, hasRouting, deviceType, 
   unitId, accountCode, clientId, data
   Acks ON Logs.messageId = Acks.messageId
   (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) 
   Logs.time ASC 

Added execution plan for reworked query:

Execution Plan

  • what's wrong with your query? i mean, why you think that improvements are needed? you are not getting the expected result? performance are poor? i would go with a join instead of EXISTS but without hints from the query analyzer there is not much to be said. btw what is your rdbms?
    – Paolo
    Oct 7, 2014 at 12:24
  • Please post actual execution plan. Approx., how many rows do you expect as a result?
    – Stoleg
    Oct 7, 2014 at 12:45
  • @Stoleg I have added a link to the Execution Plan in my original post at the bottom Oct 7, 2014 at 13:15
  • It looks as if my Join Is what's costing me. Is there not a solution where I could select from each table and put them into a temp table and then query the temp table? I need to filter my results first by time so that the partitioning can reduce the number of records to search and only they compare the other search operations. Oct 7, 2014 at 13:18
  • @Zapnologica why you make a comparison Logs.unitId LIKE 'DEVMB1' instead of Logs.unitId = 'DEVMB1'?
    – Paolo
    Oct 7, 2014 at 13:36

1 Answer 1


Without query execution plan, my first thoughts are:

I. Use datetime variables to remove implicit convertion and use index.

DECLARE @day as datetime2(2) = '20141007'
DECLARE @dayStart as datetime2(2) = CONCAT(@day,' 00:00:00.000')
DECLARE @dayEnd as datetime2(2) = CONCAT(@day,' 23:59:59.999')


EXISTS (Select * FROM Acks AS B where (A.messageId = B.messageId)


INNER JOIN ON A.messageId = B.messageId

III. Consider limiting table B on date - at the moment it looks through whole table.

UPDATE. 1. Do parts of your join work much faster as single queries? 2. Please include both Execution Plans on your post. Links may stop working in future.

  • Please see my edit: Oct 7, 2014 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.