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)
 updating TOP in sql 2000

Author  Topic 

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-07 : 02:10:36

Hi,

I've got some update queries which I believe are really slowing down my system. I have the following SPROC below that runs. The problem is that depending on the users they can have anywhere from 0 rows to update all the way to an extreme of 50,000 rows.

When these high values run, its halting the DB server to a stop. What I am thinking is to limit the UPDATE to 2000 rows or so?

I've read that "update TOP ....." is new functionality in SQL2005, which I plan to upgrade to soon but I am looking for a way to do this in SQL2000 currently. Is there any decent way to do this??

Also, can my two update statements be combined to 1 ? If they can, do you think this is running alot of extra overhead with 2?

Thanks once again, very much appreciated!
Mike123

CREATE PROCEDURE dbo.update_IM_history_deleted
(
@userID int
)
AS SET NOCOUNT ON


UPDATE tblInstantMessage SET deletedByRecipient = 1 WHERE messageToID = @userID
UPDATE tblInstantMessage SET deletedBySender = 1 WHERE messageFromID = @userID

GO


chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-07 : 02:15:48
Not sure about the top stuff.. but for updating into one statement you can make use of the following query .

CREATE PROCEDURE dbo.update_IM_history_deleted
(
@userID int
)
AS SET NOCOUNT ON


UPDATE tblInstantMessage SET deletedByRecipient = Case When
messageToID = @userID Then 1 Else deletedByRecipient End , deletedBySender = Case When
messageFromID = @userID Then 1 Else deletedBySender End


Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 02:22:31
Add a
WHERE @UserID IN (messageToID, messageFromID)

to optimize the solution and restrict the number of valid records to update.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-07 : 02:30:47
quote:
I've read that "update TOP ....." is new functionality in SQL2005, which I plan to upgrade to soon but I am looking for a way to do this in SQL2000 currently. Is there any decent way to do this??


I think you can use SET ROWCOUNT n to limit the number of rows to be affected like this:

Set Rowcount 2000

UPDATE tblInstantMessage
SET deletedByRecipient = 1
WHERE messageToID = @userID

Set Rowcount 0



Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-07 : 02:36:21
Harsh, but what for the next 2000 records??

Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-07 : 02:46:11
The next batch of update must be differentiated by the WHERE clause, but there is one more way given in BOL (using TOP operator is subquery to reduce affected records):

quote:
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id




Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 02:48:17
Is there a " WHERE state <> 'ZZ' " missing in the derived table?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-07 : 02:55:44
If it is indeed missing, it is directly taken from BOL. But it seems it updates state regardless of its current contents.

Bit inefficient, isn't it?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-07 : 03:08:14
quote:
Originally posted by Peso

Add a
WHERE @UserID IN (messageToID, messageFromID)

to optimize the solution and restrict the number of valid records to update.


Peter Larsson
Helsingborg, Sweden



Hi Peter,

Just want to verify I'm following you properly.

Are you suggesting 2 statements as such?

UPDATE tblInstantMessage SET deletedByRecipient = 1 WHERE messageToID = @userID AND @userID IN (messageToID, messageFromID)
UPDATE tblInstantMessage SET deletedBySender = 1 WHERE messageFromID = @userID AND @userID IN (messageToID, messageFromID)

thanks again,
mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 03:13:03
No, I was suggesting to Chirag to add a WHERE as this below
UPDATE		tblInstantMessage
SET deletedByRecipient = Case
When messageToID = @userID Then 1
Else deletedByRecipient
End,
deletedBySender = Case
When messageFromID = @userID Then 1
Else deletedBySender
End
WHERE @UserID IN (messageToID, messageFromID)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-07 : 03:31:44
aha.. I guess you are using SQL Server 2005 then for updating the records you can make use of the following query, that is in batches.



UPDATE t
SET deletedByRecipient = Case
When messageToID = @userID Then 1
Else deletedByRecipient
End,
deletedBySender = Case
When messageFromID = @userID Then 1
Else deletedBySender
End
From
(
Select ROW_NUMBER()
OVER (ORDER BY MessageFromID,deletedBySender,deletedByRecipient) AS 'RowNumber',*
From update_IM_history_deleted Where @UserID IN (messageToID, messageFromID)
) As t
WHERE
RowNumber Between 1 to 2000

You can change the rownumber then from 2000 - 4000 accordingly..

and for order by clause in the select make sure that you entry a primary to that column and remove other queries.


Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-07 : 03:52:58
If SQL2005, why not directly use UPDATE TOP clause?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-07 : 04:47:46
Hi,

I'm not using SQL2005 unfortunately so I can't use that suggestion =[
Yes Peter, you are correct, I should also be adding an extra WHERE clause in this query since we want to limit the update and not update the same rows again.



These are basically what I have so far that I want to limit to the top 2000.

These 2 top queries together seems to run substantially faster than the bottom query.

I'm a little confused, is there any agreement on the best way to run this, while limiting to a set amount of rows?

Thanks again everyone for your help, its very appreciated!!!
mike123


UPDATE tblInstantMessage SET deletedByRecipient = 1 WHERE messageToID = @userID AND @userID IN (messageToID, messageFromID) AND deletedByRecipient <> 1
UPDATE tblInstantMessage SET deletedBySender = 1 WHERE messageFromID = @userID AND @userID IN (messageToID, messageFromID) AND deletedBySender <> 1







UPDATE tblInstantMessage
SET deletedByRecipient = Case
When messageToID = @userID Then 1
Else deletedByRecipient
End,
deletedBySender = Case
When messageFromID = @userID Then 1
Else deletedBySender
End
WHERE @userID IN (messageToID, messageFromID)



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 05:03:52
In your message above, there is no need to include the " AND @UserID IN (messageToID, messageFromID) " because the recordset is already restricted to = @UserID part.

However I think these two UPDATEs
UPDATE    tblInstantMessage
SET deletedByRecipient = 1
WHERE messageToID = @userID
AND deletedByRecipient <> 1

UPDATE tblInstantMessage
SET deletedBySender = 1
WHERE messageFromID = @userID
AND deletedBySender <> 1
will perform better than
UPDATE    tblInstantMessage
SET deletedByRecipient = Case
When messageToID = @userID Then 1
Else deletedByRecipient
End,
deletedBySender = Case
When messageFromID = @userID Then 1
Else deletedBySender
End
WHERE @userID IN (messageToID, messageFromID)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-07 : 05:34:13
thanks peter, your right.. this does perform best.. however I'm stuck on the best way to limit it to the top 2000 rows...... I'm really wishing I was running SQL2005 right now =[

what do you think about this way below? Would this be the best way?

UPDATE tblInstantMessage
SET deletedByRecipient = 1
WHERE messageID IN

(SELECT TOP 2000 messageID FROM tblMessage WHERE messageToID = @userID
AND deletedByRecipient <> 1)


thanks again for your continued support.. very much appreciated

mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 05:43:15
That's s fine way.
I am still confused why your database chokes on a merely 50000 records update...
Do you have a primary key?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-07 : 06:07:09
Hi Peter,

to be honest I'm confused too...could be all my indexes? there could be something obvious that I'm not aware of here.. I'm no expert =\ things USED to be fine, but now I believe since there are more rows in this table (and thus larger update batches) things have gotten exponentially worse..

Table Info

8303541 rows
5202600 KB reserved KB
1054592 KB data KB
4049240 KB index size KB
98768 KB unused KB


Here is my table.. maybe you see something that could be causing this?
Any ideas greatly appreciated...


Thanks once again,

mike123


CREATE TABLE [dbo].[tblInstantMessage] (
[InstantMessageID] [int] IDENTITY (1, 1) NOT NULL ,
[MessageToID] [int] NULL ,
[MessageFromID] [int] NULL ,
[Message] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[Checked] [tinyint] NULL ,
[Mobile] [tinyint] NULL ,
[deletedbySender] [int] NULL ,
[deletedbyRecipient] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblInstantMessage] WITH NOCHECK ADD
CONSTRAINT [PK_tblInstantMessage] PRIMARY KEY CLUSTERED
(
[InstantMessageID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblInstantMessage36] ON [dbo].[tblInstantMessage]([MessageToID], [Checked]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblInstantMessage38] ON [dbo].[tblInstantMessage]([MessageFromID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblInstantMessage25] ON [dbo].[tblInstantMessage]([MessageToID], [MessageFromID], [Date] DESC , [InstantMessageID], [Message], [Mobile]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblInstantMessage24] ON [dbo].[tblInstantMessage]([MessageFromID], [InstantMessageID], [MessageToID], [Message], [Date], [Mobile]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblInstantMessage2424] ON [dbo].[tblInstantMessage]([MessageFromID], [InstantMessageID], [MessageToID], [Message], [Date], [Mobile], [deletedbySender]) ON [PRIMARY]
GO

CREATE INDEX [tblInstantMessage43] ON [dbo].[tblInstantMessage]([MessageToID], [Checked], [deletedbyRecipient]) ON [PRIMARY]
GO

CREATE INDEX [tblInstantMessage32] ON [dbo].[tblInstantMessage]([MessageToID], [Checked], [deletedbyRecipient]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblInstantMessage] ADD
CONSTRAINT [FK_tblInstantMessage_tblUserDetails] FOREIGN KEY
(
[MessageFromID]
) REFERENCES [dbo].[tblUserDetails] (
[UserID]
) ON DELETE CASCADE
GO

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 06:11:18
That's not 50000 records. That's 8 million!
Also you have many overlapping indexes.

Try to use this one
UPDATE    tblInstantMessage
SET deletedByRecipient = 1
WHERE messageToID = @userID
AND deletedByRecipient <> 1
AND InstantMessageID > 0

UPDATE tblInstantMessage
SET deletedBySender = 1
WHERE messageFromID = @userID
AND deletedBySender <> 1
AND InstantMessageID > 0


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-07 : 06:21:53
Hi Peter,

Sorry for confusion on the row amounts :) ..I'm sure that difference is no minor issue :) What I meant is that up to 50,000 records in extreme cases could fit the criteria of the UPDATE procedure and could be modified... However most would probably be in the 5000 to 10,000 range.. however still slowing the system..

I don't understand your recommendation on "AND InstantMessageID > 0", every row in the table fits this criteria. Should I still do this?


I have created all the indexes with the Index Tuning Wizard, this is probably how I got duplicate indexes. I'm guessing these duplicate indexes are probably slowing down the UPDATE statements even more, not providing any SELECT speed increase, and should be removed ? I'm not very experienced in using anything regarding indexes without the wizard so I'll have to spend some time on fixing this I think :)


Do you still think I should this query to limit the rows to 2000 ? With your recommendations I have the below... am I following you correctly?


UPDATE tblInstantMessage
SET deletedByRecipient = 1
WHERE messageID IN

(SELECT TOP 2000 messageID FROM tblMessage WHERE messageToID = @userID
AND deletedByRecipient <> 1 AND InstantMessageID > 0)

Thanks again!!,
Mike
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-07 : 06:23:06
Ok, just out of curiosity...does "tblInstantMessage43" mean that you have 43 indexes on this table??

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-07 : 06:25:55
By adding this:

AND InstantMessageID > 0

query optimizer will be able to take advantage of clustered index in UPDATE operation.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -