CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Nico
Nico

Reputation: 1197

Alternative to table variable in TVF

I've got a TVF and make use of table variables inside of it. One of the last steps is to delete several (thousands) lines of it. Exactly this query increases the runtime dramatically. Changing the code using temp tables reduces runtime again, but unfortunately temp tables must not be used in TVF. I can't change the TVF to a SPROC.

Any ideas how to enhance performance?

I added only the part of the TVF which slows it all down. Prior to this, timestamps are collected and preprocessed in @Result. @C collects includes the ids which have to be modified at this time.

UPDATE R
SET starttime = CASE R."myFunction"
                    WHEN 1 THEN Date1
                    WHEN 0 THEN Date2
                END
FROM @Result AS R
WHERE EXISTS (
    SELECT
        NULL
    FROM @c AS c
    WHERE c."id" = R."id")

If the above code is executed without the update it executes nearly instantly, so I think the right-sided function isn't the bottleneck. Even if I change SET starttime... to a fixed value, runtime stays nearly the same. @Result holds about 250.000 lines, @c about 20.000. I already added indices to the table vars - without big success.

Upvotes: 0

Views: 365

Answers (1)

Dan
Dan

Reputation: 10680

Try to rewrite your query to use a join instead of using WHERE EXISTS (which is known to perform worse in some cases):

UPDATE R
    . . .
FROM @Result AS R
INNER JOIN @c AS c ON c."id" = R."id"

Upvotes: 1

Related Questions