I Am doing Import Process of half a million records. Initially i will move the .csv file data to a sql table,then i will parse the records (Split the records to different tables and then parse) and move it to corresponding table. After completion of import I need to delete the .csv file imported data from tables(Main table=1 and split tables=20).

It takes more time to delete the data. I have check the indexes.

Main Table has totally 61 columns. Split tables have maximum of 5 to 6 columns within we have Link to parent table and foreign key combination.

Similarly there multiple import process running simultaneously. We need to delete the records related to our record Import ID., here TRUNCATE Cannot be used.

  • What do you mean about the "foreign key combination"? You won't be able to delete if there is a foreign key constraint referencing your original table. If not, then is it possible to TRUNCATE the staging table? – BCM Feb 3 at 17:32
  • If you want tips on a performance improvement, you have to show what you currently have. In this case your delete statement(s). And the underlying table structures. – D Kramer Feb 4 at 12:45
  • First - are you using the ON DELETE CASCADE functionality in your foreign keys? If so, I would recommend that you don't do that. The internal DELETE cascade function is much slower than you expect. Personally, I would use #temp tables for all of these tables (or ##TEMP tables if you need them to persist to other processes), then just DROP the tables and re-CREATE as needed. Doing this means you won't need FKs, and deletion is much faster. Just remember to create the needed indexes on the #TEMP tables for your processing. – Laughing Vergil Feb 4 at 18:07

TRUNCATE the tables that you need to have the imported data removed from.


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.