DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Because the DevOps movement has redefined engineering responsibilities, SREs now have to become stewards of observability strategy.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Related

  • What Is Data Locality?
  • How Trustworthy Is Big Data?
  • Enhancing Avro With Semantic Metadata Using Logical Types
  • A Deep Dive into Apache Doris Indexes

Trending

  • How to Merge HTML Documents in Java
  • Intro to RAG: Foundations of Retrieval Augmented Generation, Part 2
  • Go 1.24+ Native FIPS Support for Easier Compliance
  • The Role of AI in Identity and Access Management for Organizations
  1. DZone
  2. Data Engineering
  3. Big Data
  4. Three Performance Tricks for Dealing With Big Data Sets

Three Performance Tricks for Dealing With Big Data Sets

This article describes three different tricks that I used in dealing with big data sets (order of 10 million records) and that proved to enhance performance dramatically.

By 
Osama Yaccoub user avatar
Osama Yaccoub
·
Aug. 21, 21 · Opinion
Likes (5)
Comment
Save
Tweet
Share
3.6K Views

Join the DZone community and get the full member experience.

Join For Free

This article describes three different tricks that I used in dealing with big data sets (order of 10 million records), and that proved to enhance performance dramatically.

Trick 1: CLOB Instead of Result Set

The use case here was that I had a big database table (~ 10 million records) that I needed to query on one column in it, but I needed to eventually get all data inside it; imagine a table containing millions of customers and in one use case, you need to get the mobile numbers (MSISDN) of all of them at once inside your application. The first thing I tried was the ordinary result set approach, where I open a result set and continue fetching the result until it finishes. However, this was too slow even with tuning all the available parameters (fetch size, concurrency, etc.), so I went to the idea here. I created a stored procedure to compose all the mobile numbers as comma-separated CLOB, then the query returns the CLOB once, and it is then parsed at the application level. This trick enhanced the performance dramatically.

CLOB Instead of Result Set

Trick 2: Index Dropping for Bulk Update

The use case here is that I have a night job that updates a table with a bulk set of data (~10 million records). The table naturally has many indexes as many queries run against it. The point here is that bulk updates become very slow in case of having an index on the table because the index is being updated at the same time the data is updated; moreover, the index gets corrupted due to all these consecutive inserts. so the solution was to drop the indexes on the table first, make the bulk insertions, then rebuild the index again and execute the gather statistics command (as the database was Oracle). Again, the performance was enhanced to the magnitude of hours.

Trick 3: Using Mod Operation to Uniform Load Across Workers

Generally, when you have a data-intensive application that you want to make reports on, process, or transfer a subset of its data to another application, the first step is data preparation, where the target data subject to report, query, or process is moved to a staging table as a preparation for the next step, the next step is usually starting processing the data on this temp table using multiple workers (threads), a common challenge here is how to distribute the workload across workers uniformly, i.e. assume that the staging table now has 10 million records that you will distribute their processing across 20 workers for performance and efficiency, the best scenario is that each one process 500k records, the worst scenario is that one worker process 10M records while the remaining 19 threads process zero records. So, how do we distribute the workload uniformly across workers?

There are multiple options here:

1. Depending on the total number of records in the table (count(1)) and divide it by the number of threads, then make an if condition to check if the worker quota is reached and accordingly quit:

Java
 
//pseudo code

// var totalRecordsCount = select count(1) from STAGING_TABLE

// var workerQuota= totalRecordsCount /numberOfThreads

//while (numberOfProcessedRecords < workerQuota) {

//the processing logic }


Clearly, you will need some logic to guarantee that there is no overlapping between workers on the data processed. This will add complexity to the code, impact the performance, and, finally, might not be accurate.

The code is not efficient.

2. Specify a range for each worker to process. For example, each worker will process the data of a certain hour or day,  a certain range of SIDs, MSISDNs, part numbers, etc. (depending on your business).

Java
 
//pseudo code

//open a cursor for records processing_date between(11AM, 12 PM)

// or SID between (0000000,00500000)

// or AGE between (10,15)

//etc ....


The issue here clearly is that there is no guarantee for uniform load across workers unless the data has a very special uniform distribution over the selected field, which is generally not the case.

3-.The selected approach, which is a fine-tuning for the previous one, is to create an additional column in the staging table that should be populated during data preparation. The value in this column is mod(SOME_RANDOM_VALUED_COLUMN,noOfWorkingThreads) , to understand what this means. Usually, you will have some column that has numeric, random (or pseudo-random) values like SID, MSISDN, part number, voucher number, etc. So, applying the mod operation on such a column will result in numbers from 0 to noOfWorkingThreads-1 that are nearly uniformly distributed over the table. You can then make each of your n workers select its related rows by filtering on this column.

Java
 
//pseudo code

//int threadId; //a sequence number that identifies the running thread, value ranges from 0 to noOfWorkingThreads-1

//cursor targetRows = select * from STAGING_TABLE where SID_MOD = threadId


This solution guarantees no overlapping between workers, no table rows counting overhead, and no checking conditions, thus the best performance.

Mod Operation to Uniform Load Across Workers


Big data Database Processing

Opinions expressed by DZone contributors are their own.

Related

  • What Is Data Locality?
  • How Trustworthy Is Big Data?
  • Enhancing Avro With Semantic Metadata Using Logical Types
  • A Deep Dive into Apache Doris Indexes

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!