Central vs separated databases

What you have described is a very small database these days.
But, if you are going to grow it by over 200 tables, in 1 year,
it sounds like you have a design problem. Nothing you
have described needs 200 tablesto accomplish properly.
Robert
On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
> Now +- 1GB.
>
> And what if you want a base table for common thing like name,
> address,nationality... or a postal code table (wich will be used in
> more than one database).
> Maybe yours isn't a good exapmle but what about following situation:
> Employee database, intranet database, room reservation (for employees
> and others),...
> People can be in all of the databases.
> So shouldn't it be better in this situation to create a general
> database for the table people (and use a trigger to check te
> integrity).- Hide quoted text -
> - Show quoted text -
On 8 nov, 20:03, raibeart <raibe...@.gmail.com> wrote:
> What you have described is a very small database these days.
> But, if you are going to grow it by over 200 tables, in 1 year,
> it sounds like you have a design problem. Nothing you
> have described needs 200 tablesto accomplish properly.
> Robert
> On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
>
>
>
>
>
>
> - Tekst uit oorspronkelijk bericht weergeven -
Because this are only a few of the project for the comming 5 years.

Central vs separated databases

Hi,
I'm making a study about pro en contra for central and separated
databases in one sql server.
I googled and found some interesting opinions but most of the are
about management.
But what about performance, what if a database has 100-200 tables and
50 views and keeps growing.
Can Sql server handle this?
What about restore?
What about (I'm missing)...
Any help or a good link is welcome.Performance is not generally a function of the number of tables or
views. With a good database design the limiting factors are more the
number of concurrent users and transactions, as well as table size and
database size.
100-200 tables with 50 views, those numbers are not a challenge at
all. How any gigabytes of data do you anticipate? How many users? Is
it transaction processing or reporting?
Roy Harvey
Beacon Falls, CT
On Wed, 31 Oct 2007 03:20:33 -0700, HDI <hdinf@.hotmail.com> wrote:
>Hi,
>I'm making a study about pro en contra for central and separated
>databases in one sql server.
>I googled and found some interesting opinions but most of the are
>about management.
>But what about performance, what if a database has 100-200 tables and
>50 views and keeps growing.
>Can Sql server handle this?
>What about restore?
>What about (I'm missing)...
>Any help or a good link is welcome.|||On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> Performance is not generally a function of the number of tables or
> views. With a good database design the limiting factors are more the
> number of concurrent users and transactions, as well as table size and
> database size.
> 100-200 tables with 50 views, those numbers are not a challenge at
> all. How any gigabytes of data do you anticipate? How many users? Is
> it transaction processing or reporting?
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 31 Oct 2007 03:20:33 -0700, HDI <hd...@.hotmail.com> wrote:
> >Hi,
> >I'm making a study about pro en contra for central and separated
> >databases in one sql server.
> >I googled and found some interesting opinions but most of the are
> >about management.
> >But what about performance, what if a database has 100-200 tables and
> >50 views and keeps growing.
> >Can Sql server handle this?
> >What about restore?
> >What about (I'm missing)...
> >Any help or a good link is welcome.- Tekst uit oorspronkelijk bericht niet weergeven -
> - Tekst uit oorspronkelijk bericht weergeven -
It's should be processing and reporting.
The users are now 50 but keeps growing like all the rest of course.
100-200 tables now are 200-300 tables next year ...|||On Mon, 05 Nov 2007 04:12:50 -0800, HDI <hdinf@.hotmail.com> wrote:
>On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
>wrote:
>> 100-200 tables with 50 views, those numbers are not a challenge at
>> all. How any gigabytes of data do you anticipate? How many users? Is
>> it transaction processing or reporting?
>It's should be processing and reporting.
>The users are now 50 but keeps growing like all the rest of course.
>100-200 tables now are 200-300 tables next year ...
You still did not provide the size estimate (gigabytes).
From what you have mentioned so far there is no reason related to size
or performance that would suggest needing more than a single database.
The one really good reason for considering multiple databases that
always applies regardless of how small the set of data or the number
of users is that the data is unrelated. The company's internal
phonebook probably should not be in the same database as the accounts
payable.
Roy Harvey
Beacon Falls, CT|||On 5 nov, 17:42, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> On Mon, 05 Nov 2007 04:12:50 -0800, HDI <hd...@.hotmail.com> wrote:
> >On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
> >wrote:
> >> 100-200 tables with 50 views, those numbers are not a challenge at
> >> all. How any gigabytes of data do you anticipate? How many users? Is
> >> it transaction processing or reporting?
> >It's should be processing and reporting.
> >The users are now 50 but keeps growing like all the rest of course.
> >100-200 tables now are 200-300 tables next year ...
> You still did not provide the size estimate (gigabytes).
> From what you have mentioned so far there is no reason related to size
> or performance that would suggest needing more than a single database.
> The one really good reason for considering multiple databases that
> always applies regardless of how small the set of data or the number
> of users is that the data is unrelated. The company's internal
> phonebook probably should not be in the same database as the accounts
> payable.
> Roy Harvey
> Beacon Falls, CT
Now +- 1GB.
>>The company's internal phonebook probably should not be in the same database as the accounts payable.
And what if you want a base table for common thing like name,
address,nationality... or a postal code table (wich will be used in
more than one database).
Maybe yours isn't a good exapmle but what about following situation:
Employee database, intranet database, room reservation (for employees
and others),...
People can be in all of the databases.
So shouldn't it be better in this situation to create a general
database for the table people (and use a trigger to check te
integrity).|||On Tue, 06 Nov 2007 06:30:43 -0800, HDI <hdinf@.hotmail.com> wrote:
>Now +- 1GB.
>>The company's internal phonebook probably should not be in the same database as the accounts payable.
>And what if you want a base table for common thing like name,
>address,nationality... or a postal code table (wich will be used in
>more than one database).
>Maybe yours isn't a good exapmle but what about following situation:
>Employee database, intranet database, room reservation (for employees
>and others),...
>People can be in all of the databases.
>So shouldn't it be better in this situation to create a general
>database for the table people (and use a trigger to check te
>integrity).
1 GB is small. These days 50GB is not large. You need not consider
multiple databases because of size.
If you are going to have the same entities for multiple applications
then integrity should be enforced through foreign key constrains, and
that requires that they be in the same database. So yes, if
applications are going to be integrated to that degree that justifies
keeping them in a single database.
Roy Harvey
Beacon Falls, CT|||On Tue, 06 Nov 2007 06:30:43 -0800, HDI wrote:
(snip)
>Maybe yours isn't a good exapmle but what about following situation:
>Employee database, intranet database, room reservation (for employees
>and others),...
>People can be in all of the databases.
>So shouldn't it be better in this situation to create a general
>database for the table people (and use a trigger to check te
>integrity).
Hi HDI,
Employee data, intranet, and room reservations are all part of managing
the internals of a company. I would never spread these tables out over
multiple databases.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||What you have described is a very small database these days.
But, if you are going to grow it by over 200 tables, in 1 year,
it sounds like you have a design problem. Nothing you
have described needs 200 tablesto accomplish properly.
Robert
On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
> Now +- 1GB.
> >>The company's internal phonebook probably should not be in the same database as the accounts payable.
> And what if you want a base table for common thing like name,
> address,nationality... or a postal code table (wich will be used in
> more than one database).
> Maybe yours isn't a good exapmle but what about following situation:
> Employee database, intranet database, room reservation (for employees
> and others),...
> People can be in all of the databases.
> So shouldn't it be better in this situation to create a general
> database for the table people (and use a trigger to check te
> integrity).- Hide quoted text -
> - Show quoted text -|||On 8 nov, 20:03, raibeart <raibe...@.gmail.com> wrote:
> What you have described is a very small database these days.
> But, if you are going to grow it by over 200 tables, in 1 year,
> it sounds like you have a design problem. Nothing you
> have described needs 200 tablesto accomplish properly.
> Robert
> On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
>
>
> > Now +- 1GB.
> > >>The company's internal phonebook probably should not be in the same database as the accounts payable.
> > And what if you want a base table for common thing like name,
> > address,nationality... or a postal code table (wich will be used in
> > more than one database).
> > Maybe yours isn't a good exapmle but what about following situation:
> > Employee database, intranet database, room reservation (for employees
> > and others),...
> > People can be in all of the databases.
> > So shouldn't it be better in this situation to create a general
> > database for the table people (and use a trigger to check te
> > integrity).- Hide quoted text -
> > - Show quoted text -- Tekst uit oorspronkelijk bericht niet weergeven -
> - Tekst uit oorspronkelijk bericht weergeven -
Because this are only a few of the project for the comming 5 years.

Central Subscriber with updates to subscriber possible?

I am trying to set up a central subscriber topology (seven small databases
replicating into one large database). The requirement I am having trouble
with is that updates need to be made to the central subscriber and get
replicated back to the publisher DB they belong to.
This is why this topology is needed: The company sells training courses,
both online and through regional sales offices. The seven publisher
databases are located in the regional sales offices, and are updated through
a local application by the staff taking course registrations in their
office. The central subscriber is located in the corporate headquarters, and
drives the website where customers can register online and update their
contact info. Contact info updates and registrations made online need to be
replicated back to one or more of the regional office databases. The rules
for which regional DB to update as basically this:
1. Customer records need to get replicated to the regional database that
manages the geographical area containing the customer's address (can be
determined by State and Country) AND to any other regional databases where
the customer has registered for a course (the courses are held at the
regional offices).
2. Registration records need to get replicated to the regional databases
where the course registered for is held.
My biggest problem is that only merge replication seems to be able to handle
the logic about passing updates back to the regional databases, however
merge replication does not support the central subscriber topology, only
central publisher. I am very wary of trying to create a central publisher
topology out of the current setup (i.e. create a central merged DB, drop the
regional DBs and recreate them) - 90% of business activity happens in the
regional offices and it seems too risky to put the regional DBs at the
effect of the central one.
Is there any solution to this?
I think you will find that merge replication will work well for this. It is
a common deployment setup. Look at dynamic snapshots to redeploy the branch
office data, or separate publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:efsiLA0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> I am trying to set up a central subscriber topology (seven small databases
> replicating into one large database). The requirement I am having trouble
> with is that updates need to be made to the central subscriber and get
> replicated back to the publisher DB they belong to.
> This is why this topology is needed: The company sells training courses,
> both online and through regional sales offices. The seven publisher
> databases are located in the regional sales offices, and are updated
through
> a local application by the staff taking course registrations in their
> office. The central subscriber is located in the corporate headquarters,
and
> drives the website where customers can register online and update their
> contact info. Contact info updates and registrations made online need to
be
> replicated back to one or more of the regional office databases. The rules
> for which regional DB to update as basically this:
> 1. Customer records need to get replicated to the regional database that
> manages the geographical area containing the customer's address (can be
> determined by State and Country) AND to any other regional databases where
> the customer has registered for a course (the courses are held at the
> regional offices).
> 2. Registration records need to get replicated to the regional databases
> where the course registered for is held.
> My biggest problem is that only merge replication seems to be able to
handle
> the logic about passing updates back to the regional databases, however
> merge replication does not support the central subscriber topology, only
> central publisher. I am very wary of trying to create a central publisher
> topology out of the current setup (i.e. create a central merged DB, drop
the
> regional DBs and recreate them) - 90% of business activity happens in the
> regional offices and it seems too risky to put the regional DBs at the
> effect of the central one.
> Is there any solution to this?
>
|||Thanks for your reply. When you say "Look at dynamic snapshots to redeploy
the branch office data, or separate publications." it sounds like I have to
stop using the regional databases as the publishers, drop them and recreate
them as subscribers to the central database. Is that right and is there any
way to avoid dropping and recreating the regional databases? I am trying to
avoid any impact to the regional databases.
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eHh36D4pFHA.248@.TK2MSFTNGP14.phx.gbl...
>I think you will find that merge replication will work well for this. It is
> a common deployment setup. Look at dynamic snapshots to redeploy the
> branch
> office data, or separate publications.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:efsiLA0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> through
> and
> be
> handle
> the
>

central subscriber

A central subscriber whose table is updated by multiple publishers and the
transactional push replication breaks on one of the publishers.
Does
a. If you do a new snapshot for that publisher does it automatically delete
the existing data in that table for that publisher assuming you have the
proper constraints in place for a unique column and then transactional
replication procedes as normal after the snapshot?
-or-
b. You have to wipe out the whole table and redo all the snapshots, i.e
reinitialize all the subscriptions.
-or-
c. something else
Any hard data in BOL,white papers, or other references to support either of
the possibilities?
a) But only if you user filters, and set up your name conflicts correctly.
To do this, right click on your publication, select properties, click on the articles tab, click on the browse button (the three dots) and then click on the snapshot tab. In the name conflicts section, pick delete all data that matches the row filter.
For Filters click on your Filter Commands tab, and add a filter condition. You might have to make changes to your schema to add a column which uniquely identifies your publisher.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

central place for stored procedure variables

Hello,
I have numerous stored procedures with the following. Every time I make a
change I have to update all of my stored procedures. Is there a more
efficient way of doing this?
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
Thanks in advance,
sck10Why don't you put this data in a table?
David Portas
SQL Server MVP
--|||Depending on your overall system architecture, security requirements, system
complexity and maintenance provisions, you can either have the paths
represented as values in a table within the database, or an external config
file, or in rare instances in a registry or in .ini files etc.
Anith|||sck10 wrote:
> Hello,
> I have numerous stored procedures with the following. Every time I
> make a change I have to update all of my stored procedures. Is there
> a more efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx
> varchar(300), @.strPhoto_home varchar(300),
> @.strPhoto_aspx varchar(300), @.strRightArrow_home varchar(300),
> @.strRightArrow_aspx varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img
> src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif" width="../../8"
> height="../../5">'
Put the HTML snippets in a table and write a function to access the
table by string ID. For example:
Create Table HTMLSnippet (
HTMLID CHAR(15) NOT NULL PRIMARY KEY,
HTMLSnip VARCHAR(100),
HTMLDesc VARCHAR(255))
-- insert values into table
Insert Into HTMLSnippet Values ('DOCPATHHOME, '<a href="http://links.10026.com/?link=web_doc/', 'The
Doc path home')
-- etc
Create Function dbo.GetHTMLSnippet (
@.HTMLID CHAR(15) )
RETURNS VARCHAR(100)
AS
DECLARE @.HTMLCode VARCHAR(100)
SELECT @.HTMLCode = HTMLCode From HTMLSnippet Where @.HTMLID = @.HTMLID
RETURN @.HTMLCode
-- to use function
Declare @.test varchar(1000)
Select @.test = dbo.GetHTMLSnippet('DOCPATHHOME') + ...
Not tested, but should give you a good start.
David Gugick
Imceda Software
www.imceda.com|||I agree with David, put it into a table and then SELECT the values in your
SP's. Then if they change you only have to change them in the table on
time, all your SP's will be able to use the updated info appropriately...
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>|||One more vote for using SQL Server to store data! Alternatively you might
consider building something (or finding something) to do macro's for SQL
code to have this automatically done for you (it might be slightly faster to
do what you have done rather than a table, if you have ultra-high
performance needs:
For example in your code put:
--<start macro replace-name_of_macro>
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
--<end macro replace-name_of_macro>
Now it would be easy to go through all of the files with stored procedures
in them and do a find and replace for this stuff. I would probably
suggest just using a table and cross joining to it whenever you need these
values in a few cases to try first. In the end it will be a far better
solution than putting data into variables. SQL Server does not now, and
forseeable future will not have constants like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>

Central Database Design

SQL SERVER 2K
We have 30 databases each for one business units.
Now I am consolidating few tables from 30 databases to one database for
Analysis purpose.
Now my tables in Central db have millions of records.
I need some tips to handle this huge tables for query purpose (already
applied with all Indexes).
Can I go with INDEXED VIEWS '
Thx
ShIndexed views are only really good for aggregations and joining data from
mutiple tables, is this what you need to do? Several of our tables are
over a million rows in our production environment and it all comes down to
indexing. Did you create new indexes designed for queries or are they the
existing indexes from the original tables?
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> SQL SERVER 2K
> We have 30 databases each for one business units.
> Now I am consolidating few tables from 30 databases to one database for
> Analysis purpose.
> Now my tables in Central db have millions of records.
> I need some tips to handle this huge tables for query purpose (already
> applied with all Indexes).
> Can I go with INDEXED VIEWS '
> Thx
> Sh
>|||I am sorry, I meant PARTITIONED VIEWS
Sh
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> Indexed views are only really good for aggregations and joining data from
> mutiple tables, is this what you need to do? Several of our tables are
> over a million rows in our production environment and it all comes down to
> indexing. Did you create new indexes designed for queries or are they the
> existing indexes from the original tables?
>
> "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > SQL SERVER 2K
> >
> > We have 30 databases each for one business units.
> > Now I am consolidating few tables from 30 databases to one database for
> > Analysis purpose.
> >
> > Now my tables in Central db have millions of records.
> > I need some tips to handle this huge tables for query purpose (already
> > applied with all Indexes).
> >
> > Can I go with INDEXED VIEWS '
> >
> > Thx
> > Sh
> >
> >
>|||Partition views can help here. You will be able to keep table sizes down
and this can increase query times when done right. It sounds like you moved
multiple tables into one table. If this is the case you will need to break
them back out into smaller tables, add partitioning columns, modify Primary
Keys and build the view. That might be a hassle. I can query a 20million
record table and get a response back in under a second, I just did a query
on 250million and elasped time was 10sec(only one index on there, I know I
could get quicker:)). Did you change indexes for reporting or not?
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#dGAN7EYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> I am sorry, I meant PARTITIONED VIEWS
> Sh
> "Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
> news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> > Indexed views are only really good for aggregations and joining data
from
> > mutiple tables, is this what you need to do? Several of our tables
are
> > over a million rows in our production environment and it all comes down
to
> > indexing. Did you create new indexes designed for queries or are they
the
> > existing indexes from the original tables?
> >
> >
> > "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> > news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > > SQL SERVER 2K
> > >
> > > We have 30 databases each for one business units.
> > > Now I am consolidating few tables from 30 databases to one database
for
> > > Analysis purpose.
> > >
> > > Now my tables in Central db have millions of records.
> > > I need some tips to handle this huge tables for query purpose (already
> > > applied with all Indexes).
> > >
> > > Can I go with INDEXED VIEWS '
> > >
> > > Thx
> > > Sh
> > >
> > >
> >
> >
>|||Thanks for the suggestions.
Sh
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OitSnTFYDHA.2032@.TK2MSFTNGP10.phx.gbl...
> Partition views can help here. You will be able to keep table sizes down
> and this can increase query times when done right. It sounds like you
moved
> multiple tables into one table. If this is the case you will need to
break
> them back out into smaller tables, add partitioning columns, modify
Primary
> Keys and build the view. That might be a hassle. I can query a 20million
> record table and get a response back in under a second, I just did a query
> on 250million and elasped time was 10sec(only one index on there, I know I
> could get quicker:)). Did you change indexes for reporting or not?
>
> "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> news:#dGAN7EYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> > I am sorry, I meant PARTITIONED VIEWS
> >
> > Sh
> >
> > "Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
> > news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> > > Indexed views are only really good for aggregations and joining data
> from
> > > mutiple tables, is this what you need to do? Several of our tables
> are
> > > over a million rows in our production environment and it all comes
down
> to
> > > indexing. Did you create new indexes designed for queries or are they
> the
> > > existing indexes from the original tables?
> > >
> > >
> > > "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> > > news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > > > SQL SERVER 2K
> > > >
> > > > We have 30 databases each for one business units.
> > > > Now I am consolidating few tables from 30 databases to one database
> for
> > > > Analysis purpose.
> > > >
> > > > Now my tables in Central db have millions of records.
> > > > I need some tips to handle this huge tables for query purpose
(already
> > > > applied with all Indexes).
> > > >
> > > > Can I go with INDEXED VIEWS '
> > > >
> > > > Thx
> > > > Sh
> > > >
> > > >
> > >
> > >
> >
> >
>