Adriel Blog

Better DB Programmer: Table parameters

Recently we were investigating another performance optimization for our database application. The result was the best: we found a better business solution that made complicated changes obsolete. Nevertheless, it forced us looking into various interesting options.

We had an extremely fast in-memory cache with some in-progress session stored. On the other hand we had accumulated stat data related to the cached  in the remote classical database. To provide reporting capabilities we had to join basically 2 data sources: the in-memory cache and remote data. After quickly brainstorming our options we realized that it would beneficial to push the burden of the query processing to the remote database after quickly processing in-memory cache. However, how would you push the in-memory data into the remote storage?

One option we considered was writing a user defined function or simply UDF for our SQL Server. As we soon realized passing table data into a function was not an option. Thanks to the following article for explaining the differences and limitations on the table structures in the SQL Server: http://www.odetocode.com/articles/365.aspx.

SQL Server 2008 is supposed to address this limitation with Table-Valued Parameters (TVP). See the following article for a tutorial: http://www.mssqltips.com/tip.asp?tip=1483.

We suggested dumping the table into CSV file and push it the Oracle/SQL Server. We rejected it since it was not efficient and would complicate our deployment. Using a single protocol would be a better solution.

At the end we saw that in most use cases we didn’t have to go through all records but rather break as condition was first satisfied. This would happen in the 1st or 2nd batch request in 90% of use cases. We switched to a simple algorithm. It would be interesting  to know if our business reqirement would change and we would need to implement a complete solution.