Tidying data

From: Griff (Howling_at_The.Moon)
Date: 02/22/05 

Date: Tue, 22 Feb 2005 13:18:11 -0000

I have a database table with >20,000 rows and >50 fields, ~45 of which are
VARCHARS.

The data in this database is populated from a fixed-width text file using a
process that is outside of my control. However, this results in many of the
fields having leading and/or trailing spaces.

I have to find a way to tidy this data up....

I adopted the tactic of getting all the table's data back as a disconnected
recordset and iterating through every row & field. If the type is VARCHAR
and the value is not NULL, then I simply update the field's value with the
trimmed value.

Iterating through this is extremely quick.

However, the UpdateBatch command is EXTREMELY SLOW. So slow that it takes ~
5 minutes to update ~ 4000 rows.

Any alternative tactics I could use?

Thanks

Griff

Relevant Pages

  • Re: Tidying data
    ... > recordset and iterating through every row & field. ... the UpdateBatch command is EXTREMELY SLOW. ... > Any alternative tactics I could use? ...
    (microsoft.public.data.ado)
  • Re: Tidying data
    ... > recordset and iterating through every row & field. ... the UpdateBatch command is EXTREMELY SLOW. ... > Any alternative tactics I could use? ...
    (microsoft.public.sqlserver.server)
  • Tidying data
    ... I have a database table with>20,000 rows and>50 fields, ~45 of which are ... Iterating through this is extremely quick. ... the UpdateBatch command is EXTREMELY SLOW. ... Any alternative tactics I could use? ...
    (microsoft.public.sqlserver.server)
  • Re: Database Performance Problem between 3:00PM and 4:00PM
    ... where by database becomes extremely slow between 3:00PM and 4:00PM. ... FRom the beginning we suspected Network to be an issue, ... monitoring cpu, disk, memory usage..), do n''t see anything different ...
    (comp.databases.oracle.server)
  • Re: Database Performance Problem between 3:00PM and 4:00PM
    ... where by database becomes extremely slow between 3:00PM and 4:00PM. ... FRom the beginning we suspected Network to be an issue, ... monitoring cpu, disk, memory usage..), do n''t see anything different ...
    (comp.databases.oracle.server)