CODEWITHSUNDEEP
sqlsql-serverdatabaseperformancesql-server-2008
Bender
Bender

Reputation: 83

How to optimize SELECT MaxDate

I have a query to select some data from tableA matching some rows in tableB. Even though it works it's really slow, it takes about 20 min to return a few hundred rows. Here is the query, hope you have some suggestions where to improve or what to change.

I am using SQL Server 2008

DECLARE @MaxDate DATETIME

SELECT @MaxDate = MAX(TimeStamp) -- I know it's not the best column name
FROM tableA
WHERE CodeName IN
(SELECT Tag FROM TableB 
WHERE POSITION = 'UP' AND PLACE = 'Shelf') 

IF @MaxDate > '2011-08-08'
BEGIN

 SELECT @MaxDate = MAX(TimeStamp) -- I know it's not the best column name
FROM tableA
WHERE CodeName IN
(SELECT Tag FROM TableB 
WHERE POSITION = 'UP' AND PLACE = 'Shelf')AND TimeStamp >= '2008-12-24' AND TimeStamp < '2010-12-24'

END
ELSE 
PRINT 'Date out of range'

Upvotes: 1

Views: 1237

Answers (1)

digscoop
digscoop

Reputation: 371

The subquery looks fishy and you risk a table scan for every row in tableA. You also only need one query and the shouldn't reuse the variable for something else anyway.

Make sure Codename, Tag, Position and Tag are properly indexed. If "up"/"shelf" tags are not super common you should be able to get away with a table seek of tableA.

    SELECT
        @MaxDate  = MAX(TimeStamp),
        @MaxDate2 = MAX(                
            CASE WHEN 
                TimeStamp >= '2008-12-24' AND
                TimeStamp < '2010-12-24'
                THEN TimeStamp END)
    FROM
        tableA A
    JOIN
        Tag T ON 
            T.CodeName=A.Tag 
    WHERE   
        T.POSITION = 'UP' AND
        T.PLACE = 'Shelf'

And do something appropriate with the results

IF @MaxDate > '2011-08-08'
BEGIN
    SELECT @MaxDate2
END
ELSE 
PRINT 'Date out of range'

Upvotes: 1

Related Questions