| Author |
 Topic  |
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
 Posted - 11/07/2006 : 06:27:30
|
quote:
Originally posted by Lumbago
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"
If that is the case then he has 2424 indexes.
quote:
CREATE INDEX [tblInstantMessage2424] ON [dbo].[tblInstantMessage]([MessageFromID], [InstantMessageID], [MessageToID], [Message], [Date], [Mobile], [deletedbySender]) ON [PRIMARY]
GO
Harsh Athalye
India.
"Nothing is Impossible" |
 |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 11/07/2006 : 06:27:43
|
Index 43 and 32 are exactly the same, as are 24 and 2424. You can safely delete one of each...
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
mike123
Flowing Fount of Yak Knowledge
1462 Posts |
Posted - 11/07/2006 : 06:30:26
|
quote:
Originally posted by Lumbago
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"
I listed all indexes above so there are 7 indexes it looks like, I probably have reoptimized the tables a bunch of times and maybe since day one I have created 43 indexes?
|
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 11/07/2006 : 07:05:30
|
I also have another recomandation that would increase performance quite a bit but in this scenario it might be considered "cheating" so you should really focus on the index issue first and when you are done you could probably create something called "horizontal partitioning".
From what I can understand messages that have deletedByRecipient and deletedBySender = 1 are deleted for the user and are only kept in the database for history preserving reasons. In that case you can create a new table like this:CREATE TABLE [dbo].[tblInstantMessage_history] (
[InstantMessageID] [int] NOT NULL,
--> identity is removed on purpose to bypass identity insert issues
[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]
GOYou also need to create a view that queries both tables like this:CREATE VIEW [dbo].[tblInstantMessage_all]
AS
SELECT * FROM tblInstantMessage
UNION ALL
SELECT * FROM tblInstantMessage_history Then you create a job that moves deleted data from tblInstantMessage to tblInstantMessage_history at some regular interval. This will most likely cause your "active" table decrease substantially in size making it way faster than it used to be. I did this for a few tables in my old job and in some of the tables only about 5% of the data were considered active...imagine the performance gain!!
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand" |
Edited by - Lumbago on 11/07/2006 07:07:13 |
|
|
|
mike123
Flowing Fount of Yak Knowledge
1462 Posts |
Posted - 11/08/2006 : 04:25:16
|
Hi Lumbago,
Thanks for the suggestions. I tried to drop one of the indexes you suggested, but got this error.
Server: Msg 3703, Level 11, State 6, Line 1
Cannot drop the index 'dbname.tblInstantMessage32', because it does not exist in the system catalog.
I've bookmarked those URL's and plan to read them asap :) My knowledge on indexes definately needs improvement right away.
The other index you suggested is actually different by one column after double checking. I don't have a problem deleting about 50% of the rows in the table to speed things up instead of archiving them, the problem so far has been the length of time its taken to delete them. Right now I am deleting about 200,000 rows per night with a query that deletes them in small batches when the server load is low. Attempting anything big halts the system to a near stop.
Hopefully after a few days or more I'll start to notice some type of improvement.
Still not sure if limiting the transaction size to 1000 rows for these UPDATE commands below is a good solution for the meantime? (and how to do it)
Any help much appreciated!!
Thanks again :)
mike123
CREATE PROCEDURE dbo.update_IM_history_deleted
(
@userID int
)
AS SET NOCOUNT ON
UPDATE tblInstantMessage SET deletedByRecipient = 1 WHERE messageToID = @userID and deletedByRecipient <> 1
UPDATE tblInstantMessage SET deletedBySender = 1 WHERE messageFromID = @userID and deletedByRecipient <> 1
GO
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/08/2006 : 04:35:00
|
How long time does the two queries posted by me at 11/07/2006 : 06:11:18 take?
Peter Larsson
Helsingborg, Sweden |
|
|
|
mike123
Flowing Fount of Yak Knowledge
1462 Posts |
Posted - 11/08/2006 : 06:04:31
|
Hi Peter,
testing on my local testing server, I ran a few tests.
Running for a 10,000 row update it took 1:07 without the clause you suggested.. the second 10,000 row update took 16 seconds
Running for another 10,000 row update, it took 20 seconds and then a second one at 18 seconds with your "AND InstantMessageID > 0"
I'm not positive this is the only reason why for performace boost? Maybe had something to do with having certain things in RAM after the first update?
During this time the system is really really slow, and a major problem.
Thanks again for your help, any suggestions appreciated
mike123
|
Edited by - mike123 on 11/08/2006 06:04:56 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/08/2006 : 06:16:25
|
It can be your disks that is the problem. Too much disk acitivity makes the system very slow.
Peter Larsson
Helsingborg, Sweden |
|
|
|
Topic |
|
updating TOP in sql 2000
