Dramatically Speed Up Stored Procedures using Temp Tables

If you're wondering how to create a list, or temp table in a SQL Server Stored Procedure, look here.

But what if you're joining to an in-memory table and you experience performance issues?  I had a multiple join against such a table, and found that the query was taking over 12 seconds to complete - causing a timeout ocassionally.  Here was the temp table declaration:

    declare @tempIDs TABLE
    (
        pick_list_id integer
    ) Simple enough, right? Just a long list of integers.  But like I said the entire stored procedure was taking over 12 seconds to execute.  In a database table, my first approach would be to make an index on the column.  So that's what I did in my stored procedure.  It turned out to be incredibly simple and improved performance from 12 seconds to 30 milliseconds.  Looky!

    declare @tempIDs TABLE
    (
        pick_list_id integer not null PRIMARY KEY
    ) That's it! The rest of the stored procedure is exactly the same.  That's a 40000% speed boost!  Not bad for one modified line of code!


...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.