0

I have stored procedures with multiple temp tables in it. I need to run these Stored Procedures in SSIS (using SSDT 15.8.1). Since SSIS doesn't work for Stored Procedure with temp tables, it was recommended to use table variables instead of temp tables.

Now, my performance is bad. The Stored Procedure is taking a long time(8 min approx instead of previous 11 sec).

How do I resolve this without using temp tables? Is there any alternatives to table variables so that my performance doesn't get affected?

Thanks in advance.

Srinidhi Ram
  • 1
  • 1
  • 5
    " Is there any alternatives to table variables so that my performance doesn't get affected?": - Yes. temp tables.... – Mitch Wheat Oct 16 '18 at 14:06
  • 2
    Not sure I understand what you mean by "Since SSIS doesn't work for Stored Procedure with temp tables". Also I am pretty sure that whatever alternative you settle on will affect your performance. That is the whole point of your post in the first place, to positively affect performance. – Sean Lange Oct 16 '18 at 14:09
  • See here (I'm close to flagging it as a dupe): https://stackoverflow.com/questions/31858864/ssis-stored-procedure-uses-temp-table-2008-and-2014 – Tab Alleman Oct 16 '18 at 14:10
  • Please include your code for us to review and advise on. Then we'll be able to see what is affecting your performance. Also try to include your Execution Plan for this query... – Attie Wagner Oct 16 '18 at 14:14
  • 4
    Aside from the workarounds you can use in SSIS to use temp tables anyway (which the linked question has some explanation about; see also [here](http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/119024/)), one of the most common causes of performance degradation when using table variables is because the optimizer fails to account for their actual cardinality. Specifying `OPTION (RECOMPILE)` on a statement that uses a table variable forces the optimizer to consider the actual cardinality instead of defaulting to 1. This can often work wonders for bad execution plans. – Jeroen Mostert Oct 16 '18 at 14:14
  • If I'm not mistaken, if you use Functions utilizing table variables, when running the function the first time it will take a while, but after it will not. Just my 10 cents... – Attie Wagner Oct 16 '18 at 14:17
  • Could always create permanent tables that are reloaded each run. Maybe in their own schema – UnhandledExcepSean Oct 16 '18 at 14:18
  • To clarify: the link I gave talks about the problems caused by `SET FMTONLY ON` in SSRS, but SSIS uses this as well, which is why it fails to determine the result set metadata for procedures using temp tables. The workarounds involve noticing that `FMTONLY` is in effect and ensuring the correct result set is produced anyway (at least in types), which will make SSIS happy. On actual execution time, the stored procedure can do whatever you like. – Jeroen Mostert Oct 16 '18 at 14:19

1 Answers1

0

you can create PK index(s) on your table variables to help with process. But i found this article that suggests that you can use temp tables by converting them to global temp tables. https://www.mssqltips.com/sqlservertip/2826/how-to-create-and-use-temp-tables-in-ssis/

junketsu
  • 533
  • 5
  • 17