Performance question: Separate database or additional table?

We have a database that has about 50 tables, each with approximately 800K
rows. These tables are imports of data from another system that are updated
daily. All the tables have the same column, Id, for a primary key. The table
s
are accessed via views that serve mainly to assign meaningful names to the
columns and to insulate the applications from changes to the underlying
tables. In other words, the views are not restricting the users to a subset
of the columns in the tables, nor are they doing multi-table joins, etc. Thi
s
database is accessed by many users using many different applications across
the enterprise.
We have an application that queries a 12-view subset of the views, allowing
the user to do ad-hoc what-if queries with an application that generates SQL
queries from the user requests. The queries are often very time consuming. W
e
are attempting to improve the performance of this application. One thing we
know is that due to the nature of this application, 25% of the rows in the
views will never be selected and the application has to include SQL to
explicitly exlude this 25% of the rows.
One thought is to eliminate the scanning of the 25% of the rows that the
application never uses. There appear to be two ways of doing this:
1) Create another database containing only the 10-table/view subset and to
eliminate the unwanted rows at import/update time.
2) Create another table/view in the existing database with the Ids of the
75% of the rows that are used by this application and have the application d
o
a join on this table for all the queries and eliminate the SQL designed to
weed out the 25% of the rows that are not used by the application.
Whatever we choose to do, we have to live within the constraint of our
current hardware, so if we choose to create a separate database it will have
to share the current hardware with the other applications.
Given that the processing required to either load the alternate database or
maintain the 75% table is not an issue, which choice is likely to give us th
e
best increase in performance for the application?
Thanks,
BobHi
If you are currently seeing conflict between the two types of usage then it
is probably a good idea to create a second database for this to work on.
There will be a latency in the data unless depending on how you implement th
e
propogation of data.
I think you should look at the index usage on the database first, before you
implement any complex task to reduce the data.
John
"Bob" wrote:

> We have a database that has about 50 tables, each with approximately 800K
> rows. These tables are imports of data from another system that are update
d
> daily. All the tables have the same column, Id, for a primary key. The tab
les
> are accessed via views that serve mainly to assign meaningful names to the
> columns and to insulate the applications from changes to the underlying
> tables. In other words, the views are not restricting the users to a subse
t
> of the columns in the tables, nor are they doing multi-table joins, etc. T
his
> database is accessed by many users using many different applications acros
s
> the enterprise.
> We have an application that queries a 12-view subset of the views, allowin
g
> the user to do ad-hoc what-if queries with an application that generates S
QL
> queries from the user requests. The queries are often very time consuming.
We
> are attempting to improve the performance of this application. One thing w
e
> know is that due to the nature of this application, 25% of the rows in the
> views will never be selected and the application has to include SQL to
> explicitly exlude this 25% of the rows.
> One thought is to eliminate the scanning of the 25% of the rows that the
> application never uses. There appear to be two ways of doing this:
> 1) Create another database containing only the 10-table/view subset and to
> eliminate the unwanted rows at import/update time.
> 2) Create another table/view in the existing database with the Ids of the
> 75% of the rows that are used by this application and have the application
do
> a join on this table for all the queries and eliminate the SQL designed to
> weed out the 25% of the rows that are not used by the application.
> Whatever we choose to do, we have to live within the constraint of our
> current hardware, so if we choose to create a separate database it will ha
ve
> to share the current hardware with the other applications.
> Given that the processing required to either load the alternate database o
r
> maintain the 75% table is not an issue, which choice is likely to give us
the
> best increase in performance for the application?
> Thanks,
> Bob
>|||John,
"John Bell" wrote:

> Hi
> If you are currently seeing conflict between the two types of usage then
it
> is probably a good idea to create a second database for this to work on.
> There will be a latency in the data unless depending on how you implement
the
> propogation of data.
>
It's not really a conflict. The 75% part of the data represents currently
active items while the other 25% is effectively history. Items move from
current to history and new items are added, on a daily basis as the result o
f
batch processing on another system. This also eliminates data latency as an
issue.

> I think you should look at the index usage on the database first, before y
ou
> implement any complex task to reduce the data.
>
The column that determines whether an item is current or history is already
indexed. In addition, all the other columns used by the queries are also
indexed as appropriate.
Bob|||Assuming all other factors (indexing, joins, etc.) remain the same, I would
not expect removing 25% of the rows to impact the total runtime of the
queries that much. If 75% of the rows could be archived elsewhere, then that
would be significant. Rather than guessing, there are methods to know for
sure where the bottleneck is:
Investigate to what extent your indexes may be fragmented and defragment if
needed.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Use the Show Execution Plan feature in Query Analyzer to see exactly how the
query optimizer is using your indexes.
http://support.microsoft.com/defaul...;243589&sd=tech
SQL Server hardware configuration and monitoring memory usage, IO
performance, etc:
/url]
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx#EE
AA" target="_blank">http://www.microsoft.com/technet/pr...px#EE
AA
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:B41801C0-B505-4A2C-9E3B-CF4D5E7C5C9A@.microsoft.com...
> We have a database that has about 50 tables, each with approximately 800K
> rows. These tables are imports of data from another system that are
> updated
> daily. All the tables have the same column, Id, for a primary key. The
> tables
> are accessed via views that serve mainly to assign meaningful names to the
> columns and to insulate the applications from changes to the underlying
> tables. In other words, the views are not restricting the users to a
> subset
> of the columns in the tables, nor are they doing multi-table joins, etc.
> This
> database is accessed by many users using many different applications
> across
> the enterprise.
> We have an application that queries a 12-view subset of the views,
> allowing
> the user to do ad-hoc what-if queries with an application that generates
> SQL
> queries from the user requests. The queries are often very time consuming.
> We
> are attempting to improve the performance of this application. One thing
> we
> know is that due to the nature of this application, 25% of the rows in the
> views will never be selected and the application has to include SQL to
> explicitly exlude this 25% of the rows.
> One thought is to eliminate the scanning of the 25% of the rows that the
> application never uses. There appear to be two ways of doing this:
> 1) Create another database containing only the 10-table/view subset and to
> eliminate the unwanted rows at import/update time.
> 2) Create another table/view in the existing database with the Ids of the
> 75% of the rows that are used by this application and have the application
> do
> a join on this table for all the queries and eliminate the SQL designed to
> weed out the 25% of the rows that are not used by the application.
> Whatever we choose to do, we have to live within the constraint of our
> current hardware, so if we choose to create a separate database it will
> have
> to share the current hardware with the other applications.
> Given that the processing required to either load the alternate database
> or
> maintain the 75% table is not an issue, which choice is likely to give us
> the
> best increase in performance for the application?
> Thanks,
> Bob
>|||Hi
JT gave you a link regarding the query plans. Just because your archive flag
is indexed does not necessarily mean it is used or if there is a better inde
x
configuration. With ad-hoc queries you may not get brilliant query plans all
of the time, but you may be able to produce an indexing strategy that gives
very good responses most of the time. It could be that with changed indexes
there is no need to separate the system. If the system is only updated by a
batch job, then you can use a strategy where you drop the indexes before
inserting the new data and re-creating the indexes after. If you don't drop
the indexes they should be rebuilt after the bulk load anyhow.
You may want to read some of the article on
http://www.sql-server-performance.c...performance.asp regarding how
to improve performance.
One thing that I don't think has been mentioned is that you may gain some
benefit from having indexed views.
John
"Bob" wrote:

> John,
> "John Bell" wrote:
>
> It's not really a conflict. The 75% part of the data represents currently
> active items while the other 25% is effectively history. Items move from
> current to history and new items are added, on a daily basis as the result
of
> batch processing on another system. This also eliminates data latency as a
n
> issue.
>
> The column that determines whether an item is current or history is alread
y
> indexed. In addition, all the other columns used by the queries are also
> indexed as appropriate.
> Bob
>|||JT and John,
Thanks for your replies. One of the problems I had was trying to actually
grab some of the generated queries to check out the execution plan in Query
Analyzer.
I got one of the DBAs to do a trace on the processing and we grabbed a few
of the generated queries. We discovered that there was a view someone had
written that wasn't part of the standard load and was doing massive amounts
of calculations.
I'm going to write a procedure that will use that view to create another
table loaded with the results of the calculations that will run as part of
the nightly load. The view will then simply be returning data instead of
calculating the values over and over. This will change a series of repetitiv
e
compute intensive calculations into a simple inner join on a primary key.
Thanks again for everyone's help.
Bob

Performance question: Indexes on separate file group or dimension table on separate file?

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.
Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>
|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>
|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic

Performance question: Indexes on separate file group or dimension table on separate fi

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--sql

Performance Question

Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano Alves
Impossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:

> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>
|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>
|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic

Performance Question

Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano AlvesImpossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:

> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate t
he
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--