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 seconds. so from 15 minutes to 15–20 second was a significant improvement.

What is TVP

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Table-valued parameters are strongly typed and their structure is automatically validated. The size of table-valued parameters is limited only by server memory.

Beginning with Microsoft JDBC Driver 6.0 for SQL Server, table-valued parameters are supported with a parameterized statement or a parameterized stored procedure. Table-valued parameters can be populated from a SQLServerDataTable, from a ResultSet or from a user provided implementation of the ISQLServerDataRecord interface.

The following two code fragments demonstrate how to configure a table-valued parameter with a SQLServerPreparedStatement and with a SQLServerCallableStatement to insert data. Here sourceTVPObject can be a SQLServerDataTable, or a ResultSet or an ISQLServerDataRecord object. The examples assume connection is an active Connection object.

As a parameter to sql query

// Using table-valued parameter with a SQLServerPreparedStatement.  
SQLServerPreparedStatement pStmt =
(SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO dbo.Categories SELECT * FROM ?");
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);
pStmt.execute();

If we are using procedure to to handle TVP

// Using table-valued parameter with a SQLServerCallableStatement.  
SQLServerCallableStatement pStmt =
(SQLServerCallableStatement) connection.prepareCall("exec usp_InsertCategories ?");
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);;
pStmt.execute();

Here sourceTVObject is instance of source data table. In my case i handle the update and insertion in procedure with list of records passed from java program.

/* Assumes connection is an active Connection object. */// Create an in-memory data table.  
SQLServerDataTable sourceDataTable = new SQLServerDataTable();

// Define metadata for the data table.
sourceDataTable.addColumnMetadata("CategoryID",java.sql.Types.INTEGER);
sourceDataTable.addColumnMetadata("CategoryName",java.sql.Types.NVARCHAR);

// Populate the data table.
sourceDataTable.addRow(1, "CategoryNameValue1");
sourceDataTable.addRow(2, "CategoryNameValue2");

// Pass the data table as a table-valued parameter using a prepared statement.
SQLServerPreparedStatement pStmt =
(SQLServerPreparedStatement) connection.prepareStatement(
"INSERT INTO dbo.Categories SELECT * FROM ?;");
pStmt.setStructured(1, "dbo.CategoryTableType", sourceDataTable);
pStmt.execute();

We can further read from microsoft document. i was willing to provide a approach to handle large amount of data if we are using Microsoft SQL.

Angular,Vuejs,Android,Java,Git developer. i am nerd who want to learn new technologies, goes in depth.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store