DZone
Big Data Zone
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
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Big Data Zone > ETL vs Pure SQL

ETL vs Pure SQL

Which one will come out on top? You should definitely check out the results so you can decide which one to use for your next project.

Mateusz Komendołowicz user avatar by
Mateusz Komendołowicz
·
Dec. 30, 16 · Big Data Zone · Opinion
Like (5)
Save
Tweet
6.00K Views

Join the DZone community and get the full member experience.

Join For Free

In this post I would like to compare the efficiency of upsert operation in ETL tools and SQL. This is one of the most commonly used operations when building ETLs and integrating systems. Developers should always ask themselves whether not to use custom written MERGE instead of creating jobs in selected ELT tool.

As an ETL example, I will use Talend Open Studio 6.1. An SQL example will be MERGE operation in SQL Server. We will start with building two tables, which will be used for upsert. Please note, that I will populate sourceTable with 1 000 000 rows and destinationTable with 500 000 rows using modified script from http://blogbi.pl/generating-milions-of-rows-in-sql-server/ 

CREATE TABLE sourceTable (     
  id int PRIMARY KEY,     
  number int );
CREATE TABLE destinationTable (     
  id int PRIMARY KEY,     
  number int ); 
-- mentioned script here 
  SELECT COUNT(*) as [#sourceCount] FROM dbo.sourceTable; 
SELECT COUNT(*) as [#destinationCount] FROM dbo.destinationTable;

Talend Open Studio upsert operation implementation

First, we will build job in Talend. Normally you can use three components: tMSSqlInput (for  sourceTable), tMSSqlOutput (for destinationTable) and obviously tMap. Then you would switch component action for data for (Insert or Update) for tMSSqlOutput. However this approach creates a flow, which transfers data with speed of dozens rows per second, so it is extremely poor. So, let me show you how to build a well-performing Talend upsert job. 

talend upsert job

 I have used sourceTable and destinationTable as inputs for tMap. Then I have selected inner join between them to catch which rows are new and which are already in destination table. Based on that I will filter them and insert/update them.

 With such an approach the performance is better than built-in insert/update in Talend. It works in ~ 20 seconds.

SQL Server upsert operation implementation 

Now let’s check out how MERGE works in comparison to Talend.

The SQL code for MERGE:

CREATE PROCEDURE dbo.MergeExample AS BEGIN     
MERGE dbo.destinationTable AS target       
USING dbo.sourceTable AS source      
ON target.id = source.id    
WHEN MATCHED THEN           
UPDATE SET number = source.number     
WHEN NOT MATCHED THEN          
INSERT (id, number)           
VALUES (source.id, source.number); END;

In Talend you can just use TMSSqlRow with SQL Query EXEC dbo.MergeExample.

Performance: ~2 s.

So SQL is 10 times faster than Talend in this case.  

Summary

This simple theoretical test shows the same what practice taught me. Pure SQL is much faster than any ETL tool in case of many operations. With more data and more complicated transformations the advantage of SQL will be even bigger. I have seen cases where pure well written SQL is even 500 times faster than ETL tools.

Also, please note that when you have a lot of transformations, it is nearly always easier to write Table -> View – > Merge flow in SQL, then create complex jobs in ETL tool.

Therefore I would like to suggest you consider this option while developing your ETLs.

Extract, transform, load sql Database

Published at DZone with permission of Mateusz Komendołowicz, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • No-Code/Low-Code Use Cases in the Enterprise
  • SSH Tutorial: Nice and Easy [Video]
  • How to Make Git Forget a Tracked File Now in .gitignore
  • Understand Source Code — Deep Into the Codebase, Locally and in Production

Comments

Big Data Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

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

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo