0

I have a stored procedure, MySProc, that takes 7 seconds to execute.

I have made the following stored procedure, MySProcWithWhereClause, where I simply just want to have the table from MySProc, but with an extra WHERE clause on it.

Here it is:

CREATE PROCEDURE MySProcWithWhereClause
BEGIN
    CREATE TABLE #temptableT 'params'

    INSERT INTO #temptableT
        EXEC MySProc

    SELECT * 
    FROM #temptableT 
    WHERE param1 = param2

    DROP TABLE #temptableT
END

However, this stored procedure takes ages to execute. I waited for 2 minutes and decided I need another solution.

Why is it so slow, and is there a faster way?

I checked out other questions, for example this one, but it did not give me a proper solution.

Zebraboard
  • 210
  • 1
  • 13
  • How many records does the Proc MySProc returns? – Srinivasan Rajasekaran Sep 20 '22 at 11:53
  • 20713 rows, 24 columns – Zebraboard Sep 20 '22 at 11:54
  • The "slowness" is almost certainly `MySProc`, not the procedure above (though having an appropriate index on `#temptableT` *might* help things a little too). Also, using a `TOP` without an `ORDER BY` is a flaw; this will mean that the RDBMS will return an *arbitrary* row, and that row could be different *every time* you run the query. – Thom A Sep 20 '22 at 11:55
  • But MySProc only takes 7 seconds to execute - the query above takes 2+ minutes. Is there a faster way to save the "output" of the stored procedure in a (hash)table? – Zebraboard Sep 20 '22 at 11:56
  • 1
    Does your stored procedure have parameters? Executing as part of an `INSERT INTO` is not the same as executing outside of it, so you may just be hitting a bad query plan. The [usual steps for diagnosing that apply](https://sommarskog.se/query-plan-mysteries.html), to a first approach, try adding `WITH RECOMPILE` at the end of your `EXEC` to see if that shakes things loose. Also -- a [hash table](https://en.wikipedia.org/wiki/Hash_table) is something very different from a temp table, so I recommend not using that term to avoid confusion. The hash character is incidental. – Jeroen Mostert Sep 20 '22 at 12:01
  • Thank you so much. WITH RECOMPILE actually made it faster, and now I can see whats going on. It might be very slow because the table from MySProc is being selected multiple times! (I can see that on the output). But I cant see why that is the case from my query. – Zebraboard Sep 20 '22 at 12:08
  • It's always worth considering if the sproc an be rewritten (or copied) to an inline table-valued function, as those perform much better and you wouldn't need to select into a temp table first, since you could just add the `WHERE` to it. Another option worth considering is changing the sproc so it can do the `WHERE` through parameters, or making a copy of the original that can do so. Selecting into a table just to filter the results afterwards is the least efficient way, even if you could get it to perform roughly the same as a plain execution. – Jeroen Mostert Sep 20 '22 at 12:11
  • Your code as written in this question simply doesn't do that. Your actual code might, depending on what we can't see (if the sproc itself is called more than once, for example), or you may be confused about what `param1 = param2` is really filtering, or your sproc is executing queries that are nondeterministic. There is a very small chance that query execution is going wrong but only inside an `INSERT`, but this is pretty improbable. Try executing just the `INSERT .. EXEC` but outside the sproc to check what's in the temp table (but don't forget to empty it). – Jeroen Mostert Sep 20 '22 at 12:40

0 Answers0