May 15, 2012 at 9:31 pm
We have a user login table, which inserts a record for each user login.
The table structure is below.
---===============
CREATE TABLE [dbo].[user_login](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[dt] [datetime] NOT NULL,
[remoteaddr] [varchar](15) NULL,
Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com) [varchar](255) NULL,
CONSTRAINT [pk_user_login] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[user_login] WITH CHECK ADD CONSTRAINT [fk_user_login_userid] FOREIGN KEY([userid])
REFERENCES [dbo].[user_master] ([userid])
ON DELETE CASCADE
CREATE NONCLUSTERED INDEX [ix_user_login_dt] ON [dbo].[user_login]
(
[dt] ASC,
[userid] ASC
)
---======================
The table has about 432522845 rows, of which admin type user records are 400669000.(About 90% records).
These records Needs to be deleted from the table.
Planning with the below approach to minimize locking and blocking.
---=================
--Disable the non clustered indexes..
ALTER INDEX ix_user_login_dt ON user_login DISABLE
GO
--Delete in batches of 50000 rows
while 1 = 1
begin
delete top (50000) from user_login where userid in (1,70075)
if @@rowcount = 0 BREAK
end
--Rebuild indexes
ALTER INDEX ix_user_login_dt ON user_login REBUILD
GO
--=========
Are there any potential drawbacks in this approach.
Would it better, if the required data is moved into a temp table, truncate the user_login table and transfer data back from the temp table.
Looking for suggestions or other alternate options.
May 15, 2012 at 9:40 pm
As 90% of the data is unwanted, you can move the 10% of the valid data to a temp table, drop the table , create a new table with the same name and populate from the temp table. If table availability is of prime importance, then truncate the table and re-populate it...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply