SQL Server - Is there any way to speed up inserts into a table variable in a TVF?

Question

I have a query that runs in around 6 seconds. At present, it outputs about 1500 records that are then inserted into a table variable. The insert into the table takes an overwhelming 7 seconds.

If I try the same thing with a temporary table, the insert only takes about a second.

The problem is that the query is placed inside a TVF (and there are a lot of dependencies on that function so converting it to a stored procedure isn't the most ideal resolution) so I can't use temporary tables.

Is there any way to speed up the inserts into the table variable? I'm using SQL Server 2012 so memory-optimized table variables aren't an option either.

Would appreciate any advice, thanks in advance!

EDIT (Additional information):

I tried a CTE approach similar to the one shown here and the overhead is similar to using the temporary table approach, the query takes around 8 seconds now. There are three of these table variables (each giving a slightly different output) so combined, the query takes around 24 seconds total, which is quite acceptable for what we use it for.

inserting data into a big table takes much more time than inserting the same data in an empty table. How many record you have in your target table? BTW, What is TVF? — FLICKER, Feb 19 '16 at 23:45
Is your query a set of 1500 separate `INSERT` statements, or a single `INSERT` with multiple `VALUES` sub-statements, or something else? Have you considered `BULK INSERT`? — Dai, Feb 20 '16 at 00:14
The query fetches around 1500 records which are inserted using syntax like: INSERT INTO @Table(Var1, Var2) SELECT Var1, Var2 from ( ... ) query — RizJa, Feb 20 '16 at 00:36
How did you measure time of select/insert? Is your TVF finally used in joins? Did you review actual execution plans? — Ivan Starostin, Feb 20 '16 at 06:27
@IvanStarostin The TVF calls three individual queries and saves each resultset into its own table variable. The queries are quite similar and take a similar amount of time in running (I've checked the execution time of each individually). After the three table variables are loaded, they are joined together and the resulting value is outputted by the TVF. I've individually tested having populated resultsets in table variables and joining them and it took a fraction of a second to complete. — RizJa, Feb 20 '16 at 06:47
Am I correct that you did not review actual execution plans, did not measure separately time of `select` and `select` with `insert`? ...And you actually have 4 (four) table variables in your TVF: for 1st insert-select, for 2nd insert-select, for 3rd insert-select and for final joined resultset? — Ivan Starostin, Feb 20 '16 at 06:59

score 1 · Answer 1 · edited Feb 20 '16 at 08:57

1

You can stop index creation before bulk insert and enable them back after the insert

-- Disable Index

ALTER INDEX [IXYourIndex] ON YourTable DISABLE
GO

-- Insert Data

-- Enable Index

ALTER INDEX [IXYourIndex] ON YourTable REBUILD
GO

edited Feb 20 '16 at 08:57

Kamil Gosciminski

16,547
8
49
72

answered Feb 19 '16 at 23:58

Shachaf.Gortler

5,655
14
43
71

1

How this answer is related to the question? – FLICKER Feb 20 '16 at 00:08
1

@FLICKER Because every `INSERT` operation updates indexes, which can be expensive. By suspending index operations while inserting you speed-up inserts. The index-rebuild operation can then be performed asynchronously after the inserts are completed. – Dai Feb 20 '16 at 00:13
Looks like he is having problem on inserting data into table variable. Table variable cannot have index. Am I missing something? – FLICKER Feb 20 '16 at 00:15
@Flicker , Table variable can have index as part of primary key – Shachaf.Gortler Feb 20 '16 at 00:18
My bad. Thanks for info :) – FLICKER Feb 20 '16 at 00:20
@Shachaf.Gortler Do indexes apply even on table variables? I'm not creating any indexes prior to the insert. – RizJa Feb 20 '16 at 00:38
Indexes on table variables can only be created declaratively as part of the table variable definition and can't be disabled or rebuilt. – Martin Smith Feb 20 '16 at 09:00

score 0 · Accepted Answer · answered Feb 20 '16 at 02:52

Given that the ultimate goal of storing results table variables was to perform isolated queries and join them in providing the output, the following approach using CTEs worked with the query overhead time the same (or maybe even faster) as the temporary table:

;WITH CTE1 as
(
    --Query 1 (took 6 seconds)
),
CTE2 as
(
    --Query 2 (took 6 seconds)
),
CTE3 as
(
    --Query 3 (took 8 seconds)
)
SELECT cte1.fielda, cte2.fieldb, cte3.fieldc, ...
FROM cte1
JOIN cte2 on cte1.fieldx = cte2.fieldx
JOIN cte3 on cte1.fieldx = cte3.fieldx

-- Total duration 20 seconds
-- Took 39 seconds previously

SQL Server - Is there any way to speed up inserts into a table variable in a TVF?

2 Answers2