6 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