|
Home > Archive > MS SQL Data Warehousing > September 2005 > sql performance when accessing star-schemas
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 performance when accessing star-schemas
|
|
| /jerome k 2005-09-08, 3:23 am |
| Hi,
We use SQL2000 starschemas with "innerjoin" between dim-tables och a
facttable.
select distinct country_descr, product_descr, sum(qty),sum(amount)
from facts_order,dim_prod
uct,dim_dates,dim_co
untry
where dim_dates.month=...
Our facttable has appr 4000' rows, dim_prod 10', dim_dates 1' and country 10
rows.
Is the performace (execution plan?) depending in wich order I declare the
tables and where-clauses ? If konverting to ANSI-syntax, will it change
execution plans ? Any ideas about indexing strategy ?
| |
| Jéjé 2005-09-09, 3:23 am |
| do you have 4000 rows? or 4 millions?
4 000 rows is really small and try to improve the performance is not
relevant in this case
for 4 millions...
always try to use JOINS (inner join, left join...)
the order is not important, but you can enforce it by adding "option(force
order)" at the end of the query.
you'll have to use a group by syntax...
select country_descr, product_descr, sum(qty),sum(amount)
from facts_order inner join dim_product on ....
inner join dim_dates on dim_dates.month=...
inner join dim_country on ....
where .....
GROUP BY country_descr, product_descr
for the indexes...
create a clustered index on the fact table based on all your foreign key
columns.
analyze your query with the query analyzer and the index tuning wizard to
identify missing indexes.
set the fill factor of your indexes to 100%
defragment your indexes (using a command like dbcc reindex)
if you have some disks available, spread your tables & indexes on different
sets of disks.
Insure that the tempdb database is on a separate disk(s)
if you have SQL Enterprise Edition, try to play with indexed views (can be
used in std edition, but SQL don't try to use this by default, and you have
to do this manually because the index tuning wizard will not try to create
this view for you.)
I hope this helps you.
"/jerome k" <jeromek@discussions.microsoft.com> wrote in message
news:8B9F0E3E-A171-46BE-9C6A- DC8C497BB1A5@microso
ft.com...
> Hi,
> We use SQL2000 starschemas with "innerjoin" between dim-tables och a
> facttable.
>
> select distinct country_descr, product_descr, sum(qty),sum(amount)
> from facts_order,dim_prod
uct,dim_dates,dim_co
untry
> where dim_dates.month=...
>
> Our facttable has appr 4000' rows, dim_prod 10', dim_dates 1' and country
> 10
> rows.
>
> Is the performace (execution plan?) depending in wich order I declare the
> tables and where-clauses ? If konverting to ANSI-syntax, will it change
> execution plans ? Any ideas about indexing strategy ?
| |
| /jerome k 2005-09-09, 3:23 am |
| Thanks ! Its 4 millions (growing fast) facttable-rows. I have 12 dim-tables.
- Should i create one clustered index with all my foreign keys like
product,country,cust
omer,date,discount_c
ode,.... ? Not many indexes like
(date,customer,count
ry), (date,product), (product,date)...
- "option(force order)": what order would be the best in this query when
having 4000000,10000,1000 and 10-row tables only filtered on one month. From
the biggest to the smallest ?
"Jéjé" wrote:
> do you have 4000 rows? or 4 millions?
> 4 000 rows is really small and try to improve the performance is not
> relevant in this case
>
> for 4 millions...
> always try to use JOINS (inner join, left join...)
>
> the order is not important, but you can enforce it by adding "option(force
> order)" at the end of the query.
>
> you'll have to use a group by syntax...
> select country_descr, product_descr, sum(qty),sum(amount)
> from facts_order inner join dim_product on ....
> inner join dim_dates on dim_dates.month=...
> inner join dim_country on ....
> where .....
> GROUP BY country_descr, product_descr
>
> for the indexes...
> create a clustered index on the fact table based on all your foreign key
> columns.
>
> analyze your query with the query analyzer and the index tuning wizard to
> identify missing indexes.
>
> set the fill factor of your indexes to 100%
> defragment your indexes (using a command like dbcc reindex)
>
> if you have some disks available, spread your tables & indexes on different
> sets of disks.
> Insure that the tempdb database is on a separate disk(s)
>
> if you have SQL Enterprise Edition, try to play with indexed views (can be
> used in std edition, but SQL don't try to use this by default, and you have
> to do this manually because the index tuning wizard will not try to create
> this view for you.)
>
> I hope this helps you.
>
> "/jerome k" <jeromek@discussions.microsoft.com> wrote in message
> news:8B9F0E3E-A171-46BE-9C6A- DC8C497BB1A5@microso
ft.com...
>
>
| |
|
| start to use option force order only if you suffer performance issues and if
the query optimizer display a bad plan. in the case of datamarts, generally
there is no issues around this, so don't start your optimization process
from here.
you can create 1 clustered index in the fact table with all the foreign keys
and 1 index for each "desc" column in your dimension tables.
(I presume that the dimension primary key column is a clustered index)
Play with the query analyzer and the estimated execution plan + the real
execution plan to identify any issue.
also, you can create a partitionned table.
If you have 1 millions of rows by year and you always ask for a specific
year, then a partition by year will help you.
this article can help you to optimize your indexes:
http://www.microsoft.com/technet/pr...art5/c2061.mspx
"/jerome k" <jeromek@discussions.microsoft.com> wrote in message
news:E76AC9D6-C424-4042-8CD4- 78693811A106@microso
ft.com...[color=darkred]
> Thanks ! Its 4 millions (growing fast) facttable-rows. I have 12
> dim-tables.
>
> - Should i create one clustered index with all my foreign keys like
> product,country,cust
omer,date,discount_c
ode,.... ? Not many indexes like
> (date,customer,count
ry), (date,product), (product,date)...
> - "option(force order)": what order would be the best in this query when
> having 4000000,10000,1000 and 10-row tables only filtered on one month.
> From
> the biggest to the smallest ?
>
> "Jéjé" wrote:
>
|
|
|
|
|