Most efficient way to import data into a MS SQL database
- Durum: Closed
- Ödül: $190
- Alınan Girdiler: 10
- Kazanan: sopholos
We are looking for the best way to read data from a database or file (source), compare this data against the data in our database, update changed details and insert new data.
Our system is for ID card production and our card production software is often installed in multiple locations all utilising the data in our MS Sql database to look up and print the details for each person also updating this database when a card is produced.
Our database is MS SQL, the source data may be from a database or from a CSV file depending on the client.
We have created several different import routines and experience the issues below
Usually the source data will be close to 30k records
The source data usually does not contain a last edited date or similar so we do not know which data is new or changed in the source data
The problems we are trying to overcome
The overall time the import/compare process takes too long from start to finish (close to an hour in some situations)
The import process makes the database unreliable when been accessed by other systems during the import - the database is being continually used by the ID production system so the import process cannot limit, restrict (locked records) or slow the database server.
We are not looking for someone to write an application as we will do this ourselves we ARE looking for someone to design the best way to perform this process.
I have attached additional specifications
The winner of this compedition will be the person who designs the best way to perform this task