February 13, 2023 at 4:36 pm
Hi
I need to anonymize some of our members for an outward facing demo web site.
Long ugly story, but basically it has to be a mass update of our existing (restored copy) data.
Problem is I don't have much disk space so I am trying to batch them in. If I do an update without the batch it fills the log and fails (even in bulk logged or simple)
But its been four days now without finishing - starting to think there must be a better way to do this? Maybe improve my query somehow? I added a clustered for the updated fields but seem to slow things down.
SET NOCOUNT ON
DBCC TRACEON (9481, -1);
DECLARE @_batchSize int = 175000
DECLARE @sprocStart DATETIME = GETDATE()
DECLARE @printMsg VARCHAR(2000) = ''
DECLARE @debugOutput BIT = 1
DECLARE @sprocLastPrint DATETIME = GETDATE()
DECLARE @curCount INT = 0
SELECT @curCount=COUNT(1) FROM dbo.member WHERE COALESCE(os_user_name, '')<>'anon'
while (@curCount>0) begin
begin tran;
IF @debugOutput=1 BEGIN SET @printMsg = 'processing ' + FORMAT(@_batchSize, '0') + ' - ' + FORMAT(@curCount, '0') +' remaining. ' + FORMAT(GETDATE(), ' yyyy/MM/dd HH:mm:ss') + ' +' + CAST(DATEDIFF(SECOND, @sprocStart, GETDATE()) / 60 AS VARCHAR(50)) + ':' + CAST(DATEDIFF(SECOND, @sprocStart, GETDATE()) % 60 AS VARCHAR(50))+ ' Since sProc Start. +' + CAST(DATEDIFF(SECOND, @sprocLastPrint, GETDATE()) / 60 AS VARCHAR(50)) + ':' + CAST(DATEDIFF(SECOND, @sprocLastPrint, GETDATE()) % 60 AS VARCHAR(50)) +' Since Last Step.' RAISERROR(@printMsg , 10, 0) WITH NOWAIT SET @sprocLastPrint = GETDATE() END
UPDATE jX SET os_user_name = 'anonProcBatch' + FORMAT(xRow, '0000000')
FROM
(SELECT TOP (@_batchSize) os_user_name, ROW_NUMBER () OVER (ORDER BY member_id) xRow FROM dbo.member WHERE COALESCE(os_user_name, '')<>'anon' ) jX
UPDATE
member
SET
first_name = xFN.first_name
,last_name = xLN.last_name
,middle_name = xMN.middle_name
,hicn= 'HICN' + FORMAT ( member_id, '000000000' )
,medicare_beneficiary_identifier = 'MBI' + + FORMAT ( member_id, '00000000' )
,dob= DATEADD ( DAY, RIGHT(member_id, 1) + 1, dob )
,os_user_name = 'anon'
FROMdbo.member
JOIN (SELECT first_name, 'anonProcBatch' + format(ROW_NUMBER () OVER (ORDER BY NEWID()), '0000000') os_user_name FROM member WHERE os_user_name LIKE 'anonProcBatch[0-9]%') xFN
ON member.os_user_name = xFN.os_user_name
JOIN (SELECT last_name, 'anonProcBatch' + format(ROW_NUMBER () OVER (ORDER BY NEWID()), '0000000') os_user_name FROM member WHERE os_user_name LIKE 'anonProcBatch[0-9]%') xLN
ON member.os_user_name = xLN.os_user_name
JOIN (SELECT middle_name, 'anonProcBatch' + format(ROW_NUMBER () OVER (ORDER BY NEWID()), '0000000') os_user_name FROM member WHERE os_user_name LIKE 'anonProcBatch[0-9]%') xMN
ON member.os_user_name = xMN.os_user_name
WHERE
member.os_user_name LIKE 'anonProcBatch[0-9]%'
commit;
SELECT @curCount=COUNT(1) FROM dbo.member WHERE COALESCE(os_user_name, '')<>'anon'
end;
-- now w/ members renamed - tag their claims also
UPDATE dbo.claim SET member_first_name = first_name,
member_last_name = last_Name,
member_middle_name = middle_name
FROM dbo.claimJOIN dbo.member ON
member.member_id = claim.member_id
February 13, 2023 at 5:09 pm
Why do you COALESCE (os_user_name, '')<>'anon'? If os_user_name is null it is different than anon
Why do you make @_batchSize dynamic?
You might want to use a temporary table to store your batchselection (top ...) to anomize so it doesn't need the orgininal table to join with
I need more coffee
What is the purpose of
JOIN (SELECT first_name, 'anonProcBatch' + format(ROW_NUMBER () OVER (ORDER BY NEWID()), '0000000') os_user_name FROM member WHERE os_user_name LIKE 'anonProcBatch[0-9]%') xFN
ON member.os_user_name = xFN.os_user_name
February 13, 2023 at 5:54 pm
Create a view that anonymizes the names in a select of the Member table.
BCP out the view.
Empty the Member table.
BCP into the Member table.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 13, 2023 at 11:20 pm
Also, please read the following with the understanding that it also applies to numerics or anything else that could be formatted.
https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2023 at 12:47 am
Hi Jeff,
Sure! Thanks!
USE [PEC_PROD]
GO
/****** Object: Table [dbo].[member] Script Date: 2/13/2023 7:39:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[member](
[member_id] [int] IDENTITY(1,1) NOT NULL,
[last_name] [varchar](70) NOT NULL,
[first_name] [varchar](70) NULL,
[middle_name] [varchar](70) NULL,
[name_prefix] [varchar](5) NULL,
[name_suffix] [varchar](10) NULL,
[ssn] [varchar](35) NULL,
[hicn] [varchar](35) NULL,
[medicare_beneficiary_identifier] [varchar](11) NULL,
[dob] [datetime] NOT NULL,
[dod] [datetime] NULL,
[gender] [char](1) NOT NULL,
[ethnic_group_id] [int] NULL,
[language_id] [int] NULL,
[pend_claims] [bit] NOT NULL,
[subscriber_id] [int] NULL,
[relationship_id] [int] NOT NULL,
[employer_group_dept] [varchar](10) NULL,
[employer_group_location] [varchar](10) NULL,
[hire_date] [datetime] NULL,
[seperation_date] [datetime] NULL,
[last_hipaa_printing] [datetime] NULL,
[marital_status_id] [int] NULL,
[employment_status_id] [int] NULL,
[continuous_coverage_date] [datetime] NULL,
[exclude_from_bill] [bit] NOT NULL,
[gender_id] [int] NULL,
[location_code_id] [int] NULL,
[class_id] [int] NULL,
[customer_id] [int] NULL,
[vendor_id] [int] NULL,
[department_id] [int] NULL,
[division_id] [int] NULL,
[loc_off] [varchar](5) NULL,
[aid_category_code] [varchar](4) NULL,
[active] [bit] NOT NULL,
[os_user_name] [nvarchar](128) NULL,
[date_modified] [datetime] NOT NULL,
[sql_user_name] [nvarchar](128) NOT NULL,
[date_created] [datetime] NOT NULL,
[timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_member] PRIMARY KEY NONCLUSTERED
(
[member_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[member] ADD CONSTRAINT [DF_member_pend_claims] DEFAULT ((0)) FOR [pend_claims]
GO
ALTER TABLE [dbo].[member] ADD CONSTRAINT [DF_member_exclude_from_bill] DEFAULT ((0)) FOR [exclude_from_bill]
GO
ALTER TABLE [dbo].[member] ADD CONSTRAINT [DF_member_active] DEFAULT ((1)) FOR [active]
GO
ALTER TABLE [dbo].[member] ADD CONSTRAINT [DF_member_os_user_name] DEFAULT ('unknown') FOR [os_user_name]
GO
ALTER TABLE [dbo].[member] ADD CONSTRAINT [DF_member_date_modified] DEFAULT (getdate()) FOR [date_modified]
GO
ALTER TABLE [dbo].[member] ADD CONSTRAINT [DF_member_sql_user_name] DEFAULT (suser_name()) FOR [sql_user_name]
GO
ALTER TABLE [dbo].[member] ADD CONSTRAINT [DF_member_date_created] DEFAULT (getdate()) FOR [date_created]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_class] FOREIGN KEY([class_id])
REFERENCES [dbo].[class] ([class_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_class]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_customer] FOREIGN KEY([customer_id])
REFERENCES [dbo].[customer] ([customer_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_customer]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_department] FOREIGN KEY([department_id])
REFERENCES [dbo].[department] ([department_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_department]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_division] FOREIGN KEY([division_id])
REFERENCES [dbo].[division] ([division_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_division]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_employment_status] FOREIGN KEY([employment_status_id])
REFERENCES [dbo].[employment_status] ([employment_status_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_employment_status]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_ethnic_group] FOREIGN KEY([ethnic_group_id])
REFERENCES [dbo].[ethnic_group] ([ethnic_group_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_ethnic_group]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_gender] FOREIGN KEY([gender_id])
REFERENCES [dbo].[gender] ([gender_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_gender]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_language] FOREIGN KEY([language_id])
REFERENCES [dbo].[language] ([language_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_language]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_marital_status] FOREIGN KEY([marital_status_id])
REFERENCES [dbo].[marital_status] ([marital_status_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_marital_status]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_relationship] FOREIGN KEY([relationship_id])
REFERENCES [dbo].[relationship] ([relationship_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_relationship]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_subscriber] FOREIGN KEY([subscriber_id])
REFERENCES [dbo].[member] ([member_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_subscriber]
GO
ALTER TABLE [dbo].[member] WITH CHECK ADD CONSTRAINT [FK_member_vendor] FOREIGN KEY([vendor_id])
REFERENCES [dbo].[vendor] ([vendor_id])
GO
ALTER TABLE [dbo].[member] CHECK CONSTRAINT [FK_member_vendor]
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'member_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Last name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'last_name'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'First Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'first_name'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Middle Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'middle_name'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Name prefix' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'name_prefix'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Name suffix' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'name_suffix'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'PID on member form. This is the primary identification number, ssn etc.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'ssn'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Health Insurance Claim Number - Medicare beneficiary identification number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'hicn'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Medicare Beneficiary Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'medicare_beneficiary_identifier'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Date of Birth' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'dob'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Date of Death' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'dod'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Gender that is used in processing - the left 1 character of the gender_ud from the gender table that is selected on the form is stored here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'gender'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to ethnic group table - Ethnic Group ID - not visible on member form.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'ethnic_group_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to language table. The primary language used by the member' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'language_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'When set to true (1, checked) Pend all claims for this member during adjudication.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'pend_claims'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to member_id of member table. This is the subscriber of the member. When self subscribed, this is = to the member_id. MUST be populated as accumulator history is gathered by subscriber_id. Accumulation will not occur otherwise. Trigger tr_plx_member_inserttr_plx_member_insert auto sets this if relationship is SELF.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'subscriber_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to relationship table. This is the member relationship to the subscriber. Member form auto sets this to SELF when member is created on the Subscriber tab. Only visible on the Dependent tab of member form. Used by trigger tr_plx_member_insert when SELF. If scripting in a subscriber, set this to SELF.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'relationship_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Legacy' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'employer_group_dept'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Legacy' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'employer_group_location'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Hire date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'hire_date'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Date no longer employed' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'seperation_date'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Last time hippaa printing. Not exposed on member form. May be legacy or used by reports.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'last_hipaa_printing'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to marital status table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'marital_status_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to employment_status table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'employment_status_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Date for continuous coverage. Used by waiting period processing in adjudication.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'continuous_coverage_date'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Exclude this member from premium billing calculation. Since eligibilities cannot be termed before they are effective, and dates do not save with a time, they are alway effective for one day. This is a way to not bill for the one day that the member is effective.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'exclude_from_bill'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Legacy - gender is not stored here as a FK, but rather the left 1 character of the gender_ud from the gender table is stored in the gender field.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'gender_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to location code table. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'location_code_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to class table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'class_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to customer table. Assigned customer record for this member. Used for premium billing a Subscriber.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'customer_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to vendor table. Assigned vendor record for this member. Used for paying a member for a claim.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'vendor_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to department table. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'department_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'FK to division table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'division_id'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Legacy' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'loc_off'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Legacy' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'aid_category_code'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'May not be implemented for this table but this determines if a row is active. Used to keep a record from being viewed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'active'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Operating system user name of user that made last modification.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'os_user_name'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Date row was last modified.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'date_modified'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'The Database user name of user that made the last modification.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'sql_user_name'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'The date the row was created.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'date_created'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Internal database timestamp.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member', @level2type=N'COLUMN',@level2name=N'timestamp'
GO
EXEC sys.sp_addextendedproperty @name=N'TableDesc', @value=N'Member/Patient entity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'member'
GO
USE [PEC_PROD]
GO
/****** Object: Table [dbo].[claim] Script Date: 2/13/2023 7:43:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[claim](
[claim_id] [int] IDENTITY(1,1) NOT NULL,
[claim_ud] [varchar](15) NOT NULL,
[claim_pend_code_id] [int] NULL,
[omap_claim_type_id] [int] NULL,
[claim_form_type_id] [tinyint] NULL,
[institutional_claim_form_type] [tinyint] NULL,
[eligibility_id] [int] NULL,
[received_date] [datetime] NULL,
[clean_claim_date] [datetime] NULL,
[invoice_number] [varchar](35) NULL,
[rel_to_employment] [bit] NOT NULL,
[rel_to_auto] [bit] NOT NULL,
[rel_to_other] [bit] NOT NULL,
[other_plan] [bit] NOT NULL,
[date_of_current] [datetime] NULL,
[begin_disabled_date] [datetime] NULL,
[end_disabled_date] [datetime] NULL,
[same_similar_date] [datetime] NULL,
[referring_provider_id] [int] NULL,
[referring_provider_taxonomy_code] [varchar](10) NULL,
[referral_id] [int] NULL,
[begin_hospital_date] [datetime] NULL,
[end_hospital_date] [datetime] NULL,
[fed_tax_id] [varchar](15) NULL,
[fed_tax_id_type] [char](1) NULL,
[provider_id_map_id] [int] NOT NULL,
[facility_id] [int] NULL,
[claim_status_id] [int] NULL,
[member_id] [int] NULL,
[editing] [bit] NOT NULL,
[patient_number] [varchar](38) NULL,
[drg_id] [int] NULL,
[claim_submitter_file_id] [int] NULL,
[member_first_name] [varchar](35) NULL,
[member_last_name] [varchar](35) NULL,
[member_middle_name] [varchar](35) NULL,
[provider_first_name] [varchar](35) NULL,
[provider_last_name] [varchar](35) NULL,
[provider_middle_name] [varchar](35) NULL,
[tpa_check_number] [varchar](30) NULL,
[tpa_check_date] [smalldatetime] NULL,
[provider_id] [int] NULL,
[provider_taxonomy_code] [varchar](10) NULL,
[first_from_service_date] [datetime] NULL,
[last_to_service_date] [datetime] NULL,
[subscriber_member_id] [int] NULL,
[subscriber_eligibility_id] [int] NULL,
[benefitplan_id] [int] NULL,
[referral_ud] [varchar](35) NULL,
[user_1] [varchar](35) NULL,
[user_2] [varchar](35) NULL,
[claim_export_run_id] [int] NULL,
[claim_export_head_stat_id] [int] NULL,
[member_cob_id] [int] NULL,
[accept_assignment] [bit] NOT NULL,
[first_drop_date] [datetime] NULL,
[last_drop_date] [datetime] NULL,
[outside_lab] [bit] NOT NULL,
[outside_lab_charges] [money] NULL,
[medicaid_resubmission_code] [varchar](15) NULL,
[original_ref_number] [varchar](25) NULL,
[reserved_for_local_1] [varchar](25) NULL,
[reserved_for_local_2] [varchar](25) NULL,
[group_number] [varchar](15) NULL,
[location_number] [varchar](15) NULL,
[updated] [bit] NOT NULL,
[in_accounting] [bit] NOT NULL,
[zipcode_id] [int] NULL,
[claim_payment_type_id] [int] NULL,
[practice_office_id] [int] NULL,
[cr2_service_id] [int] NULL,
[network_id] [int] NULL,
[vendor_id] [int] NULL,
[group_npi] [int] NULL,
[place_of_service_id] [int] NULL,
[document_locator_number] [varchar](255) NULL,
[currency_id] [int] NULL,
[exchange_rate_date] [datetime] NULL,
[pay_to_id] [int] NULL,
[exchange_rate] [decimal](14, 7) NULL,
[originating_currency_id] [int] NULL,
[rate_calculation_method] [char](1) NULL,
[provider_signature_on_file] [bit] NOT NULL,
[assignment_of_benefits] [bit] NOT NULL,
[release_of_info] [bit] NOT NULL,
[accident_state_ud] [char](2) NULL,
[accident_country_ud] [char](3) NULL,
[claim_special_program_id] [int] NULL,
[claim_delay_reason_id] [int] NULL,
[patient_amount_paid] [money] NULL,
[accident_date] [datetime] NULL,
[edi_claim_status_id] [int] NULL,
[apply_payment_penalty] [bit] NOT NULL,
[paper_claim] [bit] NOT NULL,
[active] [bit] NOT NULL,
[sql_user_name] [nvarchar](128) NOT NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NOT NULL,
[os_user_name] [nvarchar](128) NULL,
[timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_claim] PRIMARY KEY CLUSTERED
(
[claim_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UQ_claim] UNIQUE NONCLUSTERED
(
[claim_ud] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_rel_to_employment] DEFAULT ((0)) FOR [rel_to_employment]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_rel_to_auto] DEFAULT ((0)) FOR [rel_to_auto]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_rel_to_other] DEFAULT ((0)) FOR [rel_to_other]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_other_plan] DEFAULT ((0)) FOR [other_plan]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_claim_status_id] DEFAULT ((1)) FOR [claim_status_id]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_editing] DEFAULT ((0)) FOR [editing]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_accept_assignment] DEFAULT ((0)) FOR [accept_assignment]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_outside_lab] DEFAULT ((0)) FOR [outside_lab]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_updated] DEFAULT ((0)) FOR [updated]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_in_accounting] DEFAULT ((0)) FOR [in_accounting]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_provider_signature_on_file] DEFAULT ((0)) FOR [provider_signature_on_file]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_assignment_of_benefits] DEFAULT ((0)) FOR [assignment_of_benefits]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_release_of_info] DEFAULT ((0)) FOR [release_of_info]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_apply_payment_penalty] DEFAULT ((0)) FOR [apply_payment_penalty]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_paper_claim] DEFAULT ((0)) FOR [paper_claim]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_active] DEFAULT ((1)) FOR [active]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_sql_user_name] DEFAULT (suser_name()) FOR [sql_user_name]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_date_created] DEFAULT (getdate()) FOR [date_created]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_date_modified] DEFAULT (getdate()) FOR [date_modified]
GO
ALTER TABLE [dbo].[claim] ADD CONSTRAINT [DF_claim_os_user_name] DEFAULT ('unknown') FOR [os_user_name]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_cr2_service] FOREIGN KEY([cr2_service_id])
REFERENCES [dbo].[cr2_service] ([cr2_service_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_cr2_service]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_currency] FOREIGN KEY([currency_id])
REFERENCES [dbo].[currency] ([currency_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_currency]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_delay_reason] FOREIGN KEY([claim_delay_reason_id])
REFERENCES [dbo].[claim_delay_reason] ([claim_delay_reason_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_delay_reason]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_form_type] FOREIGN KEY([claim_form_type_id])
REFERENCES [dbo].[claim_form_type] ([claim_form_type_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_form_type]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_network] FOREIGN KEY([network_id])
REFERENCES [dbo].[network] ([network_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_network]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_pay_to] FOREIGN KEY([pay_to_id])
REFERENCES [dbo].[pay_to] ([pay_to_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_pay_to]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_payment_type] FOREIGN KEY([claim_payment_type_id])
REFERENCES [dbo].[claim_payment_type] ([claim_payment_type_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_payment_type]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_place_of_service] FOREIGN KEY([place_of_service_id])
REFERENCES [dbo].[place_of_service] ([place_of_service_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_place_of_service]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_practice_office] FOREIGN KEY([practice_office_id])
REFERENCES [dbo].[practice_office] ([practice_office_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_practice_office]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_special_program] FOREIGN KEY([claim_special_program_id])
REFERENCES [dbo].[claim_special_program] ([claim_special_program_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_special_program]
GO
ALTER TABLE [dbo].[claim] WITH CHECK ADD CONSTRAINT [FK_claim_vendor] FOREIGN KEY([vendor_id])
REFERENCES [dbo].[vendor] ([vendor_id])
GO
ALTER TABLE [dbo].[claim] CHECK CONSTRAINT [FK_claim_vendor]
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'May not be implemented for this table but this determines if a row is active. Used to keep a record from being viewed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'claim', @level2type=N'COLUMN',@level2name=N'active'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'The Database user name of user that made the last modification.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'claim', @level2type=N'COLUMN',@level2name=N'sql_user_name'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'The date the row was created.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'claim', @level2type=N'COLUMN',@level2name=N'date_created'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Date row was last modified.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'claim', @level2type=N'COLUMN',@level2name=N'date_modified'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Operating system user name of user that made last modification.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'claim', @level2type=N'COLUMN',@level2name=N'os_user_name'
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnDesc', @value=N'Internal database timestamp.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'claim', @level2type=N'COLUMN',@level2name=N'timestamp'
GO
EXEC sys.sp_addextendedproperty @name=N'TableDesc', @value=N'Table for claim header data' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'claim'
GO
Member has 9525230 rows
Claim has 9985147 rows
Compatibility level is SQL2014 (120)
Recovery model is - simple
February 14, 2023 at 1:29 am
You need to first work out which query is taking the longest then look at optimising that.
You could put some logging in the query to write to a table how long each statement takes.
February 14, 2023 at 3:05 pm
Thank you for the DDL. I'll take a look at that and your code tonight after work tonight.
Just to double check after a quick scan... I see no Clustered Index on the dbo.Member table... is that actually the condition of that table?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2023 at 7:28 pm
You can probably get a significant boost to performance by removing FORMAT and converting those JOINS to a single CROSS APPLY.
It appears you are looking up the anonymized user information from the same table that you are updating. What exactly is that code supposed to be doing?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 15, 2023 at 5:24 pm
I suspect that the sub queries used to get a random row for each name are performing a table scan. It seems that you identify a batch by updating the os_user_name and then query the member table for rows where the os_user_name matches the pattern. With no index on os_user_name it seems likely a table scan is needed.
FROM dbo.member
JOIN (SELECT first_name, 'anonProcBatch' + format(ROW_NUMBER () OVER (ORDER BY NEWID()), '0000000') os_user_name FROM member WHERE os_user_name LIKE 'anonProcBatch[0-9]%') xFN
ON member.os_user_name = xFN.os_user_name
It might help to pre-assign member rows to a batch by adding a BatchID column and updating it so that 175k random rows get BatchID = 1 etc. If you create an index on BatchID with an include for first_name, last_name and middle_name, the sub queries may be faster if you include a @BatchID variable in the joins, rather than the filter on os_user_name.
FROM dbo.member AS m
JOIN (SELECT first_name, 'anonProcBatch' + format(ROW_NUMBER () OVER (ORDER BY NEWID()), '0000000') os_user_name FROM member WHERE BatchID = @BatchID) xFN
ON member.os_user_name = xFN.os_user_name
JOIN (SELECT last_name, 'anonProcBatch' + format(ROW_NUMBER () OVER (ORDER BY NEWID()), '0000000') os_user_name FROM member WHERE BatchID = @BatchID) xLN
ON member.os_user_name = xLN.os_user_name
JOIN (SELECT middle_name, 'anonProcBatch' + format(ROW_NUMBER () OVER (ORDER BY NEWID()), '0000000') os_user_name FROM member WHERE BatchID = @BatchID) xMN
ON member.os_user_name = xMN.os_user_name
WHERE b.BatchID = @BatchID
The code will still perform the row_number on the batch three times to get a random row to join to, but the selection of the rows making up the batch might be faster. I would add the BatchID column and the index and test whether a single batch runs any faster. If it does then you just need to iterate through the batches. This might not help, but it seems quite an easy test without fundamentally redesigning the code.
I would also update the claims table in batches. If the number of members is large enough to make updating members difficult, I assume there are many millions of claims which might be too much for a single transaction.
February 16, 2023 at 1:15 am
Heh... Ed... as Jeffrey Williams also recommended, my sincere and very friendly recommendation is that you stop using FORMAT. It's 17 times slower that just about any other rabbit you could pull out of the hat even when the rabbit seems to have 9 legs and a half dozen ears 😀 .
Please see the following article that demonstrates all of that in a very "Alice's Restaurant" fashion. The article is about dates but it also applies to other things.
https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2023 at 1:23 am
You can probably get a significant boost to performance by removing FORMAT and converting those JOINS to a single CROSS APPLY.
It appears you are looking up the anonymized user information from the same table that you are updating. What exactly is that code supposed to be doing?
The OP has done a restore of a database and wants to anonymize some columns. I'm working on a suggestion but here are the basic columns he wants update. Here are the stats on the tables that he provided previously and some other useful information that I asked for.
Member has 9525230 rows
Claim has 9985147 rows
Compatibility level is SQL2014 (120)
Recovery model is - simple
And here's the main thrust (a part of the OP's code) that he wants to anonymize...
UPDATE member
SET first_name = xFN.first_name
,last_name = xLN.last_name
,middle_name = xMN.middle_name
,hicn = 'HICN' + FORMAT(member_id, '000000000')
,medicare_beneficiary_identifier = 'MBI' + FORMAT(member_id, '00000000')
,dob = DATEADD(DAY, RIGHT(member_id, 1) + 1, dob)
,os_user_name = 'anon'
FROM dbo.member
...followed by a whole lot of slow stuff...
Heh... and no... that's NOT me using FORMAT. That's in the OP's orginal code.
He's also posted the complete DDL for both tables above that he wants to update.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2023 at 1:33 am
Ok... I'm playing with a copy of the Person.Person table from a copy of the Adventure Works database. I created 3 "scratch" tables, one for each name column, and populated them with an IDENTITY (as the CI) and a unique name derived from the appropriate column of the Person.Person table. I then use those as the random source of names and it all seems very fast but that's from looking at the execution plan, which I don't trust when it comes to performance.
So I have to make a much larger table to test with... but I'm out of time for the night.
p.s. I'm thinking a "Modulus Join" on each of these names tables might be MUCH faster than discrete randomized lookups but, again, that's going to have to wait.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2023 at 3:00 am
Thanks Jeff!
Hey wait...So format not so great? Googling now...
February 16, 2023 at 3:09 am
Heh... Ed... as Jeffrey Williams also recommended, my sincere and very friendly recommendation is that you stop using FORMAT.
Trust me, I don’t use format specifically because of your previous remarks. I changed as little as possible to illustrate a point.
The padding of the rownumber with zeros doesn’t appear to serve any purpose so I would probably get rid of it rather than improve it.
February 16, 2023 at 3:34 am
Thanks Jeff!
Hey wait...So format not so great? Googling now...
Don't "Google It". Most people don't have a clue about it. Go read the article I provided the link for. It has code the proves it. The article only contains 2 common examples. If you need more coded examples, I have more. FORMAT is a poison-centered M&M with a sparkly candy-coating. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply