SPL: A Faster and Easier Alternative to SQL

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:

stock_price.sort(trade_date).group@i(closing_price<closing_price[-1]).max(~.len())

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.

Conclusion

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!

🙋🏾‍♀️

ADDITIONAL RESOURCES:

SPL: a database language featuring easy writing and fast running