VincilLabs
Registered User.
- Local time
- Today, 10:02
- Joined
- May 11, 2011
- Messages
- 13
I built a process to separate first and last name and remove certain names from various tables that are imported to my database. It works great when the recordset is fairly small. But when I do say 250,000 records at one time the database bloats nearly to the point that it corrupts(the dreaded 2GB limit.). I run compact and repair and all is fine, accept I have to do this every time I run my process.
Here's the problem. Let's say I also need to standardize the address, city, state, zip at the same time. Then the database blows up to the 2GB limit and no longer functions until I repair it. The database starts at 470 MB currently and will hit 1.5 GB + when I do the name cleanup. The table I'm trying to clean is only about 250MB.
I'm not allowed to post my code but I can generalize.
Name:
sql - alter add cleanName, firstName, LastName
Open recordset to be cleaned (main)
open recordset of bad names (groups)
loop through main until eof
loop through groups until eof
if main.name = groups.name then
goto next main record
else
update recordset(main).cleanName = main.name
groups.movenext
groups.movefirst
separate name function
cleanLastname = lastNameAlg()
cleanFirstName = FirstNameAlg()
update recordset(main).lastname = cleanLastName
update recordset(main).firstName = cleanFirstName
main.movenext
main.movefirst
repeat above for address, city, state, zip (prettymuch)
'Cleanup and close everything at the end
main.close
groups.close
main = nothing
group = nothing
Here's the problem. Let's say I also need to standardize the address, city, state, zip at the same time. Then the database blows up to the 2GB limit and no longer functions until I repair it. The database starts at 470 MB currently and will hit 1.5 GB + when I do the name cleanup. The table I'm trying to clean is only about 250MB.
I'm not allowed to post my code but I can generalize.
Name:
sql - alter add cleanName, firstName, LastName
Open recordset to be cleaned (main)
open recordset of bad names (groups)
loop through main until eof
loop through groups until eof
if main.name = groups.name then
goto next main record
else
update recordset(main).cleanName = main.name
groups.movenext
groups.movefirst
separate name function
cleanLastname = lastNameAlg()
cleanFirstName = FirstNameAlg()
update recordset(main).lastname = cleanLastName
update recordset(main).firstName = cleanFirstName
main.movenext
main.movefirst
repeat above for address, city, state, zip (prettymuch)
'Cleanup and close everything at the end
main.close
groups.close
main = nothing
group = nothing