Skip to main content

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:


  1. Joining with User defined table type was taking >90 percent of the time
  2. 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 appear to the Query Optimizer as having only a single row. However, in my case I had thousands of rows.  As a result, joining on them was quite slow due to no optimization performed by query optimizer.

Resolution:

After I have dumped everything from the user defined table type parameters to a temporary table, performance improved drastically as Query Optimizer was able to keep up with the statistics. Also, I was able to compute the expensive hash function only once and saved the result as a column in the temp table. In that way I was able to save some additional compute time there too.

Doing all these, I was able to make my save operation more than twice as faster compared to previous time.

Lesson Learned:
If you have to save huge amount of row data using Table Valued parameters, consider dumping them to a temporary table first and then join with that temp table.

Comments

Popular posts from this blog

Creating dynamic email templates using C# and Office Outlook

It is quite common for many applications to send automated email notifications. Couple of months ago, I have worked on improving our old email template format to make it more user friendly.
In this tutorial I will walk you though regarding how I took advantage of Microsoft Outlook to quickly generate custom email template and later using the html template for building an automated custom email application using C#.

Steps:Creating Templates: Using the rich text editor support  in Outlook create a nicely formatted email. Use placeholder text for the values you like to change dynamically based on your task completion status.

To keep this tutorial simple, I have created a  simple table with placeholder text inside the third bracket [place holder text]. However, you can use anything supported by outlook editor.Getting HTML code: Send the created email to your own address. After that, open the sent email and right click to view source. It will display the HTML source of the email body with …

Trip to Drumheller

Persian Music - Homayoon - Nemitonam English lyrics

I love this song. I have requested one of my Persian friend to translate it for me and she did really nice job.. I am sure you will love it..
"

"The person who was the only person I had,
was the only refuge of my lonely heart,
Left me alone and went from my side
I am restless form the pain of her separation

I thought she stays with me
sings love song for me
I thought she understand my words
I didn’t know she’s unkind

Though gone, but still
I am full of her love
Her thought is always with me
Wherever I go, she is in front of my eyes, in front of my eyes
I want to stand out
find a way to reduce my pain of her separation
But it is not possible, There is no way
I cannot bear
I cannot bear

The person who was the only person I had,
was the only refuge of my lonely heart,
Left me alone and went from my side
I am restless form the pain of her separation

I thought she stays with me
sings love song for me
I thought she understand my words
I didn’t know she’s unkind

Though gone, but still
I …