Transforming Data Insights
Pre-Calculation to Dynamic Ad Hoc Queries
The Challenge
Bank W's index query system, used for calculating and displaying aggregate indexes, relied on pre-calculated methods that became a bottleneck as the number of indexes grew. Challenges included:
- Massive Data Volume: Daily generation of millions of records for deposits and loans.
- High Complexity: SQL queries were too complex and caused database overloads.
- Real-time Requirements: Thousands of indexes needed simultaneous calculation within seconds.
- Hardware Limitations: Existing systems required impractical large-scale clusters for real-time queries.
Performance
Real-time calculation of indexes (100-200ms per index) with 2000 concurrent queries completed in 3 seconds on a single server.
Development Efficiency
Reduced programming complexity and improved maintainability with concise algorithms.
Scalability
Easily deployable on clusters to handle higher concurrency.
The QDBase Solution
QDBase was utilized to replace SQL-based pre-calculation with real-time, high-performance computation.
Enhanced Performance
Real-time calculations, previously unfeasible, became highly efficient:
- Speed: Each index calculation, including front-end request and chart rendering, took only 100-200 milliseconds.
- High Concurrency: The system processed 2000 indexes in just 3 seconds on a single server.
Scalability
QDBase SPL's cluster functionality provided an easy-to-deploy solution for scaling to meet increased demand. Adding nodes to the cluster enabled the system to support larger numbers of concurrent users without significant complexity.
Cost Efficiency
The optimized approach eliminated the need for large server clusters:
- A single virtual server with 64-core CPU, 256GB RAM, and 1TB HDD was sufficient to handle real-time queries for the bank’s requirements.
- The reduced reliance on hardware minimized both initial setup costs and ongoing maintenance expenses.
Simplified Development and Maintenance
QDBase’s built-in high-performance algorithms and concise coding structure offered significant advantages:
- Rapid Implementation
- Solutions were implemented within days, significantly reducing time-to-market.
- Lower Error Rates
- Shorter, more intuitive code reduced the likelihood of bugs and improved debugging efficiency.
- Maintainability
- The modular nature of SPL's functionalities made future updates and troubleshooting straightforward.
Improved User Experience
Bank staff experienced faster query responses and improved interface performance:
- Complex query pages containing hundreds of indexes loaded seamlessly.
- User satisfaction increased due to the near-instantaneous availability of operational insights.
QDBase Feature Highlights
- Columnar Storage
- Efficient retrieval of necessary columns and improved compression ratios with deduced storage and faster data access.
- Ordered Merging
- Simplified table joins with single-pass traversal and reduced computational complexity compared to hash-based joins.
- Multi-threading
- Native support for multithreaded queries to leverage multicore processing.
- High-performance Algorithms
- Efficient computation without lengthy custom coding.
Conclusion
Real-time, scalable query systems can replace traditional pre-calculated approaches with proper algorithm optimization. QDBase enabled Bank W to meet increasing demands while ensuring performance, scalability, and cost-effectiveness. This case highlights the importance of selecting the right tools and techniques to unlock the potential of big data systems.
Read the full in-depth study, with code examples on the SCUDATA Blog
Read about more use cases with real results on our Case Studies page