Sybase IQ utilizes column-based storage, thus allowing it to perform narrow scans on only the required columns or vectors required for the query. This results in 1/100 the less scanning of a typical database, and 20-100X better performance. In addition, every column is part of an index structure, and disk storage required is vastly reduced at the same time.
The Sybase IQ Paradox
Out of the box, with minimum configuration changes (default memory settings), you
will experience superior performance.
- Compression is on, by default; a 10 GB table (size in another database) will take 4 GB or less in IQ
- All columns are indexed by default, in a structure optimized for projection queries
The Paradox: With compression on, and no indexes, vast improvements in query performance can be experienced.
Critical: What you need to know about Sybase IQ
Sybase IQ is not for OLTP
Traditional inserts/updates/deletes are allowed, but should not be considered business as usual
Case 1 .. GOOD: Allowing an administrator to occasionally add new products records, for an online retailer
Case 2 .. BAD: Several hundred users are updating ticket reservations via a client-server application
Also - for those developers who run "select *" a lot: if the table has 80 or more columns, you might see decreased performance. Limit your result set to be meaningful. As Sybase IQ is column-base, there is a lot of overhead in running "select *" on a table with lots of columns.
Is it complicated to get started, once the server is installed?
There is little or no learning curve, skills on other RDBMs transfer quite easily
1) Create tables (data types are those similar to Sybase / SQL Server)
2) Load the tables (use the LOAD TABLE command, which is very easy, similar to BCP or SQL-Loader)
3) Run any ANSII or T-SQL query, to see the difference
4) Data is instantly accessible, via Business Objects, Cognos, Crystal Reports, JDBC apps, ODBC apps
Administration
- No different to any other standard RDBMS
- Standard SQL, commands, functions
- Adding new data/columns/indexes is TRIVIAL and FAST; columns can also be dropped or renamed
- Does not interfere with existing data and query development
- Test and develop on live data - safely
- Server-level INDEX_ADVISOR option allows engine to suggest indexes where needed
Data Types
- Data types are the same as for Sybase ASE (and SQL Anywhere)
- integer, smallint, tinyint, decimal, numeric, double, float, real, bigint
- date, smalldatetime, datetime, time, timestamp
- char, varchar, text, clob
- varchar has max of 32,000 chars.
- tables can have up to 16000 columns!
Main Index Types
- HG / high_group more than 1000 distinct values enhanced b-tree index
- HNG / high_non_group data ranges, high cardinality bitmap index (non value based)
- LF / low_fast less than 1000 distinct values bitmap index
- WD character strings keywords are indexed
WD Index Example
A powerful document search facility could be developed using this type of index.
select doc_name, doc_dt from library_books where book_content contains ('Lincoln')
Loading / Exporting
You can truncate and load table - without affecting users or reports!
e.g.
begin transaction
go
truncate table invoices
go
load table .. (see topic for syntax)
go
commit transaction
go
|
|
