But what if you're joining to this in-memory table and you are experiencing 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 TABLESimple 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!
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!
declare @tempIDs TABLE
pick_list_id integer not null PRIMARY KEY
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.