HELP ME TO OPTIMIZE QUERY

Hello friends,

I'm facing performance related problem while running following query on SQL Server 2000.

This query is basically used to find last location of each unit that are passed. Here I am passing data like "'26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @.Units variable. But it takes too much time and I don't get output. Table is having around5 Million records.

Query:

SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM tblUnit INNER JOIN

tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID

WHERE tblUnitHistory.UnitHistoryDate

IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ') GROUP BY tblUnitHistory.UnitID)

AND tblUnit.UnitID in (' + @.Units + ')

ORDER BY tblUnit.UnitID

Table Structure:

UnitHistoryID int Primary Key

UnitID int

Location varchar(200)

Latitude decimal 9

Longitude decimal 9

Speed decimal5

BatteryVoltage decimal5

ReasonCode int

DistanceFromLastLocation decimal9

UnitHistoryDate datetime

Indexes:

1. Clustered Index on ColumnUnitID

2. Non-clustered Index on ColumnUnitHistoryDate

3. Non-clustered Index on ColumnUnitHistoryID

Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.

Regards,

Sandeep

try to limit processed records by:

'SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM (SELECT * FROM tblUnit

where

AND tblUnit.UnitID in (' + @.Units + '))tblUnit

INNER JOIN

(SELECT tblUnitHistory.UnitID , MAX(UnitHistoryDate) UnitHistoryDate FROM (SELECT * FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ')) tblUnitHistory GROUP BY tblUnitHistory.UnitID) tblUnitHistory

ON tblUnit.UnitID = tblUnitHistory.UnitID

ORDER BY tblUnit.UnitID'

I hope that it will work, The Idea is to limit number of records used in join to minimum.