Written by

~~ retired but not tired ~~
Article Robert Cemper · Aug 8, 2017 1m read

Outperforming PostgreSQL and MySQL

In a previous exercise, I was able to show the power of Caché.
A medium-designed set of interdependent tables with some GB of data.
URLs cross reference over some million pages resulting in ~3 billion records

Competition was between

  • Caché
  • PostgreSQL
  • MySQL

Criteria were Speed + Storage consumption
I composed  a customized loader fed over a "raw" TCP connection
Mapping the "objects" into the final table by directly writing to Global Storage.,

Phase 1:  MySQL failed before reaching the 1st million records by it's
Incredible consumption of memory and disks space
Pase2:  Disk consumption of PostgreSQL was higher than Caché
And the load speed was remarkably lower. 
Mapping the "objects" to tables done in PHP resulting in multiple DB INSERTS 

For Caché the input was in principle a string. disassembled in the related Globals.
When Caché was done, PostgreSQL was significantly under 50% of the total.
The slim and effective design o​F Table sin Caché also showed a
dramatically better performance in data retrieval. Even with the unequal dimensions.
Finale furioso: 
The customer ordered a nicely dimensioned license for Caché.​​​​​​
 

Comments

Ben Spead · Aug 8, 2017

Robert - thanks for posting, and it's great seeing that you're still doing awesome work in this space!

All the best,

Ben

0
Alexey Maslov · Aug 10, 2017

Robert,

thank you for sharing your experience. I'm just curious:
- What was the final database size in GB?
- What tool will be used for data analyzis? If a homemade one, will it be based on Caché?

0
Robert Cemper  Aug 10, 2017 to Alexey Maslov

Alexey,
- The final size  after some design optimizations was
175 GB DataGlobals  + 216 GB IndexGlobals   ;  separated for backup considerations  (on 1 single drive sad )

- data analysis was all done with SQL + a bunch of "homemade" SQL Procedures / ClassMethods running specific subqueries.
 

0