Table-Valued Parameter in Microsoft SQL, what is that and where we can use it.

Recently i worked on a java microservice which was reading records from a csv file and inserting or updating into database through sql procedure. Database was Microsoft SQL.

Initially i wrote a simple java program, which was reading record one by one from CSV and was inserting that record into database. and it was working fine. However there was an issue , Issue was performance of insertion or updation of records in database. with one record at a time it was taking roughly 1 minute for 1000 insertion or updation and if i had 18000 records , it was taking 15–16 minute just to insert. which was not acceptable at all.

So i was searching for option to make it performant. and google was providing one option which was batching. however problem with batching is either it perform insert or update operation in one batch call. There was no way (or AtLeast i was not able to find) to send 1000 records to database for insertion and updation together. so either i can insert 1000 records or 1000 update but not both.

so i thought of trying multi-threading to insert and update. Surprisingly i did not see any improvement at all. Not sure it was my programing issue or something else but this option also did not work out for me.

Then one of my colleague suggested to use TVP(Table Valued Parameter) . TVP was introduced by microsoft in 2008 . TVP solved the performance issue.Now I was able to insert 18000 records in 15–20…