CoSort Pre-Sorts Can Improve Sybase Load, Reorg and Query Performance

Home » Solutions » ETL DB Acceleration » Sybase

Improve Sybase Performance

Sybase Recommends External Sorting to Accelerate Database Loads

Challenges:

You may face one, or more, of these time-consuming issues:

• Unloading and loading of large Sybase tables
• Routine utility operations (reorgs)
• Complex queries
• Database migration or replication
• Generating test data for Sybase

Specific performance diagnoses and tuning also take time, and may affect other users. Finally, stored SQL procedures may also be programmed inefficiently, require optimization, and then still take too long to run.

Solutions:

Accelerate reorgs by performing them offline; i.e. leveraging the power of the file system instead of encumbering the datatase.

Start by dumping table data through IRI's Fast Extract (FACT) utility, now also compatible with Sybase IQ and ASE, OCS 15 and later. If you do not have FACT, you can offload tables using a SQL SELECT statement to flat files.

Next, use the SortCL interface in the CoSort package to filter, sort, join, group or otherwise transform the extracts in parallel. The Sybase SQL Server Reference Manual recommends external pre-sort to accelerate index creation during database loads. Multiple tables created quickly in query index order should help speed queries.

CoSorting data in the file system prior to loading Sybase can:

• Speed load performance between 2 and 10X
• Remove the sorting aspect of the load
• Reduce database and disk overhead
• Increase load processing rate (rows per hour)

The recommended technique says to pre-sort flat files on the clustered index key. CoSort is designed to sort huge files in parallel, with performance scaling linearly in volume.

Create a clustered index, but first eliminate the indexes by:

creating tables without indexes or triggers, or by dropping them from the table to be loaded.
CoSort the data on the primary index key.
bcp load the sorted table using fast bcp path (as there are no indexes on the table).
Use the SQL command CREATE INDEX, using the SORTED_DATA option, to create the clustered index.

CoSort's SortCL program can also transform and reformat data for database migrations and custom (detail, summary and delta) reports, and protect sensitive data at the field level with a variety of encryption, masking, and other functions.

To rapidly populate Sybase with safe test data, consider using IRI's high-volume test data generator, RowGen. RowGen uses your data models to automatically generate the test data for an entire database with referential integrity.

IRI, The CoSort Company