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 = @userIDUPDATE tblInstantMessage SET deletedBySender = 1 WHERE messageFromID = @userIDGO |
|
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 ONUPDATE tblInstantMessage SET deletedByRecipient = Case When messageToID = @userID Then 1 Else deletedByRecipient End , deletedBySender = Case When messageFromID = @userID Then 1 Else deletedBySender End Chiraghttp://chirikworld.blogspot.com/ |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 2000UPDATE tblInstantMessage SET deletedByRecipient = 1 WHERE messageToID = @userIDSet Rowcount 0 Harsh AthalyeIndia."Nothing is Impossible" |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-11-07 : 02:36:21
|
Harsh, but what for the next 2000 records??Chiraghttp://chirikworld.blogspot.com/ |
|
|
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 authorsSET state = 'ZZ' FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1WHERE authors.au_id = t1.au_id
Harsh AthalyeIndia."Nothing is Impossible" |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 AthalyeIndia."Nothing is Impossible" |
|
|
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 LarssonHelsingborg, 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 |
|
|
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 belowUPDATE tblInstantMessageSET 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 LarssonHelsingborg, Sweden |
|
|
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. Chiraghttp://chirikworld.blogspot.com/ |
|
|
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 AthalyeIndia."Nothing is Impossible" |
|
|
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!!!mike123UPDATE 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 tblInstantMessageSET 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) |
|
|
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 UPDATEsUPDATE tblInstantMessageSET deletedByRecipient = 1WHERE messageToID = @userID AND deletedByRecipient <> 1UPDATE tblInstantMessageSET deletedBySender = 1WHERE messageFromID = @userID AND deletedBySender <> 1 will perform better thanUPDATE tblInstantMessageSET 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 LarssonHelsingborg, Sweden |
|
|
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 tblInstantMessageSET deletedByRecipient = 1WHERE messageID IN(SELECT TOP 2000 messageID FROM tblMessage WHERE messageToID = @userIDAND deletedByRecipient <> 1)thanks again for your continued support.. very much appreciatedmike123 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 Info8303541 rows5202600 KB reserved KB1054592 KB data KB4049240 KB index size KB98768 KB unused KBHere is my table.. maybe you see something that could be causing this?Any ideas greatly appreciated...Thanks once again, mike123CREATE 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]GOALTER 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]GOALTER TABLE [dbo].[tblInstantMessage] ADD CONSTRAINT [FK_tblInstantMessage_tblUserDetails] FOREIGN KEY ( [MessageFromID] ) REFERENCES [dbo].[tblUserDetails] ( [UserID] ) ON DELETE CASCADE GO |
|
|
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 oneUPDATE tblInstantMessageSET deletedByRecipient = 1WHERE messageToID = @userID AND deletedByRecipient <> 1 AND InstantMessageID > 0UPDATE tblInstantMessageSET deletedBySender = 1WHERE messageFromID = @userID AND deletedBySender <> 1 AND InstantMessageID > 0 Peter LarssonHelsingborg, Sweden |
|
|
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 tblInstantMessageSET deletedByRecipient = 1WHERE messageID IN(SELECT TOP 2000 messageID FROM tblMessage WHERE messageToID = @userIDAND deletedByRecipient <> 1 AND InstantMessageID > 0)Thanks again!!,Mike |
|
|
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" |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-07 : 06:25:55
|
By adding this:AND InstantMessageID > 0query optimizer will be able to take advantage of clustered index in UPDATE operation.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
Previous Page&nsp;
Next Page
|