Database for datawarehouse

Hi,
Does anyone have any information on comparison between different databases
for datawarehousing? I am working on a study to develop a datawarehousing
solution for my client, so I would like to get some information on
competitive aspects of SQL Server 2005 over DB2 and Oracle.
Thanks all~
SQL 2005 offer a better package then the others. (at a lower price then the
2 others)
In 1 product you have a database engine, OLAP engine, data mining engine,
report engine and ETL engine.
Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
(http://www.olapreport.com/market.htm)
From a performance point of view the 3 RDBMS servers are near identical
(www.tpc.org / TCP-H benchmark)
From a 3rd party support Microsoft is better, you'll found a lot of tools &
product compatible with AS 2000/2005 & SQL 2000/2005.
But you'll found a lot of articles which push one of these vendors...
You have to setup a complete list of needs & requirements of what your
client want.
But from my experience, this doesn't really helps you in your process.
Today the internal expertise & knowledge of a particular database/technology
is most important, if you know and allready use the product you are able to
provide better & faster results.
Also... get the demo versions of the tools or ask the vendors for a proof
of concept.
if you are really neutral, let the sales rep. to do their jobs.
"Stella" <stellajst@.hotmail.com> wrote in message
news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anyone have any information on comparison between different databases
> for datawarehousing? I am working on a study to develop a datawarehousing
> solution for my client, so I would like to get some information on
> competitive aspects of SQL Server 2005 over DB2 and Oracle.
> Thanks all~
>
|||There is a technical paper with detailed comparison between Oracle and
SQL Server from wisdomforce site:
http://wisdomforce.com/dweb/resource...0g_compare.pdf
As far as I remember it contains a lot of OLAP info
J=E9j=E9 wrote:
> SQL 2005 offer a better package then the others. (at a lower price then t=
he
> 2 others)
> In 1 product you have a database engine, OLAP engine, data mining engine,
> report engine and ETL engine.
> Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
> (http://www.olapreport.com/market.htm)
> From a performance point of view the 3 RDBMS servers are near identical
> (www.tpc.org / TCP-H benchmark)
> From a 3rd party support Microsoft is better, you'll found a lot of tools=
&
> product compatible with AS 2000/2005 & SQL 2000/2005.
> But you'll found a lot of articles which push one of these vendors...
> You have to setup a complete list of needs & requirements of what your
> client want.
> But from my experience, this doesn't really helps you in your process.
> Today the internal expertise & knowledge of a particular database/technol=
ogy
> is most important, if you know and allready use the product you are able =
to[vbcol=seagreen]
> provide better & faster results.
> Also... get the demo versions of the tools or ask the vendors for a proof
> of concept.
> if you are really neutral, let the sales rep. to do their jobs.
>
> "Stella" <stellajst@.hotmail.com> wrote in message
> news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
ses[vbcol=seagreen]
ing[vbcol=seagreen]
|||Jeje,
"SQL 2005 offer a better package then the others. (at a lower price
then the 2 others) In 1 product you have a database engine, OLAP
engine, data mining engine, report engine and ETL engine."
In your opinion... ;-)
Yes, SQL Server is pretty good, and I've read up on 2005 and it's
pretty good too....I've used the beta and it seems to do what the book
says...
But I recently did a project where the design points were 10,000 users,
180M inserts per day and 15TB of disk.....and there is no question SQL
Server has no references at that level and therefore would not make the
cut under the 'proven technology' selection criteria.
So, which database is 'better' is best prefixed by asking the question,
what are you thinking of doing with it, and Stella has not provided
such information apart from 'data warehousing'...';-)
Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
hardly go wrong with any of these three...
Best Regards
Peter
|||Thanks all for your help!
To be more specific, I'm actually looking for a comparison between databases
of 5TB to 8TB in size, running on Linux or Windows. Would anyone have ever
come across any cases of data-warehousing with these criteria?
Thanks~
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1131641995.382364.174340@.z14g2000cwz.googlegr oups.com...
> Jeje,
> "SQL 2005 offer a better package then the others. (at a lower price
> then the 2 others) In 1 product you have a database engine, OLAP
> engine, data mining engine, report engine and ETL engine."
> In your opinion... ;-)
> Yes, SQL Server is pretty good, and I've read up on 2005 and it's
> pretty good too....I've used the beta and it seems to do what the book
> says...
> But I recently did a project where the design points were 10,000 users,
> 180M inserts per day and 15TB of disk.....and there is no question SQL
> Server has no references at that level and therefore would not make the
> cut under the 'proven technology' selection criteria.
> So, which database is 'better' is best prefixed by asking the question,
> what are you thinking of doing with it, and Stella has not provided
> such information apart from 'data warehousing'...';-)
> Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
> hardly go wrong with any of these three...
> Best Regards
> Peter
>
|||Hi Stella,
the major metrics to provide are...
1. Volume of source data because each of the databases expands data by
a specific amount and most of them now also support varying levels of
compression. 5 to 8 TB means different sizes on different databases.
2. Number of rows in large tables.
3. Number of users.
As the size, complexity and number of users goes up
Oracle/DB2/Teradata/Sybase IQ come more into their own over MySQL and
SQL Server. I think you will find few examples of 5-8TB databases
running on SQL Server and not that many on windows/oracle. For
DB2/oracle they are commonplace.
For IQ there are almost none because you get compression in IQ.....so
something that is 8TB disk in Oracle is about 800GB disk in IQ. IQ now
holds more of the top 10 records than any other database...amazingly it
is a very well kept secret of out friends at Sybase.
I think it is unrealistic to think that as a consulting provider to a
client you would be given the benefit of years of experience in using
these databases across many projects in short appends on a newsgroup.
Long term experience across a range of databases provides a set of hard
won skills which are of great value to clients should they want someone
to assist in the database decision.
You can ask the vendors. Each vendor produces detailed papers for their
database.
Each of MySQL, SQL Server, Oracle, DB2, Teradata, Sybase IQ have their
strenghts and weaknesses. No vendor is going to tell you anything about
their weaknesses and vendors (and biased consultants) spread plenty of
mis-information and out of date information about competitive products.
For SQL Server (we are on a MSFT newsgroup) MSFT has done an excellent
job of documenting project REAL.
http://www.microsoft.com/sql/solutio...ojectreal.mspx
This is one of the most open presentations of a BI effort any of the
vendors has produced. MSFT are to be congratulated on their open-ness
on this one!!
My FAQs page discusses some of the considerations.
http://www.peternolan.com/FAQs/tabid/136/Default.aspx but it is only
intended to provide advice for smaller clients. Larger clients are
expected to have staff to advise on these decisions or pay for advice
on these decisions. I have been paid by clients to prove that the
database they wanted to use would work for what they wanted to do with
it.
Having said all that...
Many people consider what database to use as setp 1 of a BI effort. It
is one of the most ineffective places to start. It is far better to
start with what business the business is in and to design and build a
working prototype before deciding on what database to use.
It is now possible to build large scale prototypes and to have the the
ETL completely transportable from one database to the other with only
the cost of converting the table definitions.
Of course, none of the database vendors will tell anyone that it is
possible to migrate a DW between databases with trivial effort!! It is
not in their interests. And MSFT/Oracle have tied their ETL tools
tightly to the database so if you write your ETL in one of these two
ETL products you will live with the database forever.
So...It is now possible to postpone the 'which database' decision
until very late in the process, which also means it is possible to
postpone the database license payment until late in the process.
Indeed, in some clients we have built the large scale prototype on 2
competing databases so that we could really compare apples with apples.
No amount of 'competitive information' is equal to actually running two
databases side by side, latest release, with the vendors competing for
the business. The mix of HW/OS/RDBMS/DISK and versions of all these
makes it pretty much impossible to compare two solutions on
paper...though many try.
I recommend to my clients that if they are undecided about which
database then they are best advised to remain undecided until they
complete the development of the protoype and do a side by side
comparison to prove to themselves which one they feel is best for them.
It is not lost on my clients that the database vendors are far more
likely to offer incentives when the decision process is clear and
imminent and the weaknesses of their product have been exposed in large
scale prototype... ;-)
Peter
www.peternolan.com

Database for datawarehouse

Hi,
Does anyone have any information on comparison between different databases
for datawarehousing? I am working on a study to develop a datawarehousing
solution for my client, so I would like to get some information on
competitive aspects of SQL Server 2005 over DB2 and Oracle.
Thanks all~SQL 2005 offer a better package then the others. (at a lower price then the
2 others)
In 1 product you have a database engine, OLAP engine, data mining engine,
report engine and ETL engine.
Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
(http://www.olapreport.com/market.htm)
From a performance point of view the 3 RDBMS servers are near identical
(www.tpc.org / TCP-H benchmark)
From a 3rd party support Microsoft is better, you'll found a lot of tools &
product compatible with AS 2000/2005 & SQL 2000/2005.
But you'll found a lot of articles which push one of these vendors...
You have to setup a complete list of needs & requirements of what your
client want.
But from my experience, this doesn't really helps you in your process.
Today the internal expertise & knowledge of a particular database/technology
is most important, if you know and allready use the product you are able to
provide better & faster results.
Also... get the demo versions of the tools or ask the vendors for a proof
of concept.
if you are really neutral, let the sales rep. to do their jobs.
"Stella" <stellajst@.hotmail.com> wrote in message
news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anyone have any information on comparison between different databases
> for datawarehousing? I am working on a study to develop a datawarehousing
> solution for my client, so I would like to get some information on
> competitive aspects of SQL Server 2005 over DB2 and Oracle.
> Thanks all~
>|||There is a technical paper with detailed comparison between Oracle and
SQL Server from wisdomforce site:
http://wisdomforce.com/dweb/resourc...10g_compare.pdf
As far as I remember it contains a lot of OLAP info
J=E9j=E9 wrote:
> SQL 2005 offer a better package then the others. (at a lower price then t=
he
> 2 others)
> In 1 product you have a database engine, OLAP engine, data mining engine,
> report engine and ETL engine.
> Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
> (http://www.olapreport.com/market.htm)
> From a performance point of view the 3 RDBMS servers are near identical
> (www.tpc.org / TCP-H benchmark)
> From a 3rd party support Microsoft is better, you'll found a lot of tools=
&
> product compatible with AS 2000/2005 & SQL 2000/2005.
> But you'll found a lot of articles which push one of these vendors...
> You have to setup a complete list of needs & requirements of what your
> client want.
> But from my experience, this doesn't really helps you in your process.
> Today the internal expertise & knowledge of a particular database/technol=
ogy
> is most important, if you know and allready use the product you are able =
to[vbcol=seagreen]
> provide better & faster results.
> Also... get the demo versions of the tools or ask the vendors for a proof
> of concept.
> if you are really neutral, let the sales rep. to do their jobs.
>
> "Stella" <stellajst@.hotmail.com> wrote in message
> news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
ses[vbcol=seagreen]
ing[vbcol=seagreen]|||Jeje,
"SQL 2005 offer a better package then the others. (at a lower price
then the 2 others) In 1 product you have a database engine, OLAP
engine, data mining engine, report engine and ETL engine."
In your opinion... ;-)
Yes, SQL Server is pretty good, and I've read up on 2005 and it's
pretty good too....I've used the beta and it seems to do what the book
says...
But I recently did a project where the design points were 10,000 users,
180M inserts per day and 15TB of disk.....and there is no question SQL
Server has no references at that level and therefore would not make the
cut under the 'proven technology' selection criteria.
So, which database is 'better' is best prefixed by asking the question,
what are you thinking of doing with it, and Stella has not provided
such information apart from 'data warehousing'...';-)
Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
hardly go wrong with any of these three...
Best Regards
Peter|||Thanks all for your help!
To be more specific, I'm actually looking for a comparison between databases
of 5TB to 8TB in size, running on Linux or Windows. Would anyone have ever
come across any cases of data-warehousing with these criteria?
Thanks~
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1131641995.382364.174340@.z14g2000cwz.googlegroups.com...
> Jeje,
> "SQL 2005 offer a better package then the others. (at a lower price
> then the 2 others) In 1 product you have a database engine, OLAP
> engine, data mining engine, report engine and ETL engine."
> In your opinion... ;-)
> Yes, SQL Server is pretty good, and I've read up on 2005 and it's
> pretty good too....I've used the beta and it seems to do what the book
> says...
> But I recently did a project where the design points were 10,000 users,
> 180M inserts per day and 15TB of disk.....and there is no question SQL
> Server has no references at that level and therefore would not make the
> cut under the 'proven technology' selection criteria.
> So, which database is 'better' is best prefixed by asking the question,
> what are you thinking of doing with it, and Stella has not provided
> such information apart from 'data warehousing'...';-)
> Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
> hardly go wrong with any of these three...
> Best Regards
> Peter
>|||Hi Stella,
the major metrics to provide are...
1. Volume of source data because each of the databases expands data by
a specific amount and most of them now also support varying levels of
compression. 5 to 8 TB means different sizes on different databases.
2. Number of rows in large tables.
3. Number of users.
As the size, complexity and number of users goes up
Oracle/DB2/Teradata/Sybase IQ come more into their own over mysql and
SQL Server. I think you will find few examples of 5-8TB databases
running on SQL Server and not that many on windows/oracle. For
DB2/oracle they are commonplace.
For IQ there are almost none because you get compression in IQ.....so
something that is 8TB disk in Oracle is about 800GB disk in IQ. IQ now
holds more of the top 10 records than any other database...amazingly it
is a very well kept secret of out friends at Sybase.
I think it is unrealistic to think that as a consulting provider to a
client you would be given the benefit of years of experience in using
these databases across many projects in short appends on a newsgroup.
Long term experience across a range of databases provides a set of hard
won skills which are of great value to clients should they want someone
to assist in the database decision.
You can ask the vendors. Each vendor produces detailed papers for their
database.
Each of MySQL, SQL Server, Oracle, DB2, Teradata, Sybase IQ have their
strenghts and weaknesses. No vendor is going to tell you anything about
their weaknesses and vendors (and biased consultants) spread plenty of
mis-information and out of date information about competitive products.
For SQL Server (we are on a MSFT newsgroup) MSFT has done an excellent
job of documenting project REAL.
http://www.microsoft.com/sql/soluti...rojectreal.mspx
This is one of the most open presentations of a BI effort any of the
vendors has produced. MSFT are to be congratulated on their open-ness
on this one!!
My FAQs page discusses some of the considerations.
http://www.peternolan.com/FAQs/tabid/136/Default.aspx but it is only
intended to provide advice for smaller clients. Larger clients are
expected to have staff to advise on these decisions or pay for advice
on these decisions. I have been paid by clients to prove that the
database they wanted to use would work for what they wanted to do with
it.
Having said all that...
Many people consider what database to use as setp 1 of a BI effort. It
is one of the most ineffective places to start. It is far better to
start with what business the business is in and to design and build a
working prototype before deciding on what database to use.
It is now possible to build large scale prototypes and to have the the
ETL completely transportable from one database to the other with only
the cost of converting the table definitions.
Of course, none of the database vendors will tell anyone that it is
possible to migrate a DW between databases with trivial effort!! It is
not in their interests. And MSFT/Oracle have tied their ETL tools
tightly to the database so if you write your ETL in one of these two
ETL products you will live with the database forever.
So...It is now possible to postpone the 'which database' decision
until very late in the process, which also means it is possible to
postpone the database license payment until late in the process.
Indeed, in some clients we have built the large scale prototype on 2
competing databases so that we could really compare apples with apples.
No amount of 'competitive information' is equal to actually running two
databases side by side, latest release, with the vendors competing for
the business. The mix of HW/OS/RDBMS/DISK and versions of all these
makes it pretty much impossible to compare two solutions on
paper...though many try.
I recommend to my clients that if they are undecided about which
database then they are best advised to remain undecided until they
complete the development of the protoype and do a side by side
comparison to prove to themselves which one they feel is best for them.
It is not lost on my clients that the database vendors are far more
likely to offer incentives when the decision process is clear and
imminent and the weaknesses of their product have been exposed in large
scale prototype... ;-)
Peter
www.peternolan.com

Database Driven Website using SAN

We want to develop database driven website. because of enormous amount
of data (300GB)
customers's tech advisor is insisting on using SAN instead of direct
attached storage method.
I want to know what issues need to be considered while using SAN in
web solutions.? Which database
server/ product is best suited for such kind of job ? Can anyone
suggests
good informative articles on this subject , available on web.
Thanks in advance
ssp2000"ssp2000" <ssp2000@.hotmail.com> wrote in message
news:78a9f1a5.0309290535.322076fb@.posting.google.com...
> We want to develop database driven website. because of enormous amount
> of data (300GB)
> customers's tech advisor is insisting on using SAN instead of direct
> attached storage method.
> I want to know what issues need to be considered while using SAN in
> web solutions.? Which database
> server/ product is best suited for such kind of job ? Can anyone
> suggests
> good informative articles on this subject , available on web.
Basically any full featured RDBMS product like Oracle or MS SQL Server will
be fine. As far as using a SAN with those, you'll need to follow-up in the
appropriate RDBMS forums ...
--
Tom Kaminski IIS MVP
http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS
http://mvp.support.microsoft.com/
http://www.microsoft.com/windowsserver2003/community/centers/iis/|||In addition to what Tom posted, there should be no issues in a web solution,
or any other application, directly related to using a SAN. The SAN
basically handles IO and storage transparently. Most SAN products are
designed to support IO-intensive environments and have features (sometimes
additional $) for managing large volumes of data, particularly
backup/restore and disaster recovery.
--
Bob
Microsoft Consulting Services
--
This posting is provided AS IS with no warranties, and confers no rights.|||In addition to what Tom and Bob's comments, I just want to add that, 300GB
isn't really that large these days. However, you also need to consider its
backups, and most likely you would need a Dev/QA environment as well.
Perhaps, tomorrow you'll be asked to support a reporting server. Very
quickly and easily, you can find yourself having to deal with near or over
one terabyte of data.
SAN gives you more flexibility in handling these varying factors in disk
storage requirements.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"ssp2000" <ssp2000@.hotmail.com> wrote in message
news:78a9f1a5.0309290535.322076fb@.posting.google.com...
> We want to develop database driven website. because of enormous amount
> of data (300GB)
> customers's tech advisor is insisting on using SAN instead of direct
> attached storage method.
> I want to know what issues need to be considered while using SAN in
> web solutions.? Which database
> server/ product is best suited for such kind of job ? Can anyone
> suggests
> good informative articles on this subject , available on web.
> Thanks in advance
> ssp2000

database design question

I am attempting to develop a forum. I have the pages I need and was just
curious if anyone here knows, or if there is a website, how to setup a
database for a forum? Basically, the columns is what I need.Nathan
<http://www.databaseanswers.com/data_models/index.htm> -- examples
database design
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:658A4F34-2AAC-4DAD-A42D-5FED367E48FA@.microsoft.com...
> I am attempting to develop a forum. I have the pages I need and was just
> curious if anyone here knows, or if there is a website, how to setup a
> database for a forum? Basically, the columns is what I need.|||If you are wanting to build your own database from scratch, the best advice
is to make a list of everything you want to include in your forums.
Everything. Then fill each item into a database design. Normalize it, and
you will have what you want. It is probably not as easy as it sounds, but
it is not all that hard either.
If you are looking for things to put into a forum design, look at the
website Uri gave you, and then hit lots of other forums to help make your
list of features that require data (and those that don't)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:658A4F34-2AAC-4DAD-A42D-5FED367E48FA@.microsoft.com...
>I am attempting to develop a forum. I have the pages I need and was just
> curious if anyone here knows, or if there is a website, how to setup a
> database for a forum? Basically, the columns is what I need.

Database Design Issue

Hello Friends,
I have to develop a database for website where Advertisements are displayed, Now i have to maintain number of clicks on an Advertisement on hourly basis so that report can be generated. That is number of click on Advertisement 789 between 10 AM and 11 AM.
What i am trying to do now is that i have a seperate tabel for clicks on Ad. where i store Ad_ID and nuber of clicks, and time duration.
Now my challenge is that is i store like that than for every ad there will be 24 rows per day. There can be as many as 1000 ads on that site so there will be 24*1000 rows added per day on that table alone.
Is there a better way of designing it. Because If the database becomes bulky it will take lots of time while retrieving the data.

thanks in advance
saddysanYou might consider putting this table in it's own database.
After each hour the rows for that row will be static so you could have an active table and an archive table. During slow times on the server copy the expired hours to the archive table to keep the active table small. This will give less likelyhood of corruption too.

You could make single rows with 24 fields for the hours but that wouldn't give much benefit and would cause more difficult coding.

Instead of updating the row you could insert an entry in a table then create the aggregate overnight.
This should cause less contention over locks as an update is relatively slow compared to insert.

Your current design will give around 8 million very small static rows per year which isn't too bad.
Just make sure that you create aggregate tables for any queries and don't try to query the active table.|||Do you really want data for each click? Think about what the final goal is. Maybe you can collect / evaluate the data as it comes in and store it in a summary row. Less data, more information--maybe.

--jfp|||Dumping data into a database is not nearly as recource-comsuming as reading data out from the database so you can easily fill it with plenty of rows without meeting any performance-problems. However, when you need your reporst on this you might want to look into some datawarehousing-techniques (i.e. look up OLAP in BOL). And I would also suggest that you only store Ad_ID and the date/time (GETDATE()) it was hit in the database, if you do that then you have all you need. Then you can aggregate the data into day-by-day and hour-by-hour reports say on a weekly or a monthly basis...

database design - joins on which fields?

Hi,

I,m trying to develop a relational database but confused so much & need help to streamline the basics of the the database design error free.

Below is the case,

There r six databases with the joined fileds as below,

Buyer( filed used to join with style / po / production / shipped = Buyer Name )
Factory ( filed used to join with style / po / production / shipped = Factory Name )
Style ( one style have multiple pos ) ( filed used to join with po / production / shipped = Style # )
Po ( one po have multiple styles )( filed used to join with production / shipped = Po # )
Production
Shipped

There is nothing unique in all the 6 databases to make any filed primary key.

If in Buyer database, if we change the buyer name then all the records ralated to this buyer disturbed in all the databases & same is the case for factory name, style # & po # in Factory / Style & PO databases & any of these changes in first 4 databases ( i.e Buyer / Factory / Style / Po ) also badly effects the record linking & records in Production & Shipped databases.

Need advice how to join the databases in this case?

Is I use the auto serial # method in all the 6 databases or is there other ways to do so?

Will appreciate if someone let me know the way or guide me on this?

Thanks / Sulman

Sorry, but due to this unclear information given, I think it is hard to find a quick solution for you, perhaps you post some DDL and sample data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Database Design - 3000+ users

We are in the process to develop a new VB6/SQL 2000 application (do
not ask why not .net, our company policy is to use VB6), which will
have over 3000 users to access the database (50GB data) and perform
heavy add/update on one table. Without the privilege to have a
better/faster server, we have to design database tables in a way to
increase the performance (on updates).
My boss wants to partition the data into different databases based on
defferent users groups (for examples, use users' department). Since
the data now on different databases, the updates for different user
groups would be faster. My concern is that these different databases
will hard to maintain. For example, a same query/stored procedure will
either reside on different
databases or coded with logic to go to different databases before
executing a update. It may better to have one database with multiple
(same structure) tables to store data for different user groups, then
use a (partitioned) view to link all tables (with check constraint
based on user's department number). When update, it would go to
different tables (hence reduce the traffic to tables and increase the
performance).
Thank you in advance for your suggestions.
Yang ZhongWhat makes you think since you have several db's the updates will be faster?
All the databases share the same resources and it doesn't sound like they
will be updating the same row anyway.
--
Andrew J. Kelly SQL MVP
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong|||(a) Do these users only access the data that they Insert/Update/Delete? Or,
can they access the data created by other user groups ~ different database?
(b) "perform heavy add/update on one table. Without the privilege to have a".
What kind (vertical / industry) of application is it ? (Different user
groups all updating a single table)
(c) Since all your databases (based on the different user groups) will
reside in one server - there will be no performance benefit when you use
partitioned views.
BUT, is it posisble to have each user group's database sitting on a
different physical disk with its own controller? If YES, then you will get
some of the performance benfits of a partitioned view.
Cheers!|||Since SQL can do row level locking, I suspect there will not be a lot of
locking issues for updates, (assuming single row updates), on a properly
indexed table where the Primary Key is used in the where clause.
Therefore, there may be no reason to go to all of the trouble you are
talking about.
It is certainly worth doing some testing to see!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong

Database Design - 3000+ users

We are in the process to develop a new VB6/SQL 2000 application (do
not ask why not .net, our company policy is to use VB6), which will
have over 3000 users to access the database (50GB data) and perform
heavy add/update on one table. Without the privilege to have a
better/faster server, we have to design database tables in a way to
increase the performance (on updates).
My boss wants to partition the data into different databases based on
defferent users groups (for examples, use users' department). Since
the data now on different databases, the updates for different user
groups would be faster. My concern is that these different databases
will hard to maintain. For example, a same query/stored procedure will
either reside on different
databases or coded with logic to go to different databases before
executing a update. It may better to have one database with multiple
(same structure) tables to store data for different user groups, then
use a (partitioned) view to link all tables (with check constraint
based on user's department number). When update, it would go to
different tables (hence reduce the traffic to tables and increase the
performance).
Thank you in advance for your suggestions.
Yang Zhong
What makes you think since you have several db's the updates will be faster?
All the databases share the same resources and it doesn't sound like they
will be updating the same row anyway.
Andrew J. Kelly SQL MVP
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.c om...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong
|||Since SQL can do row level locking, I suspect there will not be a lot of
locking issues for updates, (assuming single row updates), on a properly
indexed table where the Primary Key is used in the where clause.
Therefore, there may be no reason to go to all of the trouble you are
talking about.
It is certainly worth doing some testing to see!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.c om...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong

Database Design - 3000+ users

We are in the process to develop a new VB6/SQL 2000 application (do
not ask why not .net, our company policy is to use VB6), which will
have over 3000 users to access the database (50GB data) and perform
heavy add/update on one table. Without the privilege to have a
better/faster server, we have to design database tables in a way to
increase the performance (on updates).
My boss wants to partition the data into different databases based on
defferent users groups (for examples, use users' department). Since
the data now on different databases, the updates for different user
groups would be faster. My concern is that these different databases
will hard to maintain. For example, a same query/stored procedure will
either reside on different
databases or coded with logic to go to different databases before
executing a update. It may better to have one database with multiple
(same structure) tables to store data for different user groups, then
use a (partitioned) view to link all tables (with check constraint
based on user's department number). When update, it would go to
different tables (hence reduce the traffic to tables and increase the
performance).
Thank you in advance for your suggestions.
Yang ZhongWhat makes you think since you have several db's the updates will be faster?
All the databases share the same resources and it doesn't sound like they
will be updating the same row anyway.
Andrew J. Kelly SQL MVP
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong|||Since SQL can do row level locking, I suspect there will not be a lot of
locking issues for updates, (assuming single row updates), on a properly
indexed table where the Primary Key is used in the where clause.
Therefore, there may be no reason to go to all of the trouble you are
talking about.
It is certainly worth doing some testing to see!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong

Database design

Hai friends,
I am asked to develop a project for a steel company for its warehouse processing. So I would like to know how to design a database for the warehouse keeping. Plz help me out to solve this problem.I would like to know how many fields are to be there in the different database and how to get the requirements that are required for warehousing.

Thanks in advance
raghulIt is practicaly impossible to suggest you anything without accessing the actual situation. how do you expect us to guide you without having any idea what is going on at your side.

Database Creation

Hello...

I want to develop a web site having two features

1. Online Shopping
2. Forums

Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once.

____________
Thanks in adv
Nauman Ahmed

Creating of 2 separated DB will be better.

You can use the registration info in one DB table and to use this DB table for authentication. So, registrations will be one time only

regards