Optimize query using index - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Optimize query using index

Author

Topic

anculutza
Starting Member

4 Posts

Posted - 2010-04-19 : 04:01:05

What index should I use to optimize this query?

UPDATE Coordinates
SET CountSegments=c.Ord
FROM Coordinates co INNER JOIN (SELECT MAX(Ord)AS Ord, ObjectId FROM Coordinates GROUP BY ObjectId) c ON co.ObjectId=c.ObjectId

I mention that the table Coordinate has ~130Mio records.

anculutza

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-19 : 05:30:53

To optimize the inner query

quote:
SELECT MAX(Ord)AS Ord, ObjectId FROM Coordinates GROUP BY ObjectId

You can create index on ObjectId and Ord like this:


CREATE NONCLUSTERED INDEX Coordinates_IX_ObjectId_Ord
   ON Coordinates(ObjectId, Ord);

But that will add another index to be updated by the outer UPDATE statement. So you need to see if there is too much indexes on that table, and revise them to minimize their count as appropriate (if you need the index, then you should keep it).

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 06:19:05

quote:
Originally posted by anculutza

What index should I use to optimize this query?

UPDATE Coordinates
SET CountSegments=c.Ord
FROM Coordinates co INNER JOIN (SELECT MAX(Ord)AS Ord, ObjectId FROM Coordinates GROUP BY ObjectId) c ON co.ObjectId=c.ObjectId

I mention that the table Coordinate has ~130Mio records.

anculutza

Got performance problems?

Could be that you are jsut trying to do too much in one go.

Post what indexes you have currently.

If you are updating 130 million records then you may (read "probably") want to think about batching this update.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 06:50:18

"... to optimize this query?"


UPDATE Coordinates
SET CountSegments=c.Ord
...

is this always going to cause data to change? if not adding:


WHERE CountSegments<>c.Ord

might speed it up a lot

(Needs adapting if either column might be NULL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources