Database Growth Options

Please, is there an advantage for using a static megabit number over using a
percentage and what will be a recommeded growth size for a 3gb database.
Thanks in advance for your assistance.
KenO
In a production environment, relying upon the Autogrow features is outside
of 'Best Practices'. Not bad to have as an 'emergency fallback' option, but
the database filegrowth 'should' be actively managed and extended during
'slack' operation times.
If you 'MUST' reply upon the Autogrow features, then the use of the
database, the new/changed data flow, and the number of users are important
considerations. A database used only for reporting may not need any
Autogrowth, whereas a very active order entry database with hundreds of
users may need to 'grow' in large chunks.
The primary thing to remember is that Autogrow has a performance impact, and
may result in fragmented datafiles, so you don't want it happening often.
Grow by large enough chunks to handle a substantial amount of need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
|||KenO wrote:
> Please, is there an advantage for using a static megabit number over using a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Imagine your database is 214GB, and you've set it to auto-grow at a 10%
interval. The next time it grows, you're going to have to wait for it
to write 21.4GB of empty space. Might take a while.
Ideally, your database should never grow automatically. You, as the
DBA, should plan accordingly, and adjust the database size as needed
during planned outages or quiet periods when the overhead of growing the
database won't affect performance.
How much to grow by? Again, YOU need to determine that. If you add
600MB of data each month, and you have a monthly outage window, then
you'll want to add AT LEAST 600MB to the database during that outage.
You also need to consider disk fragmentation. The more often you add
space to the database, the more "pieces" the database will be broken in
to. You can reach a point where the database is so badly fragmented
that performance begins to suffer, due to the need to hunt around on the
disk for all of the fragments.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Ken
http://www.sql-server-performance.com/database_settings.asp
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO

Database Growth Options

Please, is there an advantage for using a static megabit number over using a
percentage and what will be a recommeded growth size for a 3gb database.
Thanks in advance for your assistance.
KenOIn a production environment, relying upon the Autogrow features is outside
of 'Best Practices'. Not bad to have as an 'emergency fallback' option, but
the database filegrowth 'should' be actively managed and extended during
'slack' operation times.
If you 'MUST' reply upon the Autogrow features, then the use of the
database, the new/changed data flow, and the number of users are important
considerations. A database used only for reporting may not need any
Autogrowth, whereas a very active order entry database with hundreds of
users may need to 'grow' in large chunks.
The primary thing to remember is that Autogrow has a performance impact, and
may result in fragmented datafiles, so you don't want it happening often.
Grow by large enough chunks to handle a substantial amount of need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO|||KenO wrote:
> Please, is there an advantage for using a static megabit number over using
a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Imagine your database is 214GB, and you've set it to auto-grow at a 10%
interval. The next time it grows, you're going to have to wait for it
to write 21.4GB of empty space. Might take a while.
Ideally, your database should never grow automatically. You, as the
DBA, should plan accordingly, and adjust the database size as needed
during planned outages or quiet periods when the overhead of growing the
database won't affect performance.
How much to grow by? Again, YOU need to determine that. If you add
600MB of data each month, and you have a monthly outage window, then
you'll want to add AT LEAST 600MB to the database during that outage.
You also need to consider disk fragmentation. The more often you add
space to the database, the more "pieces" the database will be broken in
to. You can reach a point where the database is so badly fragmented
that performance begins to suffer, due to the need to hunt around on the
disk for all of the fragments.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ken
http://www.sql-server-performance.c...se_settings.asp
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO

Database for internet applications

I have a colleague who wants to build a web application that is expected to
have a fairly low number of web requests and have this site capture data in
a database. His heart is set upon Access as the back-end database.
Now, I'm happy to accept that Access is a great product, but I know very
little about it - I've always used SQL Server. My gut reaction is to
suggest that he uses SQL Server Express in preference to Access, but
unfortunately I don't know enough about Access to argue why it shouldn't be
used as a database behind a web application. I don't even know much about
the licensing model.
Could someone provide the details here (or persuade me otherwise)?
Thanks everyoneGriff
SQL Server 2005 Express Edition is free but limited to 4GB db size.If I
remeber well Access is limited to 2GB db size.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||On Oct 18, 6:18 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> I have a colleague who wants to build a web application that is expected to
> have a fairly low number of web requests and have this site capture data in
> a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't be
> used as a database behind a web application. I don't even know much about
> the licensing model.
Some notes here:
http://members.optushome.com.au/anandasim/asp.htm
You don't have to worry about licensing. Because when you use
Access .mdb as a back end database, you are not using an Access
license - you are using IIS + ADO/ADO.NET + mdb - Access does not even
need to be installed on the webserver.
One concern is .mdb is not 24x7 - you need to take it offline from
time to time to pack the database and back it up - on the other hand,
if you get the time slot and scheduled batch jobs running, you can
just grab the .mdb and put it in your pocket.
One thing to consider is if you are using a development IDE against
the Access .mdb will there be design time issues with controls and
connections working properly - I used to have those years ago. And Jet
is single threaded - when I had SQL that was half baked during
development, the whole web server would be unresponsive.
HTH
Ananda|||The salomic decission
Use SQL server as the datastore & msAccess as a FrontEnd for maintenance,
reports etc
For the solution you describe the only drawbacks of using Access as a
Back-end is that it has no built in maintenance plan,
must be "offline" for backup, can get corrupted.
Pieter
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||There are no licensing issues with supplying data from an Access database
file to a web page.
There is some advantage in using an Access file to store the data, if you
need to enter, query, and report on the data from within Access.
It is not difficult for someone with experience in something like ASP or PHP
to design a web page where data is read from and/or written to an Access
database.
Access data is not really secure, so you need to ensure you handle the
security at another level. For example, last year I was able to download the
entire MDB from a computer hardware vendor because the database went
corrupt, and the error message showed the name and location of the file. In
this case, the db contained only information that was on the web (i.e. parts
and prices, not customers and orders) so no harm was done (though I did
notify them.)
If you just need to export the data from an Access table to a web page (e.g.
a product listing that needs to be re-exported whenever products or prices
change), you can also do that easily. Here's an example:
http://allenbrowne.com/AppOutputHtml.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone|||One thing about Access as far as web applications are concerned is that it
is still a file. If I happen to know the physical path where the Access
database file is stored (a vulnerability exposed can do this), I can
download the database file from the site ;-)
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||Wow - thanks for all the feed-back everyone.
Another thing I've just thought of... Access didn't have the concept of
stored procedures - I don't know if the latest version now does. If not,
then it's of course more vulnerable to SQL Injection attacks.|||You can emulate stored procedures in Access but only in version 2002 and
higher altough a few claim that Access 2000 can do it too. There are some
limitations though such as no temp tables, output parameters cannot be used,
does not use the @. character
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:e%231tviXEIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Wow - thanks for all the feed-back everyone.
> Another thing I've just thought of... Access didn't have the concept of
> stored procedures - I don't know if the latest version now does. If not,
> then it's of course more vulnerable to SQL Injection attacks.
>|||In article <uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl>, griffithsj_520
@.hotmail.com says...
> I'm happy to accept that Access is a great product
Access is only good for single user database tasks, it's always been
unstable for more than one user at at time.
Use one of the MSDE SQL versions or other versions for your solution and
you'll be a lot happier than using Access.
If you need reports you can use MS Access to pull them, but don't use
Access for a database.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Leythos wrote:
> In article <uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl>, griffithsj_520
> @.hotmail.com says...
>> I'm happy to accept that Access is a great product
> Access is only good for single user database tasks, it's always been
> unstable for more than one user at at time.
> Use one of the MSDE SQL versions or other versions for your solution and
> you'll be a lot happier than using Access.
> If you need reports you can use MS Access to pull them, but don't use
> Access for a database.
You probably missed the fact that this thread is crossposted to an
Access NG, or do you really believe what you wrote about Access?
--
Roy-Vidar|||Allen,
I took a look at your link with some interest, do you know how I can go
about doing the reverse; importing data Into Access 2007 from a webpage;
specifically Googlemaps. Here's what I want to do:
I want to click on an address filed in my Access form and take that address
to googlemaps and have it return a Latitude, longtitude. Before I continue,
let me explain that I already have a fully functioning database with a form
that does the first part succesffuly, namely sending the address (in the
correct google friendly format) to googlemaps, and the browser succesfully
finds the address, and using a combination of a single line Javascript (not
authored by me, I don't understand Java) and some cumbersome Access Sendkeys
I am able to get the Lat/Long and pasted it back into the field. This
approach has it's drawbacks, it involves Alt-tabbing two application windows
and when the net connection is busy, then the timing of the Senkeys gets out
of sequence and the whole process fails, If I increase the timings, then the
process becomes to slow. In summary, it is a bit of a Heath-Robinson setup.
So I was wondering if you could give me some pointers and an understanding
on how to go about automating the process properly. I can understand VBA to a
certain extent, but really don't know where to start. What do you think, can
it be done?
"Allen Browne" wrote:
> There are no licensing issues with supplying data from an Access database
> file to a web page.
> There is some advantage in using an Access file to store the data, if you
> need to enter, query, and report on the data from within Access.
> It is not difficult for someone with experience in something like ASP or PHP
> to design a web page where data is read from and/or written to an Access
> database.
> Access data is not really secure, so you need to ensure you handle the
> security at another level. For example, last year I was able to download the
> entire MDB from a computer hardware vendor because the database went
> corrupt, and the error message showed the name and location of the file. In
> this case, the db contained only information that was on the web (i.e. parts
> and prices, not customers and orders) so no harm was done (though I did
> notify them.)
> If you just need to export the data from an Access table to a web page (e.g.
> a product listing that needs to be re-exported whenever products or prices
> change), you can also do that easily. Here's an example:
> http://allenbrowne.com/AppOutputHtml.html
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "Griff" <griffithsj_520@.hotmail.com> wrote in message
> news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
> >I have a colleague who wants to build a web application that is expected to
> >have a fairly low number of web requests and have this site capture data in
> >a database. His heart is set upon Access as the back-end database.
> >
> > Now, I'm happy to accept that Access is a great product, but I know very
> > little about it - I've always used SQL Server. My gut reaction is to
> > suggest that he uses SQL Server Express in preference to Access, but
> > unfortunately I don't know enough about Access to argue why it shouldn't
> > be used as a database behind a web application. I don't even know much
> > about the licensing model.
> >
> > Could someone provide the details here (or persuade me otherwise)?
> >
> > Thanks everyone
>

Database error number 105

I am using ColdFusion to construct a SQL Server insert statement, the statement appears as expected in the debugger and if I cut and paste it into the Query Analyzer it works as expected. Yet from my code it produces the following error:

Database error number 105
[Macromedia][SQLServer JDBC Driver][SQLServer]Unclosed quotation mark before the character string 'kellyc'.

It almost seems as if somehow ColdFusion is introducing the problem. The server is MX, I similarly constructed Insert Statements that are working.

Here is the code...

From my Action Page...Its the first query that is failing.

<cftransaction action="begin">
<cftry>

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "AddCase"
CaseName = "#form.CaseName#"
Version = "#form.CaseVersion#"
Desc = "#form.CaseDescription#"
Start = "#form.CaseStartDate#"
End = "#form.CaseEndDate#">

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "qGetMaxCase"
CaseName = "#form.CaseName#">

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "AddCaseStatus"
CaseID = "#qGetMaxCase.NewCaseID#"
Status = "#form.CaseStatus#">

<cfset HaveError = False>
<cfcatch type="Database">
<cftransaction action="rollback">
<cfset HaveError = True>
<cf_dbErrorDisplay errno = #cfcatch.NativeErrorCode# detail = #cfcatch.Detail#>
</cfcatch>

</cftry>
</cftransaction>

And then from my queries page...

<cfif queryname is "AddCase">
<CFQUERY NAME="AddCase" DATASOURCE=#variables.DSN#>
Insert into CaseInfo
(CaseName, CaseDescription, CaseVersion, CaseStartDate, CaseEndDate)
Values ('#PreserveSingleQuotes(attributes.CaseName)#',
'#Attributes.Desc#',
'#Attributes.Version#',
'#Attributes.Start#',
'#Attributes.End#')
</CFQUERY>
</cfif>

This is producing a query that in the debug dump looks like...

Insert into CaseInfo
(CaseName, CaseDescription, CaseVersion, CaseStartDate, CaseEndDate)
Values ('kellyc', 'test', '1.1', '12/10/2003', '12/11/2003')

Which inserts correctly using Query Analyzer.

The main thing that is different about this insert from other inserts that are functioning in the program is the use of transactin processing.

Any help would be most appreciated!

KellyCKellyC,

I'm afraid I don't know a thing about ColdFusion, but here is what I would try: start up SQL Profiler and run a trace on your application. You can probably use the default settings for the trace unless it is a production server and in heavy use (in which case it's a pain to find the specific transaction you're looking for).

You can then identify the format of the T-SQL Statement (or SP) that is being used and test changes in your code to make corrections.

Regards,

hmscott|||i'm betting it's a coldfusion problem

see http://www.dbforums.com/showthread.php?threadid=974731

kelly, please don't cross-post

rudy|||Sorry Rudy,

I didn't realize it was bad form, since not everyone is subsribed to the same forums, and I don't really know if the problem is ColdFusion or SQL related at this point.

hmscott,

I have sent in a request to the DBA to try running the Profiler... I am now in the waiting zone. If it turns up something I will post that here.
Thanks for the suggestion.

KellyC|||Sorry for the crosspost again, but thought I would post my resolution here as well...

OK, as it often turns out, the problem is not always what it seems from the lovely and informative error messages. The unclosed quote was actually in the second query in the transaction. This came to light for me when I started playing with trying to submit a string with a single quote in it and noticed ColdFusion MX does not display the text of an unsucessful query in the debug. (Personally I consider this a deficiency of MX in case any Macromedia folks want my 2 cents).

So... long story short I realized it wasn't the insert query that was throwing the error and fixed the offending query and it now works like a charm.

Thanks again,
KellyC|||Nice of you to post the resolution. This is not meant to be an "I told you so," but it definitely points out an instance where SQL Profiler could have helped to narrow down the cause. Congrats for solving the problem.

Regards,

hmscott

Database Engine Worker thread pool : Queue length

Hello,
When the "Max Worker Threads" parameter is lower than the number of client
connections, SQL Server pools the worker threads. Is there a performance
counter to see the worker thread pool queue length (connections waiting a
thread to handle requests)?
TIA.There is Server Work Queues - Queue Length counter , which gives the
current length of the server queue related to this CPU>.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
> Hello,
> When the "Max Worker Threads" parameter is lower than the number of client
> connections, SQL Server pools the worker threads. Is there a performance
> counter to see the worker thread pool queue length (connections waiting a
> thread to handle requests)?
> TIA.
>|||Is this related to SQL Server ? What I want to know is the number of "work
items" (SQL Commands) waiting to be dispatched to the thread pool. It gives
an accurate value of the amout of work requested, wich can be used to
properly size the number of Threads/CPU needed to handle the workload
without eating 100% of the CPU.
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt
.com...
> There is Server Work Queues - Queue Length counter , which gives the
> current length of the server queue related to this CPU>.
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
> news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
>|||If it's SQL2005, something like the following may be useful:
select
scheduler_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
load_factor
from sys.dm_os_schedulers
where scheduler_id < 255
Pay attention to the work_queue_count value.
Linchi
"Olivier Matrot" wrote:

> Is this related to SQL Server ? What I want to know is the number of "work
> items" (SQL Commands) waiting to be dispatched to the thread pool. It give
s
> an accurate value of the amout of work requested, wich can be used to
> properly size the number of Threads/CPU needed to handle the workload
> without eating 100% of the CPU.
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt
.com...
>
>|||Very interesting.
Thanks for that.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F08138DD-A908-4295-AEDA-DE39C9C745EF@.microsoft.com...[vbcol=seagreen]
> If it's SQL2005, something like the following may be useful:
> select
> scheduler_id,
> current_tasks_count,
> runnable_tasks_count,
> current_workers_count,
> active_workers_count,
> work_queue_count,
> load_factor
> from sys.dm_os_schedulers
> where scheduler_id < 255
> Pay attention to the work_queue_count value.
> Linchi
> "Olivier Matrot" wrote:
>|||What would be the command for SQL 2000 i like to monitor this aswell.
How often to run say once hour
Thanks

Database Engine Worker thread pool : Queue length

Hello,
When the "Max Worker Threads" parameter is lower than the number of client
connections, SQL Server pools the worker threads. Is there a performance
counter to see the worker thread pool queue length (connections waiting a
thread to handle requests)?
TIA.There is Server Work Queues - Queue Length counter , which gives the
current length of the server queue related to this CPU>.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
> Hello,
> When the "Max Worker Threads" parameter is lower than the number of client
> connections, SQL Server pools the worker threads. Is there a performance
> counter to see the worker thread pool queue length (connections waiting a
> thread to handle requests)?
> TIA.
>|||Is this related to SQL Server ? What I want to know is the number of "work
items" (SQL Commands) waiting to be dispatched to the thread pool. It gives
an accurate value of the amout of work requested, wich can be used to
properly size the number of Threads/CPU needed to handle the workload
without eating 100% of the CPU.
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
> There is Server Work Queues - Queue Length counter , which gives the
> current length of the server queue related to this CPU>.
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
> news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
>> Hello,
>> When the "Max Worker Threads" parameter is lower than the number of
>> client
>> connections, SQL Server pools the worker threads. Is there a performance
>> counter to see the worker thread pool queue length (connections waiting a
>> thread to handle requests)?
>> TIA.
>>
>|||If it's SQL2005, something like the following may be useful:
select
scheduler_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
load_factor
from sys.dm_os_schedulers
where scheduler_id < 255
Pay attention to the work_queue_count value.
Linchi
"Olivier Matrot" wrote:
> Is this related to SQL Server ? What I want to know is the number of "work
> items" (SQL Commands) waiting to be dispatched to the thread pool. It gives
> an accurate value of the amout of work requested, wich can be used to
> properly size the number of Threads/CPU needed to handle the workload
> without eating 100% of the CPU.
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
> > There is Server Work Queues - Queue Length counter , which gives the
> > current length of the server queue related to this CPU>.
> > --
> > Jack Vamvas
> > ___________________________________
> > Receive free SQL tips - www.ciquery.com/sqlserver.htm
> > ___________________________________
> >
> >
> > "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
> > news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
> >> Hello,
> >> When the "Max Worker Threads" parameter is lower than the number of
> >> client
> >> connections, SQL Server pools the worker threads. Is there a performance
> >> counter to see the worker thread pool queue length (connections waiting a
> >> thread to handle requests)?
> >> TIA.
> >>
> >>
> >
> >
>
>|||Very interesting.
Thanks for that.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F08138DD-A908-4295-AEDA-DE39C9C745EF@.microsoft.com...
> If it's SQL2005, something like the following may be useful:
> select
> scheduler_id,
> current_tasks_count,
> runnable_tasks_count,
> current_workers_count,
> active_workers_count,
> work_queue_count,
> load_factor
> from sys.dm_os_schedulers
> where scheduler_id < 255
> Pay attention to the work_queue_count value.
> Linchi
> "Olivier Matrot" wrote:
>> Is this related to SQL Server ? What I want to know is the number of
>> "work
>> items" (SQL Commands) waiting to be dispatched to the thread pool. It
>> gives
>> an accurate value of the amout of work requested, wich can be used to
>> properly size the number of Threads/CPU needed to handle the workload
>> without eating 100% of the CPU.
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
>> > There is Server Work Queues - Queue Length counter , which gives
>> > the
>> > current length of the server queue related to this CPU>.
>> > --
>> > Jack Vamvas
>> > ___________________________________
>> > Receive free SQL tips - www.ciquery.com/sqlserver.htm
>> > ___________________________________
>> >
>> >
>> > "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
>> > news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
>> >> Hello,
>> >> When the "Max Worker Threads" parameter is lower than the number of
>> >> client
>> >> connections, SQL Server pools the worker threads. Is there a
>> >> performance
>> >> counter to see the worker thread pool queue length (connections
>> >> waiting a
>> >> thread to handle requests)?
>> >> TIA.
>> >>
>> >>
>> >
>> >
>>|||What would be the command for SQL 2000 i like to monitor this aswell.
How often to run say once hour
Thanks

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
SimonIt would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon

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 Diagrams : Showing Relations Column to Column

Hi to all,

As I am going to deal with a huge number of database tables, I thought that drawing their diagrams will be the most professional way of keeping track of what I am doing. So In Enterprise Manager using the Wizard I made it to be drawn nicely.

But I have a problem now. I see that the relations defined among the tables are represented correctly but the line connecting the two table is drawn randomly. I mean the starting point of the line doesnt start from the column having the primary key and doesnt end near to the column having the foreign key. Viewing my diagram I want to see the lines to start and end showing the 2 related key columns.

Is there a way to accomplish this like setting an option, clicking somewhere or should I try to drag the lines to manually?

Thanks in advance

The quick way is to generate the create table statements of the tables and you will see the constraints and if you have constraints you may also check if you have DRI(declarative referential integrity). You can have constraints without DRI so check. Hope this helps.

Database design question with time

Im designing a database where a user enters the date and the number of
hours and minutes he worked for the day..now i can do this

Workdate small date
Hours integer
Minutes integer

But then would I have to have the front end know that when adding up
the hours and minutes for the week that 60 minutes = 1 hour or is
there some way to do this in the database?

thanks

-JimJim (jim.ferris@.motorola.com) writes:
> Im designing a database where a user enters the date and the number of
> hours and minutes he worked for the day..now i can do this
> Workdate small date
> Hours integer
> Minutes integer
>
> But then would I have to have the front end know that when adding up
> the hours and minutes for the week that 60 minutes = 1 hour or is
> there some way to do this in the database?

You could have a computed column with the forumla Minutes + 60 * Hours:

CREATE TABLE workhours (
userid userid_type NOT NULL,
workdate smalldatetime NOT NULL,
hours tinyint NOT NULL,
minutes tinyint NOT NULL,
worked_minutes AS minutes + 60 * hours,
CONSTRAINT pk_workhours(userid, workdate))

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> ... database where a user enters the date and the number of hours
and minutes he worked for the day..<<

Use a duration instead of trying to create your own temporal datatype
system.

CREATE TABLE Timecard
(emp_id INTEGER NOT NULL,
start_time DATETIME NOT NULL,
finish_time DATETIME, -- null means still active
PRIMARY KEY (emp_id, start_time));

You can now use BETWEEN predicates and a calendar table.|||Thanks Ill try that

-Jim

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9474F24297CBCYazorman@.127.0.0.1>...
> Jim (jim.ferris@.motorola.com) writes:
> > Im designing a database where a user enters the date and the number of
> > hours and minutes he worked for the day..now i can do this
> > Workdate small date
> > Hours integer
> > Minutes integer
> > But then would I have to have the front end know that when adding up
> > the hours and minutes for the week that 60 minutes = 1 hour or is
> > there some way to do this in the database?
> You could have a computed column with the forumla Minutes + 60 * Hours:
> CREATE TABLE workhours (
> userid userid_type NOT NULL,
> workdate smalldatetime NOT NULL,
> hours tinyint NOT NULL,
> minutes tinyint NOT NULL,
> worked_minutes AS minutes + 60 * hours,
> CONSTRAINT pk_workhours(userid, workdate))

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 Question

I have several SQL databases that I am going to create that will all share a
basic part number/part description table, as well as a common customer table.
What is the best way to design this? Should I have a central database that
contains the master tables and then have all of the other tables link to this
table?, or should I set each database with their own copy of this part
number/customer table so they can link to those instead (and then setup some
type of replication)?
I just was not sure if it was good practice to be constantly joining tables
from two different databases each time a SELECT statement is run.
Any help would be appreciated.
Thank you.
Scott Fox, MCAD
Joining across databases is no problem (across instances can be, performancewise). You cannot define
foreign keys, though. So data integrity (RI) has to be done using triggers. There's no best way,
though. Consider advantages and disadvantages for both approaches and use the one that suits you
best. And handle the disadvantages that the solution has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:F9BC8DAB-6122-4BC8-AA45-90DCED9EEE79@.microsoft.com...
>I have several SQL databases that I am going to create that will all share a
> basic part number/part description table, as well as a common customer table.
> What is the best way to design this? Should I have a central database that
> contains the master tables and then have all of the other tables link to this
> table?, or should I set each database with their own copy of this part
> number/customer table so they can link to those instead (and then setup some
> type of replication)?
> I just was not sure if it was good practice to be constantly joining tables
> from two different databases each time a SELECT statement is run.
> Any help would be appreciated.
> Thank you.
> Scott Fox, MCAD

Database Design Question

I have several SQL databases that I am going to create that will all share a
basic part number/part description table, as well as a common customer table
.
What is the best way to design this? Should I have a central database that
contains the master tables and then have all of the other tables link to thi
s
table?, or should I set each database with their own copy of this part
number/customer table so they can link to those instead (and then setup some
type of replication)?
I just was not sure if it was good practice to be constantly joining tables
from two different databases each time a SELECT statement is run.
Any help would be appreciated.
Thank you.
Scott Fox, MCADJoining across databases is no problem (across instances can be, performance
wise). You cannot define
foreign keys, though. So data integrity (RI) has to be done using triggers.
There's no best way,
though. Consider advantages and disadvantages for both approaches and use th
e one that suits you
best. And handle the disadvantages that the solution has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:F9BC8DAB-6122-4BC8-AA45-90DCED9EEE79@.microsoft.com...
>I have several SQL databases that I am going to create that will all share
a
> basic part number/part description table, as well as a common customer tab
le.
> What is the best way to design this? Should I have a central database tha
t
> contains the master tables and then have all of the other tables link to t
his
> table?, or should I set each database with their own copy of this part
> number/customer table so they can link to those instead (and then setup so
me
> type of replication)?
> I just was not sure if it was good practice to be constantly joining table
s
> from two different databases each time a SELECT statement is run.
> Any help would be appreciated.
> Thank you.
> Scott Fox, MCAD

Database Design Question

I have several SQL databases that I am going to create that will all share a
basic part number/part description table, as well as a common customer table.
What is the best way to design this? Should I have a central database that
contains the master tables and then have all of the other tables link to this
table?, or should I set each database with their own copy of this part
number/customer table so they can link to those instead (and then setup some
type of replication)?
I just was not sure if it was good practice to be constantly joining tables
from two different databases each time a SELECT statement is run.
Any help would be appreciated.
Thank you.
Scott Fox, MCADJoining across databases is no problem (across instances can be, performancewise). You cannot define
foreign keys, though. So data integrity (RI) has to be done using triggers. There's no best way,
though. Consider advantages and disadvantages for both approaches and use the one that suits you
best. And handle the disadvantages that the solution has.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:F9BC8DAB-6122-4BC8-AA45-90DCED9EEE79@.microsoft.com...
>I have several SQL databases that I am going to create that will all share a
> basic part number/part description table, as well as a common customer table.
> What is the best way to design this? Should I have a central database that
> contains the master tables and then have all of the other tables link to this
> table?, or should I set each database with their own copy of this part
> number/customer table so they can link to those instead (and then setup some
> type of replication)?
> I just was not sure if it was good practice to be constantly joining tables
> from two different databases each time a SELECT statement is run.
> Any help would be appreciated.
> Thank you.
> Scott Fox, MCAD

Database Design Problem

I have to keep detail of detail gender based DB like this where there may be
a number of incidents per state
Create table gender
(
State_id
Male_killed
Male_injured
male_affected
Female_killed
female_affected
female_injured
Child_killed
Child_injured
child_affected
Date
Incident_type
remarks
)
All these are numbers and can be 2 male_killed and 2 female injured in the
same incident
so what is the best way to desing a DB for this type I cannot think anything
other than putting these all into one table.
Depending on your needs you might come up with something like this...
Create table gender (gender_cd char(1) not null, gender_name varchar(8) not
null) --m, male f, female
Create table persontype( person_cd char(1) not null, person_name varchar(8)
not null) -A, Adult C,Child
Create table incidenttype(incident_id int identity(1,1) not null,
incident_name varchar(24) not null) --whatever
Create table resulttype(result_cd char(1) not null, resultname varchar(12)
not null) -- K, Killed I, Injured A, Affected
Create table states(state_cd char(2) not null, statename varchar(24) not
null) --NC, North Carolina
Create table things(id int identity(1,1) not null,
state_cd char(2) not null references
states(state_cd),
incident_id int not null references
incidenttype(incident_id),
thing_dt datetime not null,
remarks varchar(2000) null)
Create table thingdetail (id int identity(1,1) not null,
thing int not null references things(id),
gender_cd char(1) not null references
gender(gender_cd),
person_cd char(1) not null references
persontype(person_cd),
result_cd char(1) not null references
resulttype(result_cd),
NumberOfPeople int not null)
Although I did this quick and dirty, I think it is in third normal form...
You may not need this much normalization, but this is a beginning..
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
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:e$dztHBWEHA.2408@.tk2msftngp13.phx.gbl...
> I have to keep detail of detail gender based DB like this where there may
be
> a number of incidents per state
> Create table gender
> (
> State_id
> Male_killed
> Male_injured
> male_affected
> Female_killed
> female_affected
> female_injured
> Child_killed
> Child_injured
> child_affected
> Date
> Incident_type
> remarks
> )
> All these are numbers and can be 2 male_killed and 2 female injured in the
> same incident
> so what is the best way to desing a DB for this type I cannot think
anything
> other than putting these all into one table.
>
|||Based on your narrative and sample schema, one has to guess your conceptual
model, the attributes and the functional dependencies that exist among them
before suggesting something meaningful. In general, requesting solutions for
design problems in newsgroup may not be a worthwhile effort since there is a
fair chance that the problem could be misunderstood, miscommunicated or
misread and so are the suggested solutions.
With such a caveat, here is a try:
CREATE TABLE Incidents(
Incident_id INT NOT NULL PRIMARY KEY,
State CHAR(2) NOT NULL
REFERENCES States( State ),
Incident_date DATETIME NOT NULL,
Inident_type INT NOT NULL CHECK (...) -- or reference it
Remarks VARCHAR(8000) NOT NULL
DEFAULT '--NA--'
UNIQUE ( State, Incident_date, Inident_type) );
CREATE TABLE Details (
Incident_id INT NOT NULL
REFERENCES Incidents ( Incident_id )
Outcome VARCHAR(10) NOT NULL
CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
'Unknown' ) ) -- or reference it
Person_type VARCHAR(6) NOT NULL
CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
-- or reference it
Incident_count INT NOT NULL DEFAULT(0)
PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
The check constraint values, if are of a higher number, can be substituted
with a referenced table with corresponding foriegn key constraint.
Anith
|||thnaks
but the only problem is that I have a file with 1000 records that comes in
and I am importing through excel so any suggestions asto how to handle it if
I am to adopt the above design considerations
Anup Help is awaited
thanks
anup
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uRQ16uFWEHA.2928@.tk2msftngp13.phx.gbl...
> Based on your narrative and sample schema, one has to guess your
conceptual
> model, the attributes and the functional dependencies that exist among
them
> before suggesting something meaningful. In general, requesting solutions
for
> design problems in newsgroup may not be a worthwhile effort since there is
a
> fair chance that the problem could be misunderstood, miscommunicated or
> misread and so are the suggested solutions.
> With such a caveat, here is a try:
> CREATE TABLE Incidents(
> Incident_id INT NOT NULL PRIMARY KEY,
> State CHAR(2) NOT NULL
> REFERENCES States( State ),
> Incident_date DATETIME NOT NULL,
> Inident_type INT NOT NULL CHECK (...) -- or reference it
> Remarks VARCHAR(8000) NOT NULL
> DEFAULT '--NA--'
> UNIQUE ( State, Incident_date, Inident_type) );
> CREATE TABLE Details (
> Incident_id INT NOT NULL
> REFERENCES Incidents ( Incident_id )
> Outcome VARCHAR(10) NOT NULL
> CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
> 'Unknown' ) ) -- or reference it
> Person_type VARCHAR(6) NOT NULL
> CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
> -- or reference it
> Incident_count INT NOT NULL DEFAULT(0)
> PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
> The check constraint values, if are of a higher number, can be substituted
> with a referenced table with corresponding foriegn key constraint.
> --
> Anith
>
|||You have a couple of workarounds. Have the data Bulk inserted or DTS'ed to a
staging table with the same structure as the spreadsheet. Then you can write
up a simple script to populate the data into well normalized tables as
mentioned.
Another option is to use a client programming language like C or VB to read
the file & split the data accordingly. Then you can do the inserts directly
into the tables from the program.
Again, to reiterate the general sentiment, the table structures posted as
the response to your initial post is just a guildline and there is little
chance that your business model conforms to the assumptions we made while
posting such a schema.
Anith

Database Design Problem

I have to keep detail of detail gender based DB like this where there may be
a number of incidents per state
Create table gender
(
State_id
Male_killed
Male_injured
male_affected
Female_killed
female_affected
female_injured
Child_killed
Child_injured
child_affected
Date
Incident_type
remarks
)
All these are numbers and can be 2 male_killed and 2 female injured in the
same incident
so what is the best way to desing a DB for this type I cannot think anything
other than putting these all into one table.Depending on your needs you might come up with something like this...
Create table gender (gender_cd char(1) not null, gender_name varchar(8) not
null) --m, male f, female
Create table persontype( person_cd char(1) not null, person_name varchar(8)
not null) -A, Adult C,Child
Create table incidenttype(incident_id int identity(1,1) not null,
incident_name varchar(24) not null) --whatever
Create table resulttype(result_cd char(1) not null, resultname varchar(12)
not null) -- K, Killed I, Injured A, Affected
Create table states(state_cd char(2) not null, statename varchar(24) not
null) --NC, North Carolina
Create table things(id int identity(1,1) not null,
state_cd char(2) not null references
states(state_cd),
incident_id int not null references
incidenttype(incident_id),
thing_dt datetime not null,
remarks varchar(2000) null)
Create table thingdetail (id int identity(1,1) not null,
thing int not null references things(id),
gender_cd char(1) not null references
gender(gender_cd),
person_cd char(1) not null references
persontype(person_cd),
result_cd char(1) not null references
resulttype(result_cd),
NumberOfPeople int not null)
Although I did this quick and dirty, I think it is in third normal form...
You may not need this much normalization, but this is a beginning..
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
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:e$dztHBWEHA.2408@.tk2msftngp13.phx.gbl...
> I have to keep detail of detail gender based DB like this where there may
be
> a number of incidents per state
> Create table gender
> (
> State_id
> Male_killed
> Male_injured
> male_affected
> Female_killed
> female_affected
> female_injured
> Child_killed
> Child_injured
> child_affected
> Date
> Incident_type
> remarks
> )
> All these are numbers and can be 2 male_killed and 2 female injured in the
> same incident
> so what is the best way to desing a DB for this type I cannot think
anything
> other than putting these all into one table.
>|||Based on your narrative and sample schema, one has to guess your conceptual
model, the attributes and the functional dependencies that exist among them
before suggesting something meaningful. In general, requesting solutions for
design problems in newsgroup may not be a worthwhile effort since there is a
fair chance that the problem could be misunderstood, miscommunicated or
misread and so are the suggested solutions.
With such a caveat, here is a try:
CREATE TABLE Incidents(
Incident_id INT NOT NULL PRIMARY KEY,
State CHAR(2) NOT NULL
REFERENCES States( State ),
Incident_date DATETIME NOT NULL,
Inident_type INT NOT NULL CHECK (...) -- or reference it
Remarks VARCHAR(8000) NOT NULL
DEFAULT '--NA--'
UNIQUE ( State, Incident_date, Inident_type) );
CREATE TABLE Details (
Incident_id INT NOT NULL
REFERENCES Incidents ( Incident_id )
Outcome VARCHAR(10) NOT NULL
CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
'Unknown' ) ) -- or reference it
Person_type VARCHAR(6) NOT NULL
CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
-- or reference it
Incident_count INT NOT NULL DEFAULT(0)
PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
The check constraint values, if are of a higher number, can be substituted
with a referenced table with corresponding foriegn key constraint.
Anith|||thnaks
but the only problem is that I have a file with 1000 records that comes in
and I am importing through excel so any suggestions asto how to handle it if
I am to adopt the above design considerations
Anup Help is awaited
thanks
anup
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uRQ16uFWEHA.2928@.tk2msftngp13.phx.gbl...
> Based on your narrative and sample schema, one has to guess your
conceptual
> model, the attributes and the functional dependencies that exist among
them
> before suggesting something meaningful. In general, requesting solutions
for
> design problems in newsgroup may not be a worthwhile effort since there is
a
> fair chance that the problem could be misunderstood, miscommunicated or
> misread and so are the suggested solutions.
> With such a caveat, here is a try:
> CREATE TABLE Incidents(
> Incident_id INT NOT NULL PRIMARY KEY,
> State CHAR(2) NOT NULL
> REFERENCES States( State ),
> Incident_date DATETIME NOT NULL,
> Inident_type INT NOT NULL CHECK (...) -- or reference it
> Remarks VARCHAR(8000) NOT NULL
> DEFAULT '--NA--'
> UNIQUE ( State, Incident_date, Inident_type) );
> CREATE TABLE Details (
> Incident_id INT NOT NULL
> REFERENCES Incidents ( Incident_id )
> Outcome VARCHAR(10) NOT NULL
> CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
> 'Unknown' ) ) -- or reference it
> Person_type VARCHAR(6) NOT NULL
> CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
> -- or reference it
> Incident_count INT NOT NULL DEFAULT(0)
> PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
> The check constraint values, if are of a higher number, can be substituted
> with a referenced table with corresponding foriegn key constraint.
> --
> Anith
>|||You have a couple of workarounds. Have the data Bulk inserted or DTS'ed to a
staging table with the same structure as the spreadsheet. Then you can write
up a simple script to populate the data into well normalized tables as
mentioned.
Another option is to use a client programming language like C or VB to read
the file & split the data accordingly. Then you can do the inserts directly
into the tables from the program.
Again, to reiterate the general sentiment, the table structures posted as
the response to your initial post is just a guildline and there is little
chance that your business model conforms to the assumptions we made while
posting such a schema.
Anith

Database Design Problem

I have to keep detail of detail gender based DB like this where there may be
a number of incidents per state
Create table gender
(
State_id
Male_killed
Male_injured
male_affected
Female_killed
female_affected
female_injured
Child_killed
Child_injured
child_affected
Date
Incident_type
remarks
)
All these are numbers and can be 2 male_killed and 2 female injured in the
same incident
so what is the best way to desing a DB for this type I cannot think anything
other than putting these all into one table.Depending on your needs you might come up with something like this...
Create table gender (gender_cd char(1) not null, gender_name varchar(8) not
null) --m, male f, female
Create table persontype( person_cd char(1) not null, person_name varchar(8)
not null) -A, Adult C,Child
Create table incidenttype(incident_id int identity(1,1) not null,
incident_name varchar(24) not null) --whatever
Create table resulttype(result_cd char(1) not null, resultname varchar(12)
not null) -- K, Killed I, Injured A, Affected
Create table states(state_cd char(2) not null, statename varchar(24) not
null) --NC, North Carolina
Create table things(id int identity(1,1) not null,
state_cd char(2) not null references
states(state_cd),
incident_id int not null references
incidenttype(incident_id),
thing_dt datetime not null,
remarks varchar(2000) null)
Create table thingdetail (id int identity(1,1) not null,
thing int not null references things(id),
gender_cd char(1) not null references
gender(gender_cd),
person_cd char(1) not null references
persontype(person_cd),
result_cd char(1) not null references
resulttype(result_cd),
NumberOfPeople int not null)
Although I did this quick and dirty, I think it is in third normal form...
You may not need this much normalization, but this is a beginning..
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
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:e$dztHBWEHA.2408@.tk2msftngp13.phx.gbl...
> I have to keep detail of detail gender based DB like this where there may
be
> a number of incidents per state
> Create table gender
> (
> State_id
> Male_killed
> Male_injured
> male_affected
> Female_killed
> female_affected
> female_injured
> Child_killed
> Child_injured
> child_affected
> Date
> Incident_type
> remarks
> )
> All these are numbers and can be 2 male_killed and 2 female injured in the
> same incident
> so what is the best way to desing a DB for this type I cannot think
anything
> other than putting these all into one table.
>|||Based on your narrative and sample schema, one has to guess your conceptual
model, the attributes and the functional dependencies that exist among them
before suggesting something meaningful. In general, requesting solutions for
design problems in newsgroup may not be a worthwhile effort since there is a
fair chance that the problem could be misunderstood, miscommunicated or
misread and so are the suggested solutions.
With such a caveat, here is a try:
CREATE TABLE Incidents(
Incident_id INT NOT NULL PRIMARY KEY,
State CHAR(2) NOT NULL
REFERENCES States( State ),
Incident_date DATETIME NOT NULL,
Inident_type INT NOT NULL CHECK (...) -- or reference it
Remarks VARCHAR(8000) NOT NULL
DEFAULT '--NA--'
UNIQUE ( State, Incident_date, Inident_type) );
CREATE TABLE Details (
Incident_id INT NOT NULL
REFERENCES Incidents ( Incident_id )
Outcome VARCHAR(10) NOT NULL
CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
'Unknown' ) ) -- or reference it
Person_type VARCHAR(6) NOT NULL
CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
-- or reference it
Incident_count INT NOT NULL DEFAULT(0)
PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
The check constraint values, if are of a higher number, can be substituted
with a referenced table with corresponding foriegn key constraint.
--
Anith|||thnaks
but the only problem is that I have a file with 1000 records that comes in
and I am importing through excel so any suggestions asto how to handle it if
I am to adopt the above design considerations
Anup Help is awaited
thanks
anup
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uRQ16uFWEHA.2928@.tk2msftngp13.phx.gbl...
> Based on your narrative and sample schema, one has to guess your
conceptual
> model, the attributes and the functional dependencies that exist among
them
> before suggesting something meaningful. In general, requesting solutions
for
> design problems in newsgroup may not be a worthwhile effort since there is
a
> fair chance that the problem could be misunderstood, miscommunicated or
> misread and so are the suggested solutions.
> With such a caveat, here is a try:
> CREATE TABLE Incidents(
> Incident_id INT NOT NULL PRIMARY KEY,
> State CHAR(2) NOT NULL
> REFERENCES States( State ),
> Incident_date DATETIME NOT NULL,
> Inident_type INT NOT NULL CHECK (...) -- or reference it
> Remarks VARCHAR(8000) NOT NULL
> DEFAULT '--NA--'
> UNIQUE ( State, Incident_date, Inident_type) );
> CREATE TABLE Details (
> Incident_id INT NOT NULL
> REFERENCES Incidents ( Incident_id )
> Outcome VARCHAR(10) NOT NULL
> CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
> 'Unknown' ) ) -- or reference it
> Person_type VARCHAR(6) NOT NULL
> CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
> -- or reference it
> Incident_count INT NOT NULL DEFAULT(0)
> PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
> The check constraint values, if are of a higher number, can be substituted
> with a referenced table with corresponding foriegn key constraint.
> --
> Anith
>|||You have a couple of workarounds. Have the data Bulk inserted or DTS'ed to a
staging table with the same structure as the spreadsheet. Then you can write
up a simple script to populate the data into well normalized tables as
mentioned.
Another option is to use a client programming language like C or VB to read
the file & split the data accordingly. Then you can do the inserts directly
into the tables from the program.
Again, to reiterate the general sentiment, the table structures posted as
the response to your initial post is just a guildline and there is little
chance that your business model conforms to the assumptions we made while
posting such a schema.
--
Anith

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...