by I have a series of updates that are taking forever to run (literally
overnight and still incomplete) I'm trying to reformat some data for a
crystal report. Currently, my table structure is set up as:
TABLE 1 (contains approx 49,000 records)
global_id,q_name,q_value
for the report, I need it as
TABLE 2
global_id,q_name,q_value,unit_num,duns_num,industry
where all of the new fields are q_values from the appropriate q_name
within some of the records. the unit number groups some of the records
as well (meaning that if 1 record in a unit has null values for any of
the additional fields, any other record sharing that unit number can be
used to populate the fields)
my current code is running a series of self-join updates to switch the
data around:
update s1 set s1.unit_num = s2.q_value from siteprofs1 s1,siteprofs1 s2
where s1.unit_num is null and s2.q_name = 'unit_num' and s1.global_id =
s2.global_id
update s1 set s1.industry = s2.q_value from siteprofs1 s1,siteprofs1 s2
where s1.industry is null and s2.q_name = 'industry' and s1.global_id =
s2.global_id
update s1 set s1.duns_num = s2.q_value from siteprofs1 s1,siteprofs1 s2
where s1.duns_num is null and s2.q_name = 'duns_num' and s1.global_id =
s2.global_id
after this series is done, I have to repeat basically the same code
again linking the self join on unit_num because not all records have
complete demographic data (data consistency was not built into the
system I inherited).
for example:
update s1 set s1.iw_num_act = s2.q_value from siteprofs1
s1,siteprofs1 s2 where s1.industry is null and s2.industry is not null
and s1.unit_num = s2.unit_num
the first updates are not so bad (20 minutes to run), but the second
updates never seem to finish.
I've tried creating different indexes on almost every field and I'm
updating statistics more than once in my batch file. 20 minutes would
be an acceptable amount of time for the entire batch to finish, but it
still seems like a long wait for not that many records.
I'm sure there's a really stupid omission on my part or a rather simple
answer, but for the life of me, I can't find it.
Kevin Mefford