|
Home > Archive > MS SQL Data Warehousing > July 2005 > SQL 2005 features table questions...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL 2005 features table questions...
|
|
|
| Hi,
I have read the SQL 2005 features comparison and I have some questions:
(http://www.microsoft.com/sql/2005/p...05features.mspx)
* what is "Star Query Optimization "?
* what is "Data warehousing"??? its a concept, not a technology, so what is this feature?
* what is the "auto tuning"?
thanks for your comments.
Jerome.
| |
| Peter Nolan 2005-06-21, 11:23 am |
| Jerome,
star query optimisation generally means that the optimiser is aware of star joins for dimensional models in the database and optimises the query to run more quickly....
These features have been added to all the major databases over the last 15 years......the various databases have implemented this in different ways to different levels of performance.
What SQL server will actually do is another guess......it's a kind of 'generic comment' and 'star join optimisation' was first introduced to sql server 7 though it didn't work very well back then...
Peter
www.peternolan.com
"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message news:epJIuOIdFHA.1504@TK2MSFTNGP15.phx.gbl...
Hi,
I have read the SQL 2005 features comparison and I have some questions:
(http://www.microsoft.com/sql/2005/p...05features.mspx)
* what is "Star Query Optimization "?
* what is "Data warehousing"??? its a concept, not a technology, so what is this feature?
* what is the "auto tuning"?
thanks for your comments.
Jerome.
| |
|
| thanks for the answer.
I presume that the query opiimizer identify when this feature is used or not
there is any way to validate if a query "really activate" this feature?
does the execution plan will display specific information to demonstrate the usage of this feature?
"Peter Nolan" <peter@peternolan.com> wrote in message news:e1tCZdndFHA.2776@TK2MSFTNGP10.phx.gbl...
Jerome,
star query optimisation generally means that the optimiser is aware of star joins for dimensional models in the database and optimises the query to run more quickly....
These features have been added to all the major databases over the last 15 years......the various databases have implemented this in different ways to different levels of performance.
What SQL server will actually do is another guess......it's a kind of 'generic comment' and 'star join optimisation' was first introduced to sql server 7 though it didn't work very well back then...
Peter
www.peternolan.com
"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message news:epJIuOIdFHA.1504@TK2MSFTNGP15.phx.gbl...
Hi,
I have read the SQL 2005 features comparison and I have some questions:
(http://www.microsoft.com/sql/2005/p...05features.mspx)
* what is "Star Query Optimization "?
* what is "Data warehousing"??? its a concept, not a technology, so what is this feature?
* what is the "auto tuning"?
thanks for your comments.
Jerome.
| |
| Peter Nolan 2005-07-08, 9:23 am |
| Hi Jerome,
yes. When you read the plan you will see the fact table(s) are the last
tables queried even though the fact table(s) are generally linked to
the dimensions....and the dimensions are not linked to each
other....you will see some form of cartesian product (or vectored join
if it is added to 2005, who knows) to combine the result sets of the
scans on the dimension tables prior to the fact tables being
accessed....
this ability to perform cartesian products or vectored joins is how
star joins are resolved more quickly than by merely joining two tables
together....
Peter Nolan
www.peternolan.com
| |
|
| so... snowflake schemas will not be optimized?
Like :
Country table -> Customer table -> Sales fact table
"Peter Nolan" <peter@peternolan.com> wrote in message
news:1120834310.880152.34920@g49g2000cwa.googlegroups.com...
> Hi Jerome,
> yes. When you read the plan you will see the fact table(s) are the last
> tables queried even though the fact table(s) are generally linked to
> the dimensions....and the dimensions are not linked to each
> other....you will see some form of cartesian product (or vectored join
> if it is added to 2005, who knows) to combine the result sets of the
> scans on the dimension tables prior to the fact tables being
> accessed....
>
> this ability to perform cartesian products or vectored joins is how
> star joins are resolved more quickly than by merely joining two tables
> together....
>
> Peter Nolan
> www.peternolan.com
>
| |
| Peter Nolan 2005-07-08, 11:23 am |
| Hi Jerome,
a good question for SQL Server......I don't know. Maybe guys with a
large DW on 2000 would like to answer?
Other databases (Oracle/ Sybase IQ) are able to understand and optimise
'to an extent' snowflaked schemas....and 'to what extent' can be very
variable......
It is variable enough that I strongly recommend against snowflaking
where it can be avoided....it saves you nothing and costs you
performance if you can avoid it......IQ is one of the few where
snowflaking really does not hurt you that much but on the other hand in
IQ you gain nothing, not even a bit of disk, by snowflaking so we
almost never do it on IQ...
If you don't snowflake you don't have to worry about what the optimiser
might do with it.. ;-)
Best Regards
Peter Nolan
www.peternolan.com
|
|
|
|
|