SQL insert long time!!!!!

[Sridharan]

hi,

when i try to load data from one staging table to my production table which contains two foreign key constraints with one of the foreign key references being a composite foreign key to another master table it takes a long time very very long time.... the average time taken to insert some 37000 rows is nearly 32 minutes.... any way by which this can be speeded up.... i need to do this for quite a no. of tables and this is really time consuming....

thanks for your suggestions


regards

sridharan

 

[BillDHS]

You didn't indicate what tool you're using. If its BTEQ or a ODBC tool, look at Multiload or TPump.

 

[Sridharan]

thanks Bill.... well i cannot use Mload coz the tables contain foreign key relations and i used tpump.... i didn't help my cause... i tried to remove the composite foreign key relationship it is very fast.... how do i make it work with that relationship active...


thanks

sridharan

 

[RanD]

what is the ddl of the tables involved?
What is the SQL you ar using to do the insert?
have you collected stats?

give us more info and we can be more helpful

;-)

 

[tdsmart]

I don't think there is any way to speed up the insert process when there are foreign keys since the database has to validate every child row before doing the actual insertion. The other way would be to populate the tables first and then create the foreign keys. This may be a bit faster.