This content originally appeared on DEV Community and was authored by Ritesh Rungta
The Challenge
In my app (React + Spring Boot + Oracle), dealing with large datasets led to frustratingly slow processing time. I needed a solution to accelerate performance without compromising accuracy or completeness.
The Solution: NTILE + Parallel Processing
NTILE is a powerful SQL window function designed to partition a result set into a specified number of roughly equal-sized chunks, known as "tiles." Each row is assigned a partition number based on its position in the ordered set.
By using NTILE, I split the query results into manageable chunks and processed these partitions in parallel. This approach allowed me to fetch and handle data simultaneously, significantly reducing wait times.
Here’s a practical example of how to implement this:
WITH PartitionedSales AS (
SELECT
sales_id,
sales_amount,
sales_date,
NTILE(2) OVER (ORDER BY sales_id) AS partition_number -- Assigns a partition number (1 or 2) to each row
FROM
sales
WHERE
sales_date BETWEEN '2023-01-01' AND '2023-12-31'
)
SELECT *
FROM PartitionedSales
WHERE partition_number = :partitionNumber -- Replace :partitionNumber with the actual partition number (1 or 2)
In the above SQL snippet:
- NTILE(2) divides the data into two equal chunks which will be sorted based on sales_id.
- Replace :partitionNumber with 1 or 2 to fetch data from the corresponding partition.
On the frontend, you can use parallel processing to fetch each partition efficiently:
async function fetchPartition(partitionNumber) {
const response = await fetch('/api/sales?partition=' + partitionNumber});
return response.json();
}
async function fetchData() {
try {
const [partition1, partition2] = await Promise.all([
fetchPartition(1), // Fetch the first partition
fetchPartition(2) // Fetch the second partition
]);
// Combine and process results
const combinedResults = [...partition1, ...partition2];
processResults(combinedResults);
} catch (error) {
console.error('Error fetching data:', error);
}
}
In this code:
- fetchPartition retrieves data for a specific partition.
- fetchData runs both fetch operations in parallel and processes the combined results.
How You Can Do It Too
- Identify the Heavy Queries: Find the queries that are slowing down your app.
- Apply NTILE: Use the NTILE function to divide the query results into smaller parts.
- Parallel Processing: Execute these smaller queries in parallel, leveraging your app’s ability to handle concurrent tasks.
If you’re looking to boost performance in your data-heavy applications, give this method a try. It’s a smart, effective way to make your queries work harder, not longer.
Important Consideration
When handling concurrent requests, the demand on database connections can become significant. This heavy utilization of connections may strain your database, potentially leading to performance bottlenecks. It's essential to monitor and manage the number of concurrent requests to ensure that your database remains responsive and performs efficiently.
This content originally appeared on DEV Community and was authored by Ritesh Rungta
Ritesh Rungta | Sciencx (2024-09-24T18:33:25+00:00) Slow SQL Queries? Boost Your App’s Performance with This Technique. Retrieved from https://www.scien.cx/2024/09/24/slow-sql-queries-boost-your-apps-performance-with-this-technique/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.