Skip to main content

Posts

Showing posts from October, 2016

SQL Performance improvement for User defined table types

Recently, I have dealt with an interesting performance issue with one of my SQL query and thought I will share the experience here.

Context:
We had a legacy stored procedure responsible for saving large amount of excel row data to our database tables. It was using  User Defined Table Types as one of the parameter to get a list of row data from excel. However, the stored procedure was taking very long time to save the large data set.

Root Cause:
After quite a bit of investigation using execution plan in SSMS, I was able to narrow down the performance issue to the following:


Joining with User defined table type was taking >90 percent of the timeA custom hash function which has been used multiple times as a join criteria was also quite expensive to compute.

After doing additional research using stack overflow, I was able to figure out that the primary reason for the poor performance doing a  JOIN on Table Valued parameters is that : it does not keep statistics and appear to the Query Op…