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:
- 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.
- 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.
