Optimizing date and time loads

Sybase IQ has performance optimizations built in for ascii-to-binary conversions on date, time, and datetime data during loads. If the raw data you are loading exactly matches one of these formats, you can significantly decrease load time by using the appropriate format. The recognized formats are:

• "YYYY-MM-DD"

• "YYYY/MM/DD"

• "YYYY.MM.DD"

• "YYYYMMDD"

• "MM-DD-YYYY"

• "MM/DD/YYYY"

• "DD-MM-YYYY"

• "DD/MM/YYYY"

• "DD.MM.YYYY"

• "HH:NN:SS"

• "HHNNSS"

• "HH:NN:SS.S"

• "HH:NN:SS.SS"

• "HH:NN:SS.SSS"

• "HH:NN:SS.SSSS"

• "HH:NN:SS.SSSSS"

• "HH:NN:SS.SSSSSS"

• "YYYY-MM-DD HH:NN:SS"

• "YYYYMMDD HHNNSS"

• "YYYY-MM-DD HH:NN:SS.S"

• "YYYY-MM-DD HH:NN:SS.SS"

• "YYYY-MM-DD HH:NN:SS.SSS"

• "YYYY-MM-DD HH:NN:SS.SSSS"

• "YYYY-MM-DD HH:NN:SS.SSSSS"

• "YYYY-MM-DD HH:NN:SS.SSSSSS"

When you load a table having one or more date, time, or datetime columns and the input format is in one of the above formats, then the load can run significantly faster if you explicitly specify the appropriate format on the load statement. Otherwise, the load can run very slowly.

Suppose that your table had a date column, created as follows:

CREATE TABLE table1(c1 DATE);

To load the table, use a statement like this:

 LOAD TABLE table1 (c1 ASCII(10)) FROM ...

If the raw data format is in a format that has been optimized (such as YYYY-MM-DD), the load will be much faster.

The following sections describe the conversion options in greater detail.