Database Efficiency

Hello all,
I am developing a website which may be used by a large number of people in the future and I am concerned about performance.

Is it better to have one table with 50, 000 rows or 5,000 tables with 10 rows each?Is there a way to divide a table in two if the table reaches a certain size?Is there a limit on the size of tables?Is there a limit on the number of tables?Is it possible to create tables from vb.net?Is it possible to program checks into sql server? For example, could I delete data that has passed a certain date or send an automated email when a time is reached?

Thanks for your time,
Padraic

From your questions, it's clear that you have absolutely no idea how to do the task you are set out to do.
You need to spend some time learning the basics. Pick up a copy of INTRODUCTION TO DATABASE SYSTEMS by DATE. It's a text book,. so you can get an older edition for a few bux.
This is not something you can teach yourself by clicking around with wizards in sql server.|||you may want to ask these questions in smaller chunks with more detail. The answer to the last question is yes. Just write a stored procedure that checks a datetime column and deletes anything older than X date (You will want to use the datediff function). You can schedule it to run daily as a job. The answer to your second to last question is yes. You can execute any query statement through vb.net, including:

create table tablename
(
column1 varchar(50)
column2 varchar(50)
)

Database design question for experts

Hi,

I have 2 design related questions.

Q1: We are developing a huge .NET e-commerce web application with a number of modules - Shopping, 'For Sell' , 'For Rent', News, Jobs, Community, Matchmaking etc. These modules will store data into SQL server 2000 database server. 'For Sell' module will be used for all user ADs for selling items(add\update\search), similarly 'For Rent' module will be for Rentals ADs. The site will be open for 20+ different countries initially and will store unlimited ADs (eg. 200,000 For Sell ADs), Shopping Catalog (100,000 items).

We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc. The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.

Q2: Will it be better to create a single web application or different web application for each module?

Please give us your expert input\suggestions\tips that will guide our team to the right direction.

Thanks

Jennifer

The size of the database you're describing is nothing out of theordinary for an SQL Server instalation. More important than the numberof records is the number and type of queries you expect to be runningper second. If a single beefy (multi CPU Xeon with as much memory asthe system will take, fast IO subsystem) SQL Server can not handle theload then you could add more servers and build a cluster. I'm no expertat these matter and frankly if you need to come to this forum to learnabout these things the project may be a bit outside of your team'scapabilities. Assuming this is a professional, commercial applicationyou're probably better of hiring an SQL Server expert as a consultantfor a few weeks. But his or her effectiveness will be limited by howwell you know what you're trying to achieve.
In the meantime I recommend you do some experiments. Build a databaseserver and populate it with tables and arbitrary data. Then write someof the more complex and frequently called queries you expect to run.See how many of those you can run in a minute. That will give you someidea what performance limitations you're looking at.
One ASP.NET app that I wrote mines a database to generate a test resultreport. Depending on parameters it can run about 2000 queries, half ofthose are joins over 5 tables or more. The report completes in a minuteor two. This includes generating all the ASP.NET webcontrols etc. Themachine is a dual Xeon 2.8G with 2 GB RAM with mirrored SCSI drives.IIS and SQL reside on the same box.
I would not recommend splitting the application into multiple parts Idon't think there's a performance impact either way but you benefitfrom easier management in a single app. I'd be interested to hear otheropinions on this though.

Good luck!
|||

Nocturnal,

Thanks for your detailed post. Our 6 modules will have around 1 MILLION records in the database! even though we will be deleting old records regularly. Do you still call this an ordinary database for an ecommerce application? Here is more on the database internal: we create views by running complex join on the database involving ~10 tables. Each module has one big view against which we run the search queries for each module.

Having this said, do you still think ONE database with ONE web application suits our needs best?

What I am seeing from your comments is that making one WEB application will be easier to manage and performance impact won't hurt comparing to individual app for each module.

Thanks for your expertise valuable suggestions.

|||Hi,
first of all, I'm hardly an expert so take my suggestions with a grain of salt ok? :-)
I didn't mean to say that 1M records is normal for a e-commerceapplication but it's nothing out of the ordinary for SQL Server. SQLServer doesn't care what application stores its data there, after all.1 M e-ecommerce records is the same as 1 M cellphone records, inconcept.
1M records sounds like a big deal but it depends on the average recordsize as well. Imagine a 200 byte record. Loading the entire table inmemory will take only 200 MB plus overhead. Let's be conservative andcall the overhead 2x. That's 400 MB. Hardly worth mentioning on aserver that has 4 GB of RAM or more. Add some smart indexing and thistable can be searched very quickly.
There are volumes and volumes written about database optimization. Iknow very little about the subject and it's beyond what a forum canreally teach anyway. You should be able to learn the essence from acombination of studying the right books, experimentation and plain ol'experience.
It is good that you have some grasp of the scalability requirements ofyour application. Many designers don't. Now take it one step furtherand start experimenting with your database structures. Create somerepresentative tables and fill them with 1M random records. Then runthe queries against it that you expect your web app to run. Measureperformance. Measure server impact (watch for high CPU utilization andswapping). Modify the experiment, draw conclusions, refine your design.There's really no substitute for experimentation. Have a go at it andlet us know!
|||

jennifer7290 wrote:

We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc.

You should probably start by having proper names for your tables. A table represents a SET of entities. "Modules", not "module". Also, the name "forsell" and "shopping" makes very little sense.What is a "shopping"? Use names that describe what the table contains. See ISO-11179.

jennifer7290 wrote:

The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.

Q2: Will it be better to create a single web application or different web application for each module?

The single largest problem you'll face with many applications is the inability to share session state -- session state goes away at the application boundry, so you'll have to develop your own site-wide state management - which may not be so bad, anyway, as a lot of applications I see go with shared Context model.

As far as your database, it doesn't have to reflect your application. A database can have quite literally a crap load of tables (2B is the max I believe) and you'll be more than fine with a few hundred. Separate databases mean separate Roles, Users, etc -- and can be quite a pain to set up. I'd recommend sticking with one -- unlike the application, you have a common "security" module shared by each database.

|||

Hello, good question... a single database or multiple databases?

Let's consider

1) A table with 10 Milion of rows is not a problem for SQL server if we use a proper index

2) 10 Tables of 1 M of rows are better then a table of 10M of rows

3) Performace ofter depends on applications that use a database (use pooling)

4) Make minimal queries and use recorset in memory (1 query of 1000 rows is batter than 10 queries of 100 rows)

I suggest 1 DB.

Hello!

|||Thanks to Alex and Claudio for sharing your valuable input. That really matters. Have a nice day!

Database design issue

Hello folks,

We are developing a datamart to which data comes from different sources (SQL, Sybase, Excel, MDB). There is going to be a refresh process that will do the retrieval.
In the source tables, there are columns that can be nullable.
In the destinaton tables, we are planning to convert all nullable to NOT Null, so that the indexing can be applied and the retrieval will be faster.

But then what default value can we give it for data types Varchar, Numeric and Date.

Is it fine to give Spaces(1) for Varchar and zero for numneric. What do we give for date?

If anyone can give an insight into these questions, i really appreciate it.

Thank you,
VenugopalYou can give '' for the varchar.

Be very careful though as you will be losing information as there will now be no difference between a null and empty string or null and zero numeric. These differences may be meaningful in the source which will now be lost.|||You can index a column that allows NULL values. Only the primary key is not allowed to take NULL values.

blindman|||In the destinaton tables, we are planning to convert all nullable to NOT Null, so that the indexing can be applied and the retrieval will be faster.


Eh?

Columns that contain NULLs can certainly be indexed; they just can't be a primary key. There is no speed issue involved.

The database schema must reflect the requirements of your data. If the original data can contain NULL values, then the repository database must do so also. There is no value whatsoever that you can substitute for "the absence of any value," which is what NULL is.|||And it can't be a unique index...DB2 has an option that allows it, but It doesn'r seems so with SQL Server...unless I'm missing it (WHAT? AGAIN?)

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int, col2 char(10))
GO

CREATE INDEX myIndex1 ON myTable99 (Col1)
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT Null, 'C' UNION ALL
SELECT Null, 'D'

SELECT * FROM myTable99
GO

CREATE UNIQUE INDEX myIndex2 ON myTable99 (Col2)
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'E' UNION ALL
SELECT 2, 'F' UNION ALL
SELECT Null, Null
GO

SELECT * FROM myTable99
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 3, Null
GO

DROP TABLE myTable99
GO

Database Design for Catalog Management

Hi,

I need advice on my design.

I'm developing a system that track the catalog distribution.

I have a 5 table.

Company Table - Store list of company. Each company can have many staff.

Staff Table - Store list of staff.

Catalogs Table - Store list of catalog. Each catalog can have many revision.

PKEY id

name

latest

CatalogsRev Table - Store list of catalog revision.

PKEY catalogID -> Catalogs.id

PKEY year

Staff_Catalog Table - Each staff can have many catalog revision.

PKEY staffID -> Staff.id

PKEY catalogID -> CatalogsRev.catalogID

PKEY year -> CatalogsRev.year

Is the above table good enough to track which company do not have latest catalog revision?

For the Catalogs Table should I use a trigger to update the Catalogs.latest column everytime a new CatalogsRev is entered?

I've read some article on the web says trigger is slow, but in my situation is it suitable to use trigger?

Thanks,

Max

Hi
if u feel u r database satisifes 3rd normal form abs u r database is ok.SO u have to decide u r tables are going to satisify the normalization or not. Ok

U can write a trigger to modify the catalog information. Here u r modifying a column value i think it will not affect on performanace. I think so.

Thank u
Baba

DataBase Creation

I am developing and application which requires a database and 7 tables.
I am trying to figure out how to do the installation of this database and
what approach to installing a database is appropriate, as I have never
installed an application which requires the installation of database.
I was hoping someone could point me in the direction of the "appropriate
way to do this".
Should I create the script files for the database and tables ind then
execute that SQL to build the tables, possibly prompting the user for the
location as to where the tables should be installed?
Should I do a Database backup of an "empty" database structure (database
and tables) and then do some sort of restore of the database through my
application?
What is the appropriate technique to this type of installation?
Thanks in advance for your assistance!!!!!!If you want to supply your database with some pre-defined values are some
data records, sending a backup of the database might be a good option, since
all the users have to do is to restore the database at their end using
Enterprise Manager. If you want to avoid this hazzle, you can create the
scripts for the database along with table creation and data insertion
scripts and then have it execute. The "pubs" database of SQL Server is done
in this way. If you have SQL Server installed, you can see how this script
is structured, by navigating to: C:\Program Files\Microsoft SQL
Server\MSSQL\Install\instpubs.sql
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!|||I have seen companies do both... The problem I have with delivering mdf and
ldf files or backup files, is that
1. These files may have a limited life span, since MS could change the
file format
2. You would have pre-selected collation and initial file sizes and
layout
I prefer using scripts, then you are covered... Then high end user can then
create the database in any way they see fit and run your scripts to setup
the tables and data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!|||One of our 10 commandments here is "Always script".
We script table creation - index creation - table format
changes - everything. We script BULK INSERT's from .txt
files to pre-fill tables with data. We script the
dropping and re-creation of stored procedures.
We script the "GRANT" of access/rights/etc.
If someone makes a change in Enterprise Manager we would
never know to get it into production. That would be a
disaster.
Been working very well for us. We are simultaneously
developing three large enterprise apps - migrating from
mainframes (VAX's) that have been in use since 1980's
>--Original Message--
>If you want to supply your database with some pre-
defined values are some
>data records, sending a backup of the database might be
a good option, since
>all the users have to do is to restore the database at
their end using
>Enterprise Manager. If you want to avoid this hazzle,
you can create the
>scripts for the database along with table creation and
data insertion
>scripts and then have it execute. The "pubs" database of
SQL Server is done
>in this way. If you have SQL Server installed, you can
see how this script
>is structured, by navigating to: C:\Program
Files\Microsoft SQL
>Server\MSSQL\Install\instpubs.sql
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
>news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
>> I am developing and application which requires a
database and 7 tables.
>> I am trying to figure out how to do the installation
of this database and
>> what approach to installing a database is appropriate,
as I have never
>> installed an application which requires the
installation of database.
>> I was hoping someone could point me in the direction
of the "appropriate
>> way to do this".
>> Should I create the script files for the database and
tables ind then
>> execute that SQL to build the tables, possibly
prompting the user for the
>> location as to where the tables should be installed?
>> Should I do a Database backup of an "empty" database
structure (database
>> and tables) and then do some sort of restore of the
database through my
>> application?
>> What is the appropriate technique to this type of
installation?
>>
>> Thanks in advance for your assistance!!!!!!
>
>.
>

DataBase Creation

I am developing and application which requires a database and 7 tables.
I am trying to figure out how to do the installation of this database and
what approach to installing a database is appropriate, as I have never
installed an application which requires the installation of database.
I was hoping someone could point me in the direction of the "appropriate
way to do this".
Should I create the script files for the database and tables ind then
execute that SQL to build the tables, possibly prompting the user for the
location as to where the tables should be installed?
Should I do a Database backup of an "empty" database structure (database
and tables) and then do some sort of restore of the database through my
application?
What is the appropriate technique to this type of installation?
Thanks in advance for your assistance!!!!!!If you want to supply your database with some pre-defined values are some
data records, sending a backup of the database might be a good option, since
all the users have to do is to restore the database at their end using
Enterprise Manager. If you want to avoid this hazzle, you can create the
scripts for the database along with table creation and data insertion
scripts and then have it execute. The "pubs" database of SQL Server is done
in this way. If you have SQL Server installed, you can see how this script
is structured, by navigating to: C:\Program Files\Microsoft SQL
Server\MSSQL\Install\instpubs.sql
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom
@.207.46.248.16...

quote:

---

> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!

---

|||I have seen companies do both... The problem I have with delivering mdf and
ldf files or backup files, is that
1. These files may have a limited life span, since MS could change the
file format
2. You would have pre-selected collation and initial file sizes and
layout
I prefer using scripts, then you are covered... Then high end user can then
create the database in any way they see fit and run your scripts to setup
the tables and data...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom
@.207.46.248.16...

quote:

---

> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!

---

|||One of our 10 commandments here is "Always script".
We script table creation - index creation - table format
changes - everything. We script BULK INSERT's from .txt
files to pre-fill tables with data. We script the
dropping and re-creation of stored procedures.
We script the "GRANT" of access/rights/etc.
If someone makes a change in Enterprise Manager we would
never know to get it into production. That would be a
disaster.
Been working very well for us. We are simultaneously
developing three large enterprise apps - migrating from
mainframes (VAX's) that have been in use since 1980's

quote:

---

>--Original Message--
>If you want to supply your database with some pre-

---

defined values are some

quote:

---

>data records, sending a backup of the database might be

---

a good option, since

quote:

---

>all the users have to do is to restore the database at

---

their end using

quote:

---

>Enterprise Manager. If you want to avoid this hazzle,

---

you can create the

quote:

---

>scripts for the database along with table creation and

---

data insertion

quote:

---

>scripts and then have it execute. The "pubs" database of

---

SQL Server is done

quote:

---

>in this way. If you have SQL Server installed, you can

---

see how this script

quote:

---

>is structured, by navigating to: C:\Program

---

Files\Microsoft SQL

quote:

---

>Server\MSSQL\Install\instpubs.sql
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
> news:Xns946EDAE2F587DJimHeaveyhotmailcom
@.207.46.248.16...
database and 7 tables.[QUOTE]
of this database and[QUOTE]
as I have never[QUOTE]
installation of database.[QUOTE]
of the "appropriate[QUOTE]
tables ind then[QUOTE]
prompting the user for the[QUOTE]
structure (database[QUOTE]
database through my[QUOTE]
installation?[QUOTE]
>
>.
>

---