I was wondering that is it possible to create a table that is really fast and temporary (basically one that resides in ram) in MS SQL Server 2005 or later for inter software communication purposes? Do all the normal locking functionalities also work with such tables if possible? We have an old software that wants to receive messages through the db and currently it is using just a normal table although it really doesn't need to preserve its state over reboots. We would also like to use stored TSQL procedures on that table.
9
-
You could use a global temp table. Temp tables are stored in tempdb, which these days often (but not always) stays in memory. Or you could use Memory Optimized tables, but there are risks involved with that (bugs and memory leaks).– CharliefaceCommented Dec 25, 2023 at 22:04
-
3What problem are you trying to solve by switching to a temp table?– J.D.Commented Dec 26, 2023 at 2:27
-
@J.D. Just thinking that a temp table would be faster to operate on and more suitable for purposes where no data needs to be persistently stored– The amateur programmerCommented Dec 26, 2023 at 6:55
-
@Charlieface could you post that global temp table thing as an answer with example on how to set one up?– The amateur programmerCommented Dec 26, 2023 at 6:57
-
1@Theamateurprogrammer Sounds like an "if it ain't broke don't fix it" kind of situation. SQL Server already keeps regular tables in Memory, as needed, so the performance gains you'd see from switching to a temp table would be almost immeasurable most likely. And 10 inserts per second is hardly a concern on standard hardware.– J.D.Commented Dec 26, 2023 at 16:08
|
Show 4 more comments
1 Answer
It does seem, based on the question, you have an XY problem.
Taking the spirit of the question, though, you'll really only have a few options.
- SQL Server 2005 (wayyyy out of support) to SQL Server 2012: Regular tables.
- SQL Server 2014 - Now: Schema only Hekaton (In-Memory OLTP) Example
Just to put this in perspective, if you're running any version of SQL Server before 2019 at this point, you're essentially out of support.
We have an old software that wants to receive messages through the db and currently it is using just a normal table although it really doesn't need to preserve its state over reboots.
Why doesn't this continue to work? Why not rewrite the software? Why not update it (did the vendor go out of business?)? If you're moving away from the software, why invest any extra time in it?
If the software is that old, expecting it to perform correctly with newer features is questionable at best. I'd leave it as it is and move on to more impactful items. That's just me.
answered Dec 26, 2023 at 12:44
-
"Why doesn't this continue to work": it does still continue to work but we are adding more software components to use the same communication table and performance could become an issue in the coming years. We are working on a large industrial site with MANY legacy systems that have to continue working while we are renewing some other parts of the environment and the question is related to improving the db performance on that existing environment. The database works as a central data storage and is means of communications between multiple software components running on different machines. Commented Dec 26, 2023 at 15:48
-
3I wouldn't pre-optimize anything, if you aren't running into any issues, don't do anything. If you're starting to see issues, identify them and find solutions. Sounds like you should just leave it alone at this point. Commented Dec 26, 2023 at 16:09