-1


I have been written an c# console app for migrate data;
count of records not so more; each table has almost 100 hundred records. but the structure of data and the businesses logic is so complicated with almost 200 tables.
my data migration has all types of actions: delete, update, insert, get.
delete and update operation just use for data correction in source database
now my migrate data takes so long time; almost three days or more!

some actions for improvement:
1- at first set 'NOCHECK CONSTRAINT' in source database; when this operation do it: delete, update and insert.
2- then for fetch data from source database; set some index.
3- disable all index and constraint in destination database when insert data.

now can any one suggest a solution for improvement duration time?

It should be noted that in this phase of the project I couldn't switch to another solution for example SSIS. I must be improvement this console app!. be used EFCore 2.2 with pure query for transfer data;

thanks a lot

KoKo
  • 421
  • 1
  • 5
  • 21
  • What sql statements are you running? Do you run all 200 in parallel or one by one? – Wouter Jun 14 '20 at 07:29
  • @Wouter : run one by one. steps depend to each others. and can't run parallel or run on multiple instance server – KoKo Jun 14 '20 at 07:39
  • Ok, if you run them one by one, it should be easy to find out which queries take so long. – Wouter Jun 14 '20 at 07:54
  • @Wouter I knew which step is slow. but Inevitable. because of the complicated operation. I search for a solution beyond quires. for example remove constraint as temporary – KoKo Jun 14 '20 at 08:08

1 Answers1

0

I had a very significant performance improvement by switching recovery model from Full to Simple. Obviously, there are good reasons to use Full; but depending on the changes that your migration is doing, the performance improvement may be order of magnitude!

Sorry to bring it up, but it is very difficult to understand what you are trying to say (I suppose it is due to poor English). Maybe run some grammar checker to improve clarity! Is your question about EF Migrations? Or you are using a custom query to read data from one DB and write into another? It seems the latter, then you probably need to look at the SQL extended events to identify poorly written queries before you start tuning the database instance! Nothing improves performance like tuning the SQL!

Felix
  • 9,248
  • 10
  • 57
  • 89