Instance of passing DatabaseTableType types to a Stored Procedure instead of simple parameters in C#

Question

Hi All

Can anyone comment on which method they feel should be used here and why?

Please bear in mind that we are a very small team so do not tend to implement best practice all the time and more often that not have to be pragmatic.

I have come across a situation where DatabaseTableTypes are being used as parameters for passing a single row of data for each of 2 tables to a Stored Procedure, and am struggling to justify why we should keep doing this over just passing simple parameters. It is worth noting that this infrequently called, and will only called from one C# component. Example code has been used to compare the 2 methods below.

I have listed some reasons for questioning this approach (for being a bit of a using a sledgehammer to crack a nut approach), or more importantly whether wanting to duplicate it further:

1. Requires 2/3 additional components to be created on both the DB side and the C# side to make it work (about 5 times the amount of C# code).
2. Only a single row of data passed in each table so I would not expect the overhead to still be more efficient, plus this is an infrequent DB call anyway.
3. Cannot be debugged in the live environment because we cannot easily debug the DB side and if it errors it rolls back our DB logging, whereas C# can always be debugged and write logs.
4. There is no Git or or other versioning of changes that records every change made in the DB, but there is for our C#.

Many thanks in advance

---

Using the pass simple Parameters in C# code method...

C#

cmd.CommandType = CommandType.StoredProcedure;

//add the params
SqlParameter param;
cmd.Parameters.Add("@payment_pp_id", SqlDbType.Int).Value = paymentTableId; [* 23 of these lines for each param (not passing duplicates used in each table)]

//now create the entry;
intId = Convert.ToInt32(cmd.ExecuteScalar());

SQL Management Studio

(none other than stored procedures)

---

Using the build all Tables and Parameters in C# code method...

C#

CommandType = CommandType.StoredProcedure

//create first parameter
DatabaseTableType paymentInfoTableType = new DatabaseTableType();
//create the column structure
dbTableType.AddColumn(new DatabaseTableTypeColumn(1, "payment_type_id", typeof(int))); [* 11 of these lines for each param]
//create the data
DatabaseTableTypeRowData row = new DatabaseTableTypeRowData(1); //fixed at 1 row
row.AddData("payment_type_id", new DatabaseTableTypeDataItem("payment_type_id", typeof(int), paymentType)); [* 11 lines for each param]
//add the row
dbTableType.AddRowData(row);
//add the DatabaseTableType parameter
cmd.Parameters.Add("@PAYMENT_INFO", SqlDbType.Structured).Value = paymentInfoTableType.GetDataTable();

//create second parameter
DatabaseTableType creditCardInfoTableType = new DatabaseTableType();
//create the column structure
dbTableType.AddColumn(new DatabaseTableTypeColumn(1, "order_id", typeof(int)));
//create the data
DatabaseTableTypeRowData row = new DatabaseTableTypeRowData(1); //fixed at 1 row
row.AddData("order_id", new DatabaseTableTypeDataItem("order_id", typeof(int), requestInfo.OrderId)); [* 19 lines for each param]
//add the row
dbTableType.AddRowData(row);
//add the DatabaseTableType parameter
cmd.Parameters.Add("@CREDIT_CARD_TRANSACTION_INFO", SqlDbType.Structured).Value = creditCardInfoTableType.GetDataTable();

SQL Management Studio

Database -> Programmability -> Types -> User-Defined Table Types [* 2 tables have been fully defined in here to be identical to the 2 tables generated in the above C# code]

(nothing else other than stored procedures)

---

Answer 1

Yeah, if it is only a single row, I would prefer scalar parameters. At least if I'm dead sure that it is never going to change...

Answer 2

The first results in Google (searched for: efficency Table-Valued Parameters for a "single row") come up with the following:

"That said, I would not recommend TVPs for single-row operations due to the start-up overhead but it’s a good choice when the most common use case is multiple rows."
https://www.dbdelta.com/maximizing-performance-with-table-valued-parameters/

This article does comparison between 1, 10 & 100 rows. It quotes cpu, reads, writes, duration.
https://www.mssqltips.com/sqlservertip/3473/compare-sql-server-stored-procedure-performance-for-table-valued-parameters-vs-multiple-variables/

Single Row Scenario
Test Scenario CPU (ms) Reads Writes Duration (ms)
Regular Parameters 0 29 0 1
Table-valued Parameter 0 254 2 4

So in the first instance, using this along, this strongly suggests that this method should not be used for a single row (and perhaps should in general be a few at least).

Answer 3

Hi @CrystalDevelopment ,

prefer to

passing a single row of data for each of 2 tables to a Stored Procedure

Using the build all Tables and Parameters in C# code method...

Table-Valued Parameters

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.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.