469,602 Members | 1,845 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

Forcing auto-update statistics to fire in SQL Server 2000

Our users have potentially dirty legacy data that they need to get
into our application. We provide a set of staging tables, which map
to our source tables, that the users do their ETL into. Every row in
the source tables has a generated integer id. Every row in both the
source and staging tables has a unique publicid (varchar(22)). All
foreign key references in the staging tables are through publicids.
(The foreign key reference could be to a row in a source or staging
table.)

After successfully validating the data in the staging tables, we then
walk the tables in a safe ordering (no forward references on foreign
keys) and do insert/selects from the staging tables into the source
tables. We generate correlated subqueries in the select list to do
the publicid->id mappings for the foreign keys. For example:

insert into t1(id, fooid) select id, (select id from foo where
t1.publicid = foo.publicid) from t1;

All insert/selects are done in a single transaction as we need to
perform some additional validations afterwards before deciding that it
is okay to commit the changes.

The problem is that the optimizer is choosing bad query plans for the
insert/selects where there are foreign key references to be
translated. In our test case, the primary key table is growing from 0
to 750,000 rows, but the optimizer is choosing a table scan. (NOTE:
Optimizer chooses same query plan when we rewrite the query to be a
left outer join instead of using a correlated subquery in the select
list.)

The change counts that cause auto-update statistics to be fired do not
get modified during an insert/select. I tried do a searched update,
followed by a select, after the insert/select in an attempt to cause
auto-update statistics to get triggered, but it didn't work:

UPDATE foo SET publicid = publicid;
SELECT MIN('asdf' + publicid) FROM foo;

Microsoft support suggested calling DBCC UPDATEUSAGE(). This worked
on one machine, but did not work on another machine with the exact
same HW, patch level and configuration.

(NOTE: We can't call UPDATE STATISTICS because that can't be done
within a transaction.)

Any clues how to force auto-update statistics to be fired?
Jul 20 '05 #1
0 3549

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by John | last post: by
2 posts views Thread by petev | last post: by
15 posts views Thread by Andrew Fedoniouk | last post: by
2 posts views Thread by janet | last post: by
reply views Thread by Ray Mitchell | last post: by
4 posts views Thread by ATS16805 | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.