Optimizing Star Schemas

Hello All,
I was asked this question : " How will you go about optimizing Star Schemas
?". I understand data warehouses and understand what a Star Schema is.
However, I was not sure how to address this question.
Can someone point me in the right direction.
Thanks,
rgnHello Gopi,
I guess it might be good to qualify what you mean by "Optimizing Star
Schemas"
In my view there are two main points of Optimization
1. ETL: The process of populating the Star Schema.
2. Star Schema Query performance: The creation of reports and ad hoc
queries.
I am assuming you are really interested in Star Schema Query
performance. I am also assuming you are using surrogate keys. Here are
some basic rules.
1. Make sure all dimensions PK keys have a clustered index that is
used for joining to the fact table.
2. Make sure the Fact table has a good covering index for all the
corresponding dimension keys. A cluster index would be preferable.
3. Check the covering index is in the right order for most common
queries.
I would recommend that you look at using Analysis Services 2005 as your
main query engine. It will give you the best query performance by far.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Optimizing SQL Query performance

Hi,
I have collected SQL Trace events into a trace table by exporting the trace
into a table.
I have a table Trace1 with following columns:
-RowNumber
-ApplicationName
-DatabaseName
.
.
.
-StartTime
-EndTime
Clustered Index on RowNumber Column.
NonClustered Index on StartTime Column.
Trace1 table contains 100,000 of rows.
Now I am firing a query something like
"select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
But above query gives me timeout error for most of the cases. I have
specified timeout value as 60 seconds.
How can I solve this timeout issue?
Do I need to have some other proper indexes, if current indexes are not
proper?
Do I need to increase the timeout value? What is the optimum value of
Timeout in such scenarios? I am expecting that this table is going to grow
to contain atleast 5 crores of rows. So please suggest what strategy should
I adopt?
Thanks,
PushkarIf you have an index on StartTime, this should be the most efficient way to
retrieve the data. I would not expect 100,000 rows to take more than a
moment. Even without an index, I would expect the query to finish in
seconds.
Check your execution plan and see if it is using the index.
You could try regenerating your statistics on this table, which should get
it to use this index.
Post the full DDL of your table including the indexes themselves, just so we
are perfectly clear on what you have.
http://www.aspfaq.com/etiquette.asp?id=5006
I think the likely culprit here is the use of "Select * ". If this is a
very wide table, you may be timing out moving all of that data across the
network. Also, how many rows does your typical date range select? If you
usually return 90,000 rows, that makes a big difference. If every row
contains 1 meg of data (an extreme case, just to illustrate a point), for
example, that would be 90 gigs moving over the network, and would timeout
every time.
Also, what application are you using to run the query?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23aMdJOHaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have collected SQL Trace events into a trace table by exporting the
trace
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy
should
> I adopt?
> Thanks,
> Pushkar
>
>
>|||Pushkar,
It depends on the relative amount of rows that the query returns.
I would start with adding a (nonclustered) index on StartTime. If the
query returns just a few percent of all rows and tables rows are
relatively wide as compared to the StartTime column, then this index
will probably be used.
If the query returns more than a few percent, or the rows are narrow,
then a nonclustered index on StartTime might be ignored. If this query
is one of the most important queries in your system, then you could
create a clustered index on StartTime (you will need to change the
current clustered index to nonclustered).
HTH,
Gert-Jan
Pushkar wrote:
> Hi,
> I have collected SQL Trace events into a trace table by exporting the trac
e
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy shoul
d
> I adopt?
> Thanks,
> Pushkar

Optimizing SQL Query

Hello,
I have to run a script which will delete records from 10 tables
based on a certain condition. Instead of having to run the condition 10
times I thought running it once would have better performance and did
something like this
CREATE PROCEDURE testScript AS
set nocount on
declare @.script_table table (row_id int identity(1,1), sid int)
declare @.max int, @.now int, @.result varchar(100)
insert into @.script_table
select sid from Alpha where lname like 'DOWN' and (fname like 'Tom' or
fname like 'Seinfeld')
select @.max = max(row_id) from @.script_table
select @.now=2, @.result=convert(varchar(6), sid) from @.script_table
where row_id=1
while @.max >=@.now
Begin
select @.result = @.result + ',' + convert(varchar(6), sid) from
@.script_table where row_id=@.now
set @.now=@.now + 1;
End
select * from Beta where convert(varchar(5), sid) in ( @.result )
if @.@.error <> 0
BEGIN
ROLLBACK Transaction
RAISERROR ( 'E', 2, 127)
END
...
...
...
but when I run this I dont get any values. Instead when I run this
query I get the output rows
select * from Beta where convert(varchar(5), sid) in (select sid from
Alpha where lname like 'DOWN' and (fname like 'Tom' or fname like
'Seinfeld'))
since @.result has the output from Alpha with a comma delimiter I was
under the impression that this should give me the result but instead I
dont get any rows. Is this because @.result is a varchar? Am I doing
something wrong here? Any help would be great..
Thanks
Khris> Is this because @.result is a varchar?
Yes. You can, however, parse a delimited string and have values returned in
a table, that you can use in your query.
My favourite solution is this one (by Dejan Sarka):
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks ML, I will try that out|||Thanks ML I will try that out
Khris

Optimizing SQL Filtering - Conceptual question

I expose a simple example of what I'm trying to figure it out:
table1:
====
field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
Daniel E. Alvarez
IMS Soluciones Tecnolgicas S.A.
quilate@.kropol.com.ar
Daniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====
> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnolgicas S.A.
> quilate@.kropol.com.ar
>
sql

Optimizing SQL Filtering - Conceptual question

I expose a simple example of what I'm trying to figure it out:
table1:
====
field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
Daniel E. Alvarez
IMS Soluciones Tecnolgicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====
> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnolgicas S.A.
> quilate@.kropol.com.ar
>

Optimizing SQL Filtering - Conceptual question

I expose a simple example of what I'm trying to figure it out:
table1:
==== field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
--
Daniel E. Alvarez
IMS Soluciones Tecnológicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnológicas S.A.
> quilate@.kropol.com.ar
>

Optimizing SQL 2000 SP4 for large amount of memory

Hi
We will within the next week move our SQL 2000 server SP4 to a new high
performance server (2 x ghz XEON, 4 GB RAM)
The database which is to be running on the server has a physical size of 50
mb on disk.
Is there a way to optimize the SQL 2000 to keep as much as possible
in-memory so searching will as quick as possible? The read / write rate is
approx 1000/1 (we read 1000 x as often as writing)
Is the some memory settings we can tweak or are we better of letting sql
server 2000 handle it?
Thanks in regards
Anders JacobsenJust checking 50Mb not 50 Gb. If so then there's not much you can do, with
4Gb of RAM it will all reside in memory.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
> 50 mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
> Thanks in regards
> Anders Jacobsen
>|||"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
50
> mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
Generally you're better off letting SQL Server handle it. It will grab as
much RAM as it can (2GB with Standard, more with Enterprise with the proper
switches) and use it to cache.
Check perfmon and look at the cache hit ratio for one thing to see how
you're doing.
If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
> Thanks in regards
> Anders Jacobsen
>|||> Generally you're better off letting SQL Server handle it. It will grab as
> much RAM as it can (2GB with Standard, more with Enterprise with the
> proper
> switches) and use it to cache.
> Check perfmon and look at the cache hit ratio for one thing to see how
> you're doing.
> If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
Sounds great.
Thanks