SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 updating TOP in sql 2000
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/07/2006 :  02:10:36  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 11/07/2006 :  02:15:48  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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/

Edited by - chiragkhabaria on 11/07/2006 02:19:03
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/07/2006 :  02:22:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/07/2006 :  02:30:47  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 11/07/2006 :  02:36:21  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
Harsh, but what for the next 2000 records??

Chirag

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

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/07/2006 :  02:46:11  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

Sweden
30265 Posts

Posted - 11/07/2006 :  02:48:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Is there a " WHERE state <> 'ZZ' " missing in the derived table?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/07/2006 :  02:55:44  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/07/2006 :  03:08:14  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 11/07/2006 :  03:13:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 11/07/2006 :  03:31:44  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/07/2006 :  03:52:58  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
If SQL2005, why not directly use UPDATE TOP clause?

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

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/07/2006 :  04:47:46  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 11/07/2006 :  05:03:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/07/2006 :  05:34:13  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 11/07/2006 :  05:43:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/07/2006 :  06:07:09  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 11/07/2006 :  06:11:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/07/2006 :  06:21:53  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 11/07/2006 :  06:23:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/07/2006 :  06:25:55  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000