SPL: A Faster and Easier Alternative to SQL
3 min read
When it comes to databases, SQL is King.
SQL stands for Structured Query Language, the primary language used to interact with a database.
However, SQL has its limitations.
SQL Is Complex
Often, people refer to SQL as an easy language to write and learn.
When you write a SQL query, it almost reads like plain English.
This is not always the case, though.
SQL becomes complex and challenging to write and read when you have to solve more sophisticated problems.
The code snippet below is a recruitment test of a company called Raqsoft, with a low success rate.
SQL becomes unreadable when you have to perform certain operations.
select max (consecutive_day) from (select count(*) (consecutive_day from (select sum(rise_mark) over(order by trade_date) days_no_gain from (select trade_date, case when closing_price>lag(closing_price) over(order by trade_date) then 0 else 1 END rise_mark from stock_price ) ) group by days_no_gain)
SQL Is Slow-Performing
If you work as a backend engineer, you'll probably have been in situations where you had to sort your database.
Assuming that you work with large amounts of data, SQL can perform poorly when sorting data.
Imagine a table with thousands of rows, but you only want to select the top 20.
SELECT TOP 20 NAME_OF_COLUMN FROM NAME_OF_TABLE ORDER BY NAME_OF_COLUMN DESC
SQL will have to:
Sort all the data.
Select the top 20.
Throw away the remaining data.
So...What Could Be an Alternative?
The alternative is called SPL.
SPL stands for Structured Process Language.
SPL has several enhancements.
Some of them are:
Understanding of grouping and aggregation: with SPL, the grouping operation is split into a two-step autonomous operation.
High performance: for example, SPL allows you to avoid sorting data, therefore allowing for better performance.
The following operation calculates the maximum number of consecutive days that a stock price keeps rising:
With SQL, the above query would look like this:
WITH cte AS ( SELECT date, price, LAG(price) OVER (ORDER BY date) AS prev_price, ROW_NUMBER() OVER (ORDER BY date) AS rn FROM stock_prices ), cte2 AS ( SELECT date, price, prev_price, rn, CASE WHEN price > prev_price THEN 1 ELSE 0 END AS is_increasing, SUM(CASE WHEN price > prev_price THEN 1 ELSE 0 END) OVER (ORDER BY date) AS increasing_days ) SELECT MAX(increasing_days - rn + 1) FROM cte2;
You can see how verbose SQL becomes when you need to perform more complex operations.
Now you have a generic understanding of SQL's limitations and why SPL could be a great alternative.
SPL has an open-source GitHub repository if you'd like to start contributing.
If you enjoyed this article, you'll definitely benefit from subscribing to my FREE weekly newsletter, where I share curious insights about software engineering, career development and personal stories.
I hope to see you there.
Until next time!
SPL: a database language featuring easy writing and fast running
Did you find this article valuable?
Support Maddy by becoming a sponsor. Any amount is appreciated!