If data is stored in a disk-based table, it must be read into memory before it can be used, and it must be written back to the disk after it has been used. In-memory tables provide higher performance because all the data resides always in the main memory; the server may use more efficient techniques to provide the maximum performance for accessing and manipulating data.
Almost any database server will perform faster if it has more memory and can store a larger percentage of its data in the cache memory of the server. However, solidDB® main memory engine’s high-performance in-memory technology does much more than merely copy data into memory. The solidDB® main memory engine also uses index structures that are optimized to work with data that is stored entirely in memory. The solidDB® main memory engine also takes into account issues that arise with in-memory tables, such as memory “fragmentation” when tables grow or shrink.
Temporary and transient tables provide higher performance than persistent tables for the following reasons:
▪Data in temporary tables and transient tables is stored solely in memory; it is never written to disk. If you shut down and restart the server, or if the server terminates abnormally, the data is lost. In the case of temporary tables, the data is discarded at the end of the user session; it does not even remain until the server is shut down.
▪Temporary and transient tables do not log transaction data to disk. The data is not recoverable after an abnormal server termination.
▪When the server does its periodic checkpoint operations, which write database data to the disk drive, the data in temporary tables and transient tables is not written to the disk.
▪Temporary tables and transient tables use a more efficient data storage structure than regular in-memory tables use.
▪Temporary tables have a further performance advantage over transient tables. Sessions (connections) do not see each other’s records in a temporary table, and therefore they do not need sophisticated concurrency control – for example, there is no need to check for locking conflicts on records within the table.
If a table is stored in memory, all indexes on that table are also stored in memory. This improves performance but also consumes memory space. In general, in‑memory indexes can be extremely fast, and you should use them to ensure fast access to the data of the tables. However, if you do not have enough memory to store all your tables and indexes in memory, adding a particular index might not help in all cases. This is because even though it will speed up some queries, it will slow up other queries by using memory that otherwise could be used to put other tables in memory.