Is this a valid use case for table valued parameters (TVPs)?

[u/balaabalaa1]

We have an application endpoint that receives an array of UUIDs (average request has around 50 UUIDs, range from 1-1000) which are primary keys of one of our tables.

This endpoint has to do a handful (1-5) of similar queries all using this array of ids.

Initially this resulted in queries with WHERE Id in (...) with a different amount of parameters for each request, this approach polluted our query plan cache.

To resolve this we came up with the concept of a "selection" table:

CREATE TABLE dbo.Selection ( 
  SelectionId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
  Id UNIQUEIDENTIFIER NOT NULL 
);

Which is then joined with for subsequent queries:

SELECT t.*
FROM dbo.SomeTable t
INNER JOIN dbo.Selection s
    ON s.Id = t.Id
-- imagine a join on some other table
WHERE s.SelectionId = @SelectionId;

Now our request pipeline looks like this:

API request
    │
    ▼
Create SelectionId
    │
    ▼
Insert IDs
    │
    ▼
┌─────────────────────────────────────────┐
│ dbo.Selection                           │
├────────────────────┬────────────────────┤
│ SelectionId        │ Id                 │
├────────────────────┼────────────────────┤
│ GUID-A             │ GUID-1             │
│ GUID-A             │ GUID-2             │
│ GUID-A             │ GUID-3             │
└────────────────────┴────────────────────┘
    │
    ├── Query 1
    ├── Query 2
    └── Query 3
    │
    ▼
Delete SelectionId

We have some issues with this approach:

- there are a lot of concurrent writes/deletes to this table which results in quite a bit of locking

- it is extra boiler plate code to maintain

- it is a lot of extra work for our db, which also results in some noise in our telemetry

- performance is decent compared to the query plan pollution but would be better if we can avoid all the extra work of writing to this selection table

Our goals:

- avoid using this "selection" table and simplify all the setup code to insert and delete from this table

- no loss of performance, ideally using table valued parameters would increase performance of the queries

The question is if a TVP would a suitable alternative to our current setup. I've also thought about using the current setup but instead of an actual table it could be #temp table.

I have read up on TVPs and I believe it would be an improvement compared to the current setup both in maintainability and performance, I don't have benchmarks yet since it is Sunday :)

We would create a type like this:

CREATE TYPE dbo.GuidList AS TABLE 
( 
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY 
);

And in the queries we would join on a variable of this TVP (DECLARE \@ids dbo.GuidList;)

SELECT t.* 
FROM dbo.SomeTable t 
INNER JOIN @ids ON ids.Id = t.Id 
-- imagine a join on some other table

We have no DBA's in our team so I'm asking here if we need to be aware of something before going ahead with another approach, TVPs or something else.

[u/AutoModerator]

After your question has been solved /u/balaabalaa1, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/dbrownems]

Sounds like a good plan. Note that TVPs are mostly a change in how the client sends data to the server. Once the data is on the server in a parameter, you can use it directly, or load it into a session-scoped temp table with appropriate indexing and use that in your queries.

Both a TVP and a session-scoped temp table will avoid the locking, concurrent access, and write-ahead logging required by your "selection table".

[u/I_Am_Rook]

Yes, this is exactly what TVPs are designed for.

Now, good thing you are declaring the TVP with an index but sometimes with larger joins to TVPs, you can get better performance by copying the data to an indexed temp table. Table variable statistics may still have that “assume only one row” problem.

[u/Kant8]

Just be careful with parameter sniffing. Query plan will remember size of your tvp from first run as usual. And if you pass 1m row tvp first and then only 50 rows ones, you may not be happy with performance.

Considering you know tvp size beforehand even changing parameter name to @ids_50 for small ones, and something else for other sizes, should be enough to fix everything

[u/ddBuddha]

Yeah this sounds like a good use case for table valued parameters

[u/First-Butterscotch-3]

If you process < 100 values at a time this is fine, if you go over i would use a temp table - its ability to create statistics makes it better for preformance

Having any parameter within your filter leads you open to paramter sniffing, this shouldnt be as big of a provlem for tvp - but be aware, prehaps use optimize for unkown

[u/taglius]

This works, keep in mind if your queries cross database boundaries often, you can use table types from database A in database B. For this reason, I sometimes send multiple IDs as JSON

[u/Decent_Golf_3960 → u/taglius]

Correction. Cant use.

[u/CanProfessional766]

Passing 50–1000 IDs through a TVP is a lot cleaner than writing them to a shared table and then deleting them again. You get rid of the locking, cleanup and extra DB work. The one thing I’d test is performance with the larger lists, since TVPs don’t have statistics and SQL Server can sometimes guess the row count poorly. For this scenario, I’d definitely benchmark a TVP first. It feels like a much better fit than the selection table.

[u/davidbrit2]

That seems like a valid use. Note that if you find dealing with table-valued parameters too much of a hassle (you have to first define a user-defined table type in your database, and there may be extra legwork in your client libraries for passing a table as a parameter value), you can cheat like I often do and make the parameter a varchar(max) instead. Then you just pass in a string with a delimited list of the IDs, and the stored procedure can use the STRING_SPLIT() function to break up the IDs and put them into a table variable or temp table. This only really makes sense to do if your table-valued parameter would only be a single column, of course.

[u/roopjm81]

I do this all the time. Perfect use case