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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/07/2006 :  06:27:30  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:
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"
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/07/2006 :  06:27:43  Show Profile  Reply with Quote
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"
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

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

Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/07/2006 :  06:43:27  Show Profile  Reply with Quote
Hehe...what you should do instead of using the wizard is to read up a little about indexes. The concept is not that hard actually and the index tuning wizard will almost never give you optimal recomandations. Read up on these links and practice a little when you have the time, the payoff will be fenomenal:

http://www.sql-server-performance.com/gv_index_data_structures.asp
http://www.sql-server-performance.com/clustered_indexes.asp

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

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/07/2006 :  07:05:30  Show Profile  Reply with Quote
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]
GO
You 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
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/08/2006 :  04:25:16  Show Profile  Reply with Quote
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








Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 11/08/2006 :  04:35:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
How long time does the two queries posted by me at 11/07/2006 : 06:11:18 take?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/08/2006 :  06:04:31  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 11/08/2006 :  06:16:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It can be your disks that is the problem. Too much disk acitivity makes the system very slow.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.09 seconds. Powered By: Snitz Forums 2000