Very Computer

I thought of a way of speeding up these mass inserts that done for the app
we have built.
Basically do

Begin tran
alter table table1 nocheck constraint FK_table1_table2
insert into table1 values(...)
:
insert into table1 values(...)
alter table table1 check constraint FK_table1_table2
commit tran

The snag is when "alter table table1 check constraint FK_table1_table2" is
re-applied, all those rows that were inserted are not re-checked.

Stephen Howe

You seem to be aware of the trade-offs between referential integrity
checking and performance.
Is your question how to not have to make the choice between the two?  That
is, how can you have it both ways?

Quote:> Using SQL Server 7.0, SP3

> I thought of a way of speeding up these mass inserts that done for the app
> we have built.
> Basically do

> Begin tran
> alter table table1 nocheck constraint FK_table1_table2
> insert into table1 values(...)
> :
> insert into table1 values(...)
> alter table table1 check constraint FK_table1_table2
> commit tran

> The snag is when "alter table table1 check constraint FK_table1_table2" is
> re-applied, all those rows that were inserted are not re-checked.

> Stephen Howe

--
Mike John

MCSE,MCSD,MCDBA,MCT

KnowledgePool Ltd

Quote:> Using SQL Server 7.0, SP3

> I thought of a way of speeding up these mass inserts that done for the app
> we have built.
> Basically do

> Begin tran
> alter table table1 nocheck constraint FK_table1_table2
> insert into table1 values(...)
> :
> insert into table1 values(...)
> alter table table1 check constraint FK_table1_table2
> commit tran

> The snag is when "alter table table1 check constraint FK_table1_table2" is
> re-applied, all those rows that were inserted are not re-checked.

> Stephen Howe

Quote:> Look for the WITH CHECK clause on the alter to get SQL server to check the
> constraints for existing data - but what will you then do with the
> inevitable rows that fail constraints when trying to reapply the check. I
> would only consider turning off constraints for MASSIVE loads of data that
> has been pre-validated and being loaded via BCP, DTS or BULK INSERT .
> Overall I would leave the constraints in place - they are your friends!

Thanks

Stephen Howe

Quote:> Stephen:

> You seem to be aware of the trade-offs between referential integrity
> checking and performance.
> Is your question how to not have to make the choice between the two?  That
> is, how can you have it both ways?

But in practice since I wrote this message, I realise that the Import apps
written (see other message), do all the checks necessary (they really do)
before changes are made to the database. For example before a row is added
to Table1 a lookup is done in a C++ map container for an entry on Table 2,
if not found, a lookup is done on the database and if the row is really
absent, it is scheluded to be added to table 2 before the corresponding
entry is done on table 1.

 Therefore, I can safely temporarily turn off the FK constraints while
changing the database and reapply them.

thanks

Stephen Howe