| It was summer of 2000 when I first learned about | | | | the cost of the new hardware maybe not worth the |
| Sybase IQ and its revolutionary column vector | | | | investment. In comparison; a standalone upgrade of |
| database technology. As a long time Sybase ASE and | | | | the database engine might be worth the effort. |
| Oracle DBA I was used to database engines that | | | | It further means that cheaper server hardware on |
| organize data in a row by row method. For quite | | | | Linux can be used to build Sybase IQ multiplex |
| some time I had difficulties to think in column terms | | | | systems that produce high end performance results |
| and not in row terms. | | | | on a slim budget. Due to its architecture there are no |
| A column vector database requires totally different | | | | added network constraints either. |
| methodology for performance and tuning efforts. | | | | 2. Multiple writer nodes in a single multiplex |
| Nothing is straight forward and the message that | | | | environment. |
| more data volume doesn't make a difference in the | | | | This is an enormous step forward. Previously a typical |
| query performance is not easy to understand. For | | | | Sybase IQ was build with one big server that acts as |
| example: A traditional database engine allows the | | | | the writer node and many smaller servers for the |
| usage of only one index per table in the same query. | | | | reader nodes. The thinking was to provide the best |
| Sybase IQ has no limits. If each column in the query | | | | hardware to the CPU intensive load jobs to minimize |
| requires a different index, it will use a different index. | | | | the load windows. The downside of this architecture |
| In fact, by default every column is an index. | | | | was that in a failure situation, one of the smaller |
| Getting my hands around the fact of having queries | | | | servers would take over the writer part and then |
| perform up to a 1000 times faster on Sybase IQ | | | | would be helplessly overwhelmed in case the writer |
| than on traditional row based RDBMS systems is no | | | | node couldn't be fixed in time for the next load. |
| easy matter either. Of course in an Oracle | | | | It is also economically not practical to devote |
| implementation with the OLAP technology similar | | | | high-end, expensive server hardware to a job that |
| results can be achieved. However, you are paying for | | | | only last for a fraction of a daily work load. Having |
| the underlying OLTP engine regardless if you're using | | | | multiple writer nodes solves this problem once and |
| it or not. Sybase IQ doesn't have this overhead. | | | | for all. |
| One of the key features of Sybase IQ is its data | | | | Utilizing all the available processing power in a |
| compression. I worked with Sybase IQ systems that | | | | multiplex environment ultimately leads to faster load |
| easily exceeded 80% compression ratio. Meanwhile, | | | | performance, which can be solved without upgrading |
| every database vendor introduced data compression | | | | the writer node server hardware over and over |
| into their database engines, but Sybase IQ is the | | | | again. |
| undisputed leader in the highest compression ratio of | | | | Also, another data load performance improvement is |
| them all. | | | | the new feature of loading data directly from clients. |
| I know that there are other data warehouse | | | | This means that data can be loaded from files using a |
| systems out there that are equally as fast as Sybase | | | | simple SQL statement instead of copying data files |
| IQ and some are even faster, but in this article I am | | | | onto a server and then using the bcp command. |
| focused on the Sybase IQ engine and its recent | | | | Of course there are other major improvements in |
| setting of a new benchmark record for TPC-H | | | | security, flexibility and integration support, but the |
| transactions. This record is all about saving money | | | | two improvements above are the major contributors |
| while providing blazing fast performance. | | | | to any cost savings or cost avoidance initiative a |
| OK, back to what's new in version 15 of Sybase IQ. | | | | business is taking on these days. |
| There are two major improvements in the new | | | | Sybase also improved their client apps to better |
| release that are worth mentioning. | | | | manage the engine, easier develop applications and |
| 1. The overall query performance was once again | | | | more effectively monitor the system. Once the |
| dramatically improved and yields in an average | | | | Achilles heel of Sybase, these tools are now very |
| 20%-50% performance gain, compared to the | | | | usable and mature. |
| previous Sybase IQ release. | | | | From a cost/performance point of view, Sybase IQ |
| What does this mean for your business? | | | | is a force to be reckoned with and due to its column |
| Analytical queries are typically CPU hungry monsters | | | | vector architecture there is no other major database |
| that can eat up your entire processing resources. | | | | engine in the market like it. To support Sybase's |
| Producing results faster means more queries will be | | | | strong performance in technology they also had their |
| processed in the same time window. | | | | best financial year ever in 2008 and the best quarter |
| It also means the hardware upgrade can be | | | | on record in Q1 2009. |
| postponed for a while. Considering that the | | | | I hope you enjoyed my brief introduction into |
| associated QA requirements to move an entire | | | | Sybase's data warehouse engine Sybase IQ and its |
| production system to a new hardware platform can | | | | latest version 15 features. |
| be a very expensive proposition and combined with | | | | |