0

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

and

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

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 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.

{EDIT}

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

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:

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 '14 at 12:24
  • Please post actual execution plan. Approx., how many rows do you expect as a result? – Stoleg Oct 7 '14 at 12:45
  • @Stoleg I have added a link to the Execution Plan in my original post at the bottom – Zapnologica Oct 7 '14 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. – Zapnologica Oct 7 '14 at 13:18
  • @Zapnologica why you make a comparison Logs.unitId LIKE 'DEVMB1' instead of Logs.unitId = 'DEVMB1'? – Paolo Oct 7 '14 at 13:36
2

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

II. Change EXISTS for INNER JOIN

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

to

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.

| improve this answer | |
  • Please see my edit: – Zapnologica Oct 7 '14 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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