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 time A 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 a
Memento of the things I have learned