I have a table with over 400,000,000 records and I am looking for advice on how to parse it quickly.
TheNameTable ( NameID int primary key, TheName varchar(500) )
The names are stored like this: "FirstName, LastName" (not my table, just what I have to work with)
I need to extract a unique list of last names. My initial thoughts are to process the table in a series of batches (say 50,000 records at a time), using the NameID to control the batch ranges. I would then use SQL's built in string functions to break the string at the "," and keep the right half of the string.
I have a feeling this will still take a LONG time.
Anyone out there have any other ideas?
Would it be worthwhile to simply export the data and process the file outside the database?
The Solution I went with:
As suggested, I created two computed columns. One for first name, one for last name. They are not persisted, as I do have limited space.
alter table TheNameTable add LastName as substring(TheName, charindex(',',TheName)+1,1000) alter table TheNameTable add FirstName as left(TheName,charindex(',',TheName)-1)
I asked the admins for a temporary increase in RAM, they bumped the VM up to 32GB.
I created a new table, with columns for both FirstName and LastName. I put a unique compound index on the columns, but specified IGNORE_DUP_KEY = ON.
I just inserted the first 1,000,000 records. It filtered out 125,000 duplicates. The whole statement took 9 seconds to run.
That is the speed I was looking for!