Lyniate Team

SQL server: Performance considerations with table-valued parameters

August 18, 2015

Relational databases have long been designed to operate efficiently on sets of data rather than individual data items. The database’s optimizations for operating on sets of data were generally one-way, in that the database excelled at returning sets of data to the application but generally provided little support for the application to send sets of data to the database.

Developers who needed to send sets of data to the database had to resort to such hacks as encoding lists of numbers in comma-delimited strings, or sets of records in XML strings that were parsed out in stored procedures.

SQL Server 2008 took a big step towards full bi-directional support for dealing with sets of data with the combination of user-defined table types (UDTTs) and table-valued parameters (TVPs). Put simply, UDTTs allowed developers to define data types that resembled tables with named and strongly-typed columns into which any number of rows could be inserted.

TVPs allowed developers to take those previously-defined UDTTs and use them as parameters or variables in stored procedures. Now, a developer could send sets of data from the application to the database in a type-safe, fully-supported fashion. No more difficult-to-maintain and poorly-performing hacks were required.

One of the most frequent database operations in Corepoint Integration Engine is the enqueuing of a message that has been received for subsequent processing. That message is always accompanied by somewhere between 0 and 2 descriptive entries about the message, depending on the channel through which the message was received. This operation seemed like the perfect opportunity to use a TVP – the message to be enqueued could be passed to the stored procedure as a single parameter. The descriptive entries (whether 0, 1, or 2 in number) could be inserted into a TVP that was also passed to the stored procedure.

This has two primary advantages:

  1. We would be required to write and maintain only a single piece of code that would work for all three cases of 0, 1, or 2 descriptive entries per message.
  2. If we added new channels to the engine in the future that had more than 2 descriptive entries per message, the existing code should “just work” since those additional descriptive entries could simply be added as additional rows to the TVP for the stored procedure.

We coded it up and it worked as expected with one wrinkle: buried deeply in the fine print on the documentation for SQL Server TVPs is the notice that they are materialized in tempdb. In other words, they are treated as miniature tables and written to disk.

Non-TVP parameters to stored procedures are not written disk and therefore do not incur the performance penalty associated with disk writes. Because this operation was among our most frequent database operations, we wanted to measure the performance cost of using TVPs in a high-volume stored procedure.  We tried splitting the one stored procedure that took the descriptive entries in a TVP into three separate stored procedures, each of which took either 0, 1, or 2 descriptive entries as regular parameters.

We found that our write load on tempdb declined by 70% and our engine’s overall processing time declined by 5%. We gave up the two advantages of the TVP implementation of that operation, but we were happy to do it given the performance increase.

We continue to use TVPs for other operations performed less frequently and where we send many more than 0-2 records to the database. We avoid using TVPs for high-volume operations where the input data can be relatively easily passed through regular stored procedure parameters.

Related Blogs

Lyniate Team

Medrics scales patient engagement app globally with Lyniate Rhapsody

Medrics uses Lyniate Rhapsody to integrate healthcare data from disparate sources for improved patient experiences and outcomes.

Read more

Melanie Medina

Caresyntax CTO shares how he relies on fully managed integration services to help provider customers improve surgical outcomes

Caresytnax relies on fully managed services from Lyniate to maintain integrations between its platform and its provider customers.

Read more
group of people sitting in chairs with text Healthy Data. Healthy People.

Lauren Usrey

Driving healthier outcomes with healthier data

The Healthy Data. Healthy People. panel discussion at Lyniate CONNECT 2022 brought together healthcare leaders to discuss the importance of data quality.

Read more