PLEASE HELP ME TO OPTIMIZE QUERY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

PLEASE HELP ME TO OPTIMIZE QUERY

Author

Topic

projects@computerra.com
Starting Member

1 Post

Posted - 2007-02-02 : 06:35:23

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 around 5 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 decimal 5
BatteryVoltage decimal 5
ReasonCode int
DistanceFromLastLocation decimal 9
UnitHistoryDate datetime

Indexes:

1. Clustered Index on Column UnitID
2. Non-clustered Index on Column UnitHistoryDate
3. Non-clustered Index on Column UnitHistoryID

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,
Raj

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 07:30:34

You seem to be using D-Sql for running this query. You can take a look at the CSV parsing approaches described here

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-02 : 13:27:26

Is UnitID indexed on both tables? I suspect it's the primary key on one of your tables, probably tblUnit. You automatically get an index when you create a primary key. You don't automatically get an index with the foreign key though. So it must be added manually to the child table, probably tblUnitHistory. Or is UnitID the PK in both tables?

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources