I have the following query which basically gets data from a 48 million record table, performs various things using SQL CLR C# functions then puts the results into a new table.
Initially I set the code to run on the entire table but after 3 hours the code failed due to some dodgy data in one of the columns! So now I want to perform this in chunks of 1 million records at a time.
However, as the Loading_B2C_keys_ table has more and more data, the insert is getting slower and slower!
I've tried adding a non-clustered index on the ID column on both tables but it's not having much effect? Is there a better way to do this?
By the way, the ID column appears to contain a mixture of integers and GUIDs. Should I create a new Identity column on the big table and use that as my ID? Would that help?
INSERT INTO dbo.Loading_B2C_keys_ (
SELECT top 1000000 ID,
dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(SURNAME)) + LEFT(FIRSTNAME, 1), dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(SURNAME)),
UPPER(dbo.clrFn_GetLastWord(SURNAME)) + ',' + UPPER(dbo.clrFn_GetFirstWord(FIRSTNAME)) + ',' + UPPER(dbo.clrFn_GetFirstWord(SECOND_INIT_NAME)),
dbo.clrFn_DoubleMetaphone(dbo.clrFn_RemoveDigits(ISNULL(AD1, '') + ' ' + ISNULL(AD2, ''))),
dbo.clrFn_GetDigits(ISNULL(AD1, '') + ' ' + ISNULL(AD2, '')),
FROM dbo.Loading_B2C as a
WHERE NOT EXISTS (SELECT 1 FROM dbo.Loading_B2C_keys_ as b WHERE a.ID = b.ID)
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lens
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn