Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
uberman
Posting Yak Master
159 Posts |
 Posted - 2007-09-07 : 06:38:33
|
| [url]http://www.databasecolumn.com/[/url]Think in columns!(Picked up from \.) |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-07 : 06:51:12
|
I had to look up what Column-orientation meant ... this from Vertica's website:quote:
Column-orientation. Since many database queries are “disk bound,” meaning D > C, the most obvious performance advantage of Vertica is that it only needs to read the two columns involved in the query from disk. In a traditional row-oriented database, the disk would need to scan over all five columns. This additional three columns worth of scanning can represent as much as a 250% slowdown in performance. Since most databases have many more than five columns (e.g., some fact tables have hundreds), the performance of a row-oriented database is often even worse. Other database vendors include support for materialized views and data cubes, which are data structures designed to optimize the performance of a few common queries. They work by reducing the need to scan over unused columns by partially pre-computing query answers into data structures stored on disk. Since these approaches are optimized for answering a few queries well, they are unable to provide comparable performance to Vertica across many different query workloads. Besides column-orientation, one of the major ways in which Vertica achieves its performance advantage is via aggressive column-oriented compression, which we describe next.
But I don't see how it helps because you need to find the relevant data for the three columns, now in separate places, that represent the rows in question. Also, for common-queries (rather than ad hoc stuff) these would presumably be "covered" by an index, which solves the problem of not having to retrieve the "whole row"Kristen |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-12 : 21:12:53
|
| Lol - a solution trying to find a problem. If it's not there talk gibberish to pretend it is.Note it's taking about dimensional databases which will have disc problems. And as they are meant for non-technical users probably don't have anyone to optimise them.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2007-09-28 : 21:39:03
|
| You need to look at the performance of SAND, WX4 (nee White Cross) and some other column-oriented RDBMS. Since a column is one data type, you can compress it easily, then do all of your searching on the compressed data in primary storage. Since each column is assembled into rows after the fetch, you can use parallel hardware. It really works!!--CELKO--Joe Celko, SQL Guru |
|
|
|
|
|
|
|
|
The Database Column