I have sql server database insert operation is very slow
-
I have sql server database insert operation is very slow. I am moving data from stage tables--> temporary tables-->Master tables --> Data warehouse tables. Loading the data from stage tables to master tables it is very slow due primary key, foreign key constraints and non clustered index. I am trying to drop the non clustered index before loading the data but there is no use. I am trying to drop the primary key getting issue with foreign key.
-
I have sql server database insert operation is very slow. I am moving data from stage tables--> temporary tables-->Master tables --> Data warehouse tables. Loading the data from stage tables to master tables it is very slow due primary key, foreign key constraints and non clustered index. I am trying to drop the non clustered index before loading the data but there is no use. I am trying to drop the primary key getting issue with foreign key.
Be very sure you are only doing and insert, not an update. We created a stored proc that removed all indexes except the PK on the destination tables. applied the insert and then rebuilt the indexes. CAVEAT your data MUST be clean as you lose all referential integrity checking.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Be very sure you are only doing and insert, not an update. We created a stored proc that removed all indexes except the PK on the destination tables. applied the insert and then rebuilt the indexes. CAVEAT your data MUST be clean as you lose all referential integrity checking.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
If i drop non clustered index will i any benefit on insertion? If i found matched record i am updating otherwise insert.
-
If i drop non clustered index will i any benefit on insertion? If i found matched record i am updating otherwise insert.
kali siddhu wrote:
If i found matched record i am updating otherwise insert
Right there is your problem, for every record you are checking for an existing record and when you do the update the system has to check referential integrity. Split your data into 2 set, those to be inserted and those to be updated. OR delete the records to be updated and insert all records. This may not be viable as it will break existing RI.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP