Practice#1: Regular Filtering and Grouping & Aggregation using the orders table as an example to externalize data with SPL and accelerate regular filtering and grouping & aggregation operations
This article describes step by step how to export a database table to SPL storage files and how to take advantage of columnar and composite column formats to improve the performance of queries that filter and aggregate data. It also includes practical recommendations for applying cursor processing, cursor filtering and parallelism.
Preparation and export with the SPL ETL tool
Open the SPL ETL tool from the SPL installation path and create a new ETL process. Connect the data source and drag the orders table to the work area. Edit the table to configure the export options and in the data path section indicate the directory where the exported files will be saved. Create an SPLX file or export directly to a BTX binary file. Save the ETL process with a descriptive name such as Q1.etl and run the export.
Example SQL query for grouping and aggregating
select employee_id, count(*) as order_count, sum(shipping_fee) as total, avg(shipping_fee) as average, max(order_date) as latest_order_date from orders where order_date between 2024-01-01 and 2024-10-31 and shipper_id <> 1 and shipping_fee > 10 group by employee_id;
In the MySQL database this example took approximately 11 seconds to run in the tests described.
SPL approach with cursor and filtering
When the orders table is too large to be read entirely into memory, SPL can use a cursor to read and process in batches. The cursor retrieves only the fields needed to evaluate conditions, reducing object creation and memory usage. Use the select function to apply filtering equivalent to WHERE in SQL. To group, use SPL grouping syntax where the part before the semicolon is the grouping key and the part after contains the aggregate expressions. Unlike SQL, SPL natively combines key and aggregated values in the result.
Indicative times: the initial SPL script with cursor reading and BTX format took about 2.2 seconds in the test, already showing an improvement over the direct MySQL query.
Using composite columnar format CTX
The CTX format stores data by columns and is especially useful when the table has a large number of fields but queries access few of them. Creating a CTX requires defining the data structure, so the process is somewhat more detailed than for a BTX. Running the same processing on a CTX, the time dropped to 1.9 seconds in the test.
Cursor filtering to minimize reads
Attach filtering conditions to the cursor so that only the fields involved in the conditions are read first, and only if these are met are the additional fields needed to build the record retrieved. This reduces disk reads and unnecessary object creation. In the test, the cursor filtering algorithm on CTX lowered the time to 1.8 seconds.
Easy parallelism in SPL
SPL facilitates parallel processing by specifying the number of threads. Adjust the number of threads to the available CPU cores, for example 8. Adding the parallelism option to the cursor for a BTX lowered the time to 0.6 seconds and for CTX to 0.5 seconds in the tests performed.
Comparative performance summary in seconds
MySQL direct query: 11 seconds
SPL with BTX and sequential cursor: 2.2 seconds
SPL with CTX and sequential cursor: 1.9 seconds
SPL with CTX and filtered cursor: 1.8 seconds
SPL with parallelism in BTX: 0.6 seconds
SPL with parallelism in CTX: 0.5 seconds
Practical observations
SPL storage and data export are especially suitable for scenarios with static historical data that is frequently queried. The CTX format is ideal when queries use few columns from wide tables. Cursor filtering and parallelism are two key techniques to minimize I/O and take advantage of the CPU.
Suggested exercises
1 Group by customer and calculate the total freight for each customer applying a condition that combines order_date and employee_id
2 Export a large table from a known database and generate a BTX and a CTX. Run the same aggregations using both formats and compare times and resource usage
About Q2BSTUDIO
Q2BSTUDIO is a software development company specialized in custom applications and custom software. We offer artificial intelligence and AI solutions for businesses, we develop AI agents and apply Power BI and business intelligence services to transform data into decisions. Our services include cybersecurity, aws and azure cloud services and consulting for scalar projects. We are experts in integrating artificial intelligence models, secure cloud deployment and creating custom applications that incorporate AI agents to automate processes.
Featured services from Q2BSTUDIO
Custom applications and custom software adapted to operational needs. Implementation of artificial intelligence, AI for businesses and AI agents that improve productivity. Cybersecurity solutions to protect critical data. aws and azure cloud services to deploy applications and models scalably. Business intelligence services and dashboards with Power BI for visualization and analysis.
Keywords for positioning
custom applications, custom software, artificial intelligence, cybersecurity, aws and azure cloud services, business intelligence services, AI for businesses, AI agents, power bi
Resources and license
SPL is open source and the source code is available on GitHub in the official esProc repository. You can download a trial version from the official website and follow the steps described to export tables and test the different storage formats.
Contact
If you want Q2BSTUDIO to help you externalize data, optimize queries or develop custom applications that integrate artificial intelligence and cybersecurity, contact our team for an initial consultation and a personalized project proposal
