Sybase IQ as a Data Warehousing Database Solution

Sybase IQ as a Data Warehousing Database Solution

 

There are a number of points to look at when deciding whether to use Sybase IQ as a database platform.  It is specialised the target audience is inherently limited by feature set.

 

Some background - Sybase IQ is the Sybase Data Warehouse product offering.  It is not like ASE or Oracle or MS SQL Server in that it has been designed from the ground up for analytic and not OLTP based applications.

 

Key Business benefits:

 

Ø       Scalability: can provide access for many simultaneous users to terabytes of information.

Ø       Speed: delivers query responses to users in seconds rather than minutes or hours.

Ø       Flexibility: gives users the ability to create any type of ad-hoc query.

Ø       Low TCO: reduces storage with data compression; compresses data from 50 to 80 percent; is far easier to maintain than traditional databases and does not require time and resource-intensive tuning to obtain excellent performance

 

Key technical advantages:

 

a)       Many times faster for both simple and complex selects (typically a factor of 100) as the optimiser takes more time so is more thorough.

b)       Storage efficiency - data is held in a compressed form so take up typically 1/3rd the space.

c)       All columns are indexed by default - other types of index can be added for additional performance.

d)       Uses less system resources so requiring smaller hardware configurations.

e)       Scales well to Terabytes.

f)        Large updates performed more efficiently (typically 100K row update <1second). 

g)       Can operate in a cluster mode by adding additional 'read only' nodes for scaling.

h)       Transact and ANSI SQL compatible.

i)         Can be fed data by Sybase Replication/CIS or File.

j)        Bulk load is very fast.

k)       Uses table versioning so no transaction log to fill up, in addition updates are queued so no locking issues.

l)         No read locking as ‘selects’ read current version.

m)     Inbuilt Index optimiser advises of indexes needed to enhance performance.

n)       Little tuning necessary and very forgiving of badly written queries.

o)       No index rebuilds or statistics maintenance.

p)       Internal event based scheduling.

 

Main disadvantages:

 

a)       It is not suitable for an OLTP environment.

b)       Query optimiser is more thorough and takes longer to compile queries so is not suitable where ultra quick response time is required.

c)       Optimised for last row out not first as on OLTP systems

d)       Inefficient updating small numbers of records due to large page size (1 record similar time to 10,000 records).

e)       Only one writer/updater process per table at the moment (all updaters on one node only).

f)        Is 99.9% compatible with Transact SQL so there is likely to be some reworking necessary to migrate ASE SQL.

 

Other Points:

 

Ø       Data is held in a column based format and no row based as is the case with conventional RDBMS’s.

Ø       DR is maintained by Database + either Transactional or Differential dumps.

Ø       Database dump time in the order of 200Gb+ per hour (Linux + SAN).

Ø      Many different types of indexes for precise data type support + will support time series in the next version.

Ø       Data load time in the order of 100k+ records per CPU per second.