Efficiently filter data in a table-valued parameter in a stored procedure

Question

I've got a table-valued type as a parameter to a stored procedure, with anything up to thousands of rows in it. I perform several operations on this stored procedure (currently a MERGE and an INSERT), but before those I want to filter the contents of the parameter on a predicate:

@data TableValuedType READONLY

MERGE INTO Table2
USING (
    SELECT ... FROM @data
    UNION
    SELECT ... FROM @data
    UNION
    ...)
ON ...
WHEN NOT MATCHED THEN ....

INSERT INTO Table3
    SELECT ... FROM @data

Is the most efficient way to do this to declare another variable of the table type, insert into that, then use that variable to merge and insert from:

DECLARE @sanitisedData TableValuedType
INSERT INTO @sanitisedData
    SELECT ... FROM @data
    WHERE <predicate>

or to use the predicate every time I select something from @data:

MERGE INTO Table2
USING (
    SELECT ... FROM @data WHERE <predicate>
    UNION
    SELECT ... FROM @data WHERE <predicate>
    UNION
    ...)
ON ...
WHEN NOT MATCHED THEN ....

INSERT INTO Table3
    SELECT ... FROM @data WHERE <predicate>

or something else?

Can you not pass in a pre-filtered set of data to begin with? — Oded
No, the condition to do the filtering only exists on the server — thecoop
Not directly, but some of the select clauses are turned into joins by the optimizer — thecoop
Depends on the case I think. Either the balance tilts towards a complex filter that takes forever to process and will be executed lots of times. In that case, pre-store the results in a new table and re-use that. Or, in case the filter is trivial (e.g. on the PK of the @table) and the amount of data to copy would be large, then go for the other option. — deroby

elvis elvis · Accepted Answer · 2012-03-28T22:10:00

Do avoid using a @Table variable whenever possible. It, in most cases, will cause a performance decrease. Opt for a temp table instead.

Both table variables and temp tables are written to disk anyway, and temp tables have the added advantage over table variables because it contains statistics which the query optimizer can use. You can also create an index (both clustered and nonclustered indexes) on a temp table, whereas a table variable you cannot.

So I'm suggesting you use a temp table and create an index on the columns you are filtering by.

Hope this helps.

Efficiently filter data in a table-valued parameter in a stored procedure

1 Answers