Update query tuning. - 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)

Update query tuning.

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2008-12-23 : 00:02:43

Hi Everyone,

I need to have a small update query tuned up.

The current query goes something like this:

Update Table1
set id = (select min(id) from table2 t2 where t2.logid = table1.logid and t2.id > table1.id)

Can anyone help me get rid of the sub query by using the update/from/where clause?

Thank you

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-23 : 00:11:09

Try This

Update t1
set id = min(t2.id)
from table1 t1
inner join table2 t2 on t2.logid = t1.logid and t2.id > t1.id

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-23 : 00:12:20

update t1
set id = min(t2.id)
from table1 t1
inner join
table2 t2 on t2.logid = t1.logid
WHERE
t2.id > t1.id

xpandre
Posting Yak Master

212 Posts

Posted - 2008-12-23 : 00:26:33

Awesome guys..Thank you.
I was really thinking in lines of using an inline view 'n all, but I guess it could be done as simple as this :=)
Thanks Again Guys.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-23 : 01:20:50

you're welcome

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-23 : 01:34:49

Welcome

Jai Krishna

xpandre
Posting Yak Master

212 Posts

Posted - 2008-12-23 : 01:41:14

Hi Jai n BKLR,

It gives me "An aggregate may not appear in the set list of an UPDATE statement."

Thank You

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-23 : 02:00:52

just declare a variable and take the min value into and then update the statement

DECLARE @value INT
SELECT @value = min(id) from table2 t2
inner join table1 t1 ON t2.logid = table1.logid
WHERE t2.id > table1.id

update t1
set id = @value
from table1 t1
inner join
table2 t2 on t2.logid = t1.logid
WHERE
t2.id > t1.id

xpandre
Posting Yak Master

212 Posts

Posted - 2008-12-23 : 02:26:51

Thanks BKLR..But we were actually looking for a solution which involved just 1 sql, the UPDATE.
Your solution was out last resort.

If we do get the solution, we shall post the same in here.

Thank you.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-23 : 03:57:47

welcome

if u want in 1 sql statement then u can use by subquery only
i think so.......

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-23 : 04:23:35

Try this

update t1
set t1.id = p.id
from ( select logid,min(id) as id from table2 group by logid ) p
inner join table1 t1 on t1.logid = p.logid and p.id > t1.id

xpandre
Posting Yak Master

212 Posts

Posted - 2008-12-23 : 05:06:10

Hi,

This is the scenario:
table1:
ID LOGID NEXTLOG
1 3 NULL
1 4 NULL
1 5 NULL

UPDATE TABLE1
SET NEXTLOG = ( select MIN(iq.logId) FROM TABLE1 iq WHERE iq.id = TABLE1.id and iq.logId > TABLE1.logId )

table1
ID LOGID NEXTLOG
1 3 4
1 4 5
1 5 NULL

I hope I am clear enough..

Thank you

xpandre
Posting Yak Master

212 Posts

Posted - 2008-12-23 : 07:44:30

Hi,

I was trying to avaoid sub query because of performance issues.

We have modified it to look like this:

CREATE TABLE #E(ID INT, logId INT, M1 INT)

CREATE INDEX IXZ1 ON #E(LOGID, ID)
INSERT INTO #E
select #LogRecs.ID, #LogRecs.logId, MIN(iq.logId) M1
FROM #LogRecs, #LogRecs iq WHERE iq.id = #LogRecs.id AND iq.logId > #LogRecs.logId
GROUP BY #LogRecs.ID, #LogRecs.logId

UPDATE T
SET NextLogId = M1
FROM #LogRecs T, #E T1
WHERE T1.LOGID = T.LOGID AND T1.ID = T.ID

DROP TABLE #E

This has improved performance by quite significant time..earlier 47k records were processed in 15 mins now..its done in 9 mins.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources