"Normalizing" imported data
hi Dave,
On 14.12.2009 14:32, pccdavef wrote:
3. Run a series of update queries on the import table to update the foreign
key fields with the keys of the lookup data. Depending on the source data
file, there are between 3 and 7 of these update queries.
I'm discovering that the update queries in step 3 are taking a LONG time to
run (several minutes each), which is going to annoy my users to no end.
My questions a
- are there other, better processes or data structures to use?
- is there a way of optimizing update queries?
I have to assume, that you don't have indexed these fields in the import
table? So you're running up to seven queries against the whole tables.
I simply would not update my import tables. Only insert the new records
INSERT INTO [table] (fieldlist)
SELECT fieldsFromImport, fieldsFromLookup
FROM [importTable]
INNER JOIN [lookupTable] ON condition
Use one INSERT with all necessary JOINs. This should reduce at least the
number of table reads from seven to one.
mfG
-- stefan --
|