Tag: database
Is There Any Simple and Lightweight In-memory Database Technology?
HANA, SPARK and Redis are commonly used in-memory databases, but they all have complex and heavy frameworks, compromising their ranges of applications. The popular simple and lightweight in-memory technology is SQLite, which is nimble and simple-framed and can be directly embedded in a Java program. Its disadvantages include lack of independent services and support for stored procedures, unstable environment and slow execution, weak computing abilities, and complicated data loading process for external data use.
The technology has better and more powerful embodiment – esProc SPL.
Open-source SPL Eliminates Tens of Thousands of Intermediate Tables from Databases
Intermediate tables are data tables in databases specifically used to store intermediate results generated from processing the original data – which is why they are so named. They are summary tables usually created for speeding up or facilitating the front-end queries and analysis. For some large organizations, years of accumulation results in tens of thousands of intermediate tables, which is an incredible number, in their databases, bringing great trouble to database operation and usage.
The large number of intermediate tables occupies too much database storage space, putting enormous pressure on storage capacity and increasing demand for capacity expansion. But database space is expensive and capacity expansion is exceedingly costly. Moreover, often there are restrictions on the expansion. It is not a good choice to cost you an arm and a leg with storing intermediate tables also because too many of them reduce database performance. Intermediate tables are not created out of thin air. Rather, they are generated from the original data through a series of computations that consume database computing resources. Sometimes, a lot of intermediate tables are produced during a computation. This consumes a large number of resources, and in serious cases, can slow down queries and transactions.
Why Are Databases Faster Than Text Files？
Why text files are slower
The main reason is that there are too many data parsing actions.
Let’s look at an example. Suppose we need to convert text "12345" to a binary integer in the memory. The process is like this:
How Reporting Tools Auto-convert SQL between Different Databases?
There are more or less differences in terms of function between various types of databases. When we are trying to migrate reports from one database to another, the existing SQL containing a different set of function syntax need to be converted at the same time. Usually, a SQL data set in a report is static and fixed. As an automatic conversion is impossible, we have to modify corresponding SQL statement manually and replace the old with the new.
Yet the auto-conversion of SQL in report between databases can be achieved with esProc SPL. SPL designs a set of standard SQL query syntax that has a wealth of built-in functions (and the number is growing) for describing as many common computations as possible. The SPL sqltranslate function can translate the standard SQL to different database SQL dialects.
How to Write Universal SQL That Can Be Executed in All Databases?
Databases implement SQL in their own sets of syntax. We need to translate function syntax from one database to another so that functionally equal SQL query statements can be executed in an incompatible system.
The ORM technique lets programmers convert queries to SQL of another type of database. This, in effect, is equivalent to having some degree of portability. The technique, however, is only suitable for dealing with simple SQL in OLTP scenarios. It is hard to handle the complicated SQL migration tasks under OLAP scenarios, such as the use of functions that are not supported directly by ORM and complex SQL queries where the FROM clause contains a subquery.
