0

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?

thecoop
  • 45,220
  • 19
  • 132
  • 189
  • Can you not pass in a pre-filtered set of data to begin with? – Oded Mar 20 '12 at 15:57
  • No, the condition to do the filtering only exists on the server – thecoop Mar 20 '12 at 16:00
  • Are you doing joins using the passed in TVP? – Oded Mar 20 '12 at 16:01
  • Not directly, but some of the select clauses are turned into joins by the optimizer – thecoop Mar 20 '12 at 16:06
  • If the filtering reduces the working set significant, I would guess that would be the most efficient way to go. You should test for your specific scenario. – Oded Mar 20 '12 at 16:11
  • It operates as a blacklist - most of the time, the data wouldn't be affected – thecoop Mar 20 '12 at 16:14
  • 1
    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 Mar 26 '12 at 12:30

1 Answers1

0

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.

elvis
  • 312
  • 2
  • 12
  • The OP has the comment "No, the condition to do the filtering only exists on the server" this indicates that the proc is being called by the client not another SP. So a temp table probably isn't realistic in this use case. A staging table that gets populated by BCP is however a realistic alternative – Conrad Frix Mar 28 '12 at 22:22
  • I was assuming he had the capability to modify the SP, and if this is incorrect then I do apologize. Isn't it possible to create the temp table within the SP before the merge/insert? I also was going to suggest the staging table option as well but glad you brought it up. Thanks. – elvis Mar 29 '12 at 19:41
  • hmm actually I may have misunderstood your answer. Are you recommending replacing `@Data` or `@sanitisedData` with a temp table? – Conrad Frix Mar 29 '12 at 19:48
  • Oh ok, then no, changing `@Data` to a temp table won't work here. The OP is doing something like `MySqlCommand.Parameters[@Data].Value = MyDataTable; MySqlCommand.ExecuteNonQuery();` to pass "thousands of rows" to SQL server. If you want to use a temp table you'd need to figure out a way to populate it. That's why I suggested a staging table and BCP. – Conrad Frix Mar 30 '12 at 14:48
  • CREATE PROCEDURE myProcedure (@myTableParam myTableType READONLY) AS SELECT * INTO #TempTable FROM @myTableParam;............. Then use the temp table from there on. – elvis Mar 30 '12 at 15:27