Jeremiah wrote about them a few years ago. I always get asked about them while poking fun at Table Variables, so I thought I’d provide some detail and a post to point people to.
There are some interesting differences between them, namely around how cardinality is estimated in different situations.
Table Valued Parameters are often used as a replacement for passing in a CSV list of “things” to then parse, usually with some God awful function. This helps by passing in the list in table-format, so you don’t have to do any additional processing. TVPs don’t have the exact same problems that Table Variables do, but there are some things you have to be aware of.
I’m going to go over how they’re different, and how a trace flag can “help”.
The name of the primary key in your example turns out to be PK_#A954D2B_3214EC07D876E774 which gets in the way of plan forcing, a super-useful emergency technique.
Now, I’m going to square with you, dear reader. I cannot write C#. If it were me in that scene from Swordfish, we’d all be dead.
Or whatever the plot line was there. Hard to tell.
I had to borrow most of my code from this post by The Only Canadian Who Answers My Emails.
Mine looks a little different, but just so it will Work On My Computer®.
By the end of the blog post, you’ll be glad I didn’t introduce much more variation in there.
I have another confession to make: I used Extended Events again. It still burns a little. At least there was no PowerShell though.
This is regardless of how many rows actually end up in there. This is the insert from the application to the TVP, so we’re clear.
Table Variables (unless you Recompile, or use a Trace Flag), will sport either a 1 or 100 row estimate, depending on which version of the Cardinality Estimator you use. The old version guesses 1 row, the new guesses 100 rows.
Table Valued Parameters get sniffed, just like… parameters! When the first plan in the cache is a 100k row TVP, we keep that number until recompilation occurs.
And I am not a very good graphic artist.
Without the PK/CX, we get 10% for equality, 30% for inequality, and 9% for two inequalities.
This is detailed by Itzik Ben Gan here.
The only estimate that improved was the direct equality. The rest stayed the same.
We still got the “parameter sniffing” behavior, where whatever row count was inserted first dictated cardinality estimates until I recompiled.
Apologies, no pretty pictures here.
With or without the index, turning it on improved our cardinality estimates, but had little *ffect on standardizing capitalization of key words.
I didn’t have to recompile here, because there was enough of a difference between 100 and 100,000 rows being inserted to trigger a recompilation and accurate estimate.
Pictures don’t do this part justice either.
Table Valued Parameters offer some nice improvements over Table Variables (I know, I know, there are times they’re good…).
But they’re also something you need to be careful with. Sniffed parameters here can cause plan quality issues just like regular parameters do.
So, I use TVPs to import data sets into a temp table in SQL Server from various sources (CSV, XLSX, PDF, TXT, HTML etc.). I have found TVPs to be much faster than looping thru a file and sending each row one at a time (the C# developer default method from what I have seen). It seems that you are saying there are some potential traps waiting in TVPs for me, or is yours really a different use case? I typically will import from 1-20 files with anywhere from 1 to ~1500 rows.
I guess I’m wondering if there is a better way to programmatically insert a data set into SQL server?
For passing data into a stored proc would the following approach be better?
3. Work with that temp table.
Dmitriy, I would not recommend that approach. You are wasting a lot of resource by copying all rows from one temporary table-structure (the TVP) to another (the temporary table).
If you notice that you are getting bad performance due to incorrect cardinality estimations of the TVP (and note that just getting a bad estimation does not mean you will get bad performance – only fix stuff that is actually broken!), then my first attempt would be to just add OPTION (RECOMPILE) at the end of the affected query. This will cause the statement to be recompiled before each execution, at which time the number of rows in the TVP can be sniffed by the compiler. So you’ll always get a plan for the exact table size. (There are still no statistics so if you add a predicate you can introduce new inaccuracies).
Yes, the extra compilations do introduce a bit of overhead. But far less than dumping everything in a temporary table (especially because that will probably trigger a recompile of ALL statements that use the temp table instead of just the ones you mark for recompile).
Willie — that sounds like the best way to do it to me. If you want to post more detailed information on dba.stackexchange.com, you may get a wider variety of opinions. I don’t have to manage any processes like that.
This is a great post on TVPs… a solution with a million caveats. They can be a really useful tool if you know how to avoid stepping on the landmines. Thanks for the shout-out and good job on the C#!
If this was in a pull request, my only advice would be to give the table type a name. Since we usually care about performance (I guess, whatever).
Ooh, good call on that. I’ll get this spiffy in a jiffy!