0

I am trying to build a stored procedure that retrieve information from few tables in my databases. I often use variable table to hold data since I have to return it in a result set and also reuse it in following queries instead of requiring the table multiple times.

Is this a good and common way to do that ?

So I started having performance issues when testing the stored procedure. By the way is there an efficient way to test is without having to change the parameter each times ? If I don't change parameter values the query will take only a few milliseconds to run I assume it use some sort of cache.

So I was starting having performance issues when the day before everything was working well so I reworked my queries looked that all index was being used correctly etc. Then I tried switching variable table for temp table just for testing purpose and bingo the 2 or 3 next tests ran like a charm and then performance issues started to appear again. So I am a bit clueless on what happens here and why it happen.

I am running my tests on the production db since it doesn't update or insert anything. There is a piece of code to give you an idea of my test case

--Stuff going on to get values in a temps table for the next query

DECLARE @ApplicationIDs TABLE(ID INT)

-- This table have over 110 000 000 rows and this query use one of its indexes. The query insert between 1 and 10-20k rows
INSERT INTO @ApplicationIDs(ID)
    SELECT ApplicationID 
    FROM Schema.Application 
    WHERE Columna = value 
      AND Columnb = value 
      AND Columnc = value

-- I request the table again but joined with other tables to have my final resultset no performance issues here. ApplicationID is the clustered primary key
SELECT Columns 
FROM Schema.Application
INNER JOIN SomeTable ON Columna = Columnb
WHERE ApplicationID IN (SELECT ID FROM @ApplicationIDs)

--There is where it starts happening this table has around 200 000 000 rows and about 50 columns and yes the applicationid column is indexed (nonclustered). I use this index that way in few other context and it work well just not this one

SELECT Columns 
FROM Schema.SubApplication
WHERE ApplicationID IN (SELECT ID FROM @ApplicationIDs)

The server is in a VM with 64 gb of ram and SQL have 56GB allocated.

Let me know if you need further details.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex Samson
  • 125
  • 2
  • 10
  • 4
    I'd remove the use of IN and instead use a INNER JOIN. Also, in general, I'd recommend using temporary tables over variable tables due to performance reasons. Here is a good article on usage: https://www.sqlshack.com/when-to-use-temporary-tables-vs-table-variables/ – QuestionGuyBob Oct 25 '19 at 17:26
  • 1
    Also, see the following for why IN clause needs to change. https://stackoverflow.com/questions/1013797/is-sql-in-bad-for-performance – QuestionGuyBob Oct 25 '19 at 17:31
  • Thanks that's a lot of useful infos i'll give it a try and post back if I resolve the issue with it – Alex Samson Oct 25 '19 at 18:02
  • 1
    @QuestionGuyBob - your second link (about `IN` clause) does not apply in this case as this is a table source. It is treated like a semi-join. (See comments in this [question](https://stackoverflow.com/questions/58297923/avoid-function-in-where-clause-due-to-that-its-causing-performance-issue#comment102960086_58297923)) – Alex Oct 26 '19 at 07:29
  • 1
    you need to get the execution plans for the slow and fast cases and see what is different. If using a table variable put `OPTION (RECOMPILE)` on the problem statement so that it can adjust the plan for the number of rows in the source. Also add a primary key on that column so that SQL Server can determine it is unique – Martin Smith Oct 26 '19 at 21:15

0 Answers0