XSM eXtended Sort Merge

1. External sorting to speed up database loading

Suppose you have to load large data files every night into your preferred database: Oracle, DB2, MySQL, SQL Server, Informix or Sybase.

In this example, we use MySQL, which is reasonably fast at loading data.

Suppose you have a heavily indexed table whose contents must be deleted and reloaded from files every night:

• if your external data is not sorted, your database server must do the work;
• if your external data is presorted, the database server only has to load it, without the overhead of building its indexes.

Consider the following benchmark:

• Input: 100 MB ASCII text file containing 1,023,009 records
• Variable-length, tab-separated records with five columns: two integers and three strings
• MySQL Server 4.0.10-gamma on Linux 2.4.18, Pentium II 550 MHz, 512 MB RAM (the same principle applies to any RDBMS)
• The chart shows total elapsed loading time in seconds:

This demonstrates the purpose of external sorting: presorting speeds up large data processing.

Do not leave this task to an integrated database engine that “can do everything”: it is not its job.

2. Merge / Split / Filter / Selective copy / Identify and remove duplicate records

You need to merge, split, filter or copy data according to defined criteria.

Consider a simple example: every day, you receive a sales report composed of 50 files and need to split the data by ZIP code, creating one distinct file per ZIP code.

Two solutions:

1. Use your RDBMS: many developers would choose this option, but it is not the right one.
   • Drop / create table: 30 seconds
   • Load 50 files into the table: 1 hour
   • Run a deduplication SQL job: 1 hour
   • Run 100 unload jobs, one per ZIP code: 2 hours

   Estimated total time: 4 hours

2. Use XSM as a batch external sort/merge utility.
   • In a single operation, XSM merges, sorts, deduplicates and selectively splits the data

   

   Estimated total time: 5 minutes