SPL High-performance algorithm

 

General

The impasse of SQL performance optimizing

SPL: a database language featuring easy writing and fast running

How the performance improvement by orders of magnitude happened

Algorithm

SQL Performance Enhancement: IN Operator in WHERE Clause

SQL Performance Enhancement: Segmentation-based Grouping & Aggregation

SQL Performance Enhancement: TopN and Intra-group TopN

SQL Performance Enhancement: DISTINCT & COUNT(DISTINCT) on Big Data

SQL Performance Enhancement: Big Table Associations on the Primary Key

SQL Performance Enhancement: Multiple Grouping & Aggregate Operations on a Big Table

SQL Performance Enhancement: Select the Earliest Records in each Group

SQL Performance Enhancement: Count N Earliest Events in each Group

SQL Performance Enhancement: Conversion Funnel Analysis

SQL Performance Enhancement: Binary Flag

SQL Performance Enhancement: Queries on Highly Concurrent Accounts

SQL Performance Enhancement: highly concurrent queries with associated dimension table


Learn performance optimization skills from TPCH tests

Performance optimization skill: Use Multi-purpose traverse to speed up multiple grouping

Performance optimization skill:TopN

Performance optimization skill: Pre-Joining

Performance optimization skill: Partial Pre-Association

Performance optimization skill: Numberizing Foreign Key

Performance optimization skill: Association For Dimension Table Filtering & Computation

Performance optimization skill: Ordered MERGE

Performance optimization skill: Ordered Locate Association to speed up the filtering on joined primary-sub tables

Performance optimization skill: Attached Table

Performance optimization skill: Associating Small Fact Table with Big Dimension Table

Performance optimization skill: Associating Big Fact Table with Big Dimension Table

Performance optimization skill: ordered grouping

Performance Optimization Skill: Second-half Ordered Grouping

Performance Optimization Skill: First-half Ordered Sorting

Cases

Open-source SPL Speeds up Query on Detail Table of Group Insurance by 2000+ Times

Open-source SPL improves bank’s self-service analysis from 5-concurrency to 100-concurrency

Open-source SPL turns pre-association of query on bank mobile account into real-time association

Open-source SPL speeds up batch operating of bank loan agreements by 10+ times

Open-source SPL optimizes batch operating of insurance company from 2 hours to 17 minutes

Open-source SPL speeds up intersection calculation of customer groups in bank user profile by 200+ times