On Thu, 2006-09-28 at 12:44 -0400, Carlo Stonebanks wrote:
> At this early stage in the project, we are initializing our portal's
> database with millions of rows of imported data in over 50 different
> flattened tables; each table's structure is unique to the data provider.
> This requires a pretty complex import program, because the data must be
> matched semantically, not literally. Even with all of the expression
> matching and fuzzy logic in the code,our performance statistics show that
> the program spends over 75% of its time in SQL queries looking for matching
> and/or duplicate data.
My experience with that type of load process is that doing this
row-by-row is a very expensive approach and your results bear that out.
It is often better to write each step as an SQL statement that operates
on a set of rows at one time. The lookup operations become merge joins
rather than individual SQL Selects via an index, so increase the
efficiency of the lookup process by using bulk optimisations and
completely avoiding any program/server call traffic. Data can move from
step to step by using Insert Selects into temporary tables, as Jim has
already suggested.
The SQL set approach is different to the idea of simply moving the code
server-side by dropping it in a function. That helps with the net
traffic but has other issues also. You don't need to use esoteric
in-memory thingies if you use the more efficient join types already
available when you do set based operations (i.e. join all rows at once
in one big SQL statement).
You can also improve performance by ordering your checks so that the
ones most likely to fail happen first.
Trying to achieve a high level of data quality in one large project is
not often possible. Focus on the most critical areas of checking and get
that working first with acceptable performance, then layer on additional
checks while tuning. The complexity of the load programs you have also
means they are susceptible to introducing data quality problems rather
than removing them, so an incremental approach will also aid debugging
of the load suite. Dynamic SQL programs are particularly susceptible to
this kind of bug because you can't eyeball the code.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
