Determining Security: groups

Showing posts with label groups. Show all posts

Tuesday, March 27, 2012

Determine Report Permissions with T-SQL

Is there a way, using the system tables in the ReportServer or
ReportServerTempDB to determine which Active Directory groups have access to
which reports. I want to document what I would otherwise have to go
report-by-report in the Report Manager screen to see. I've looked at
several of the system tables (Users, DataSource, Policies, Roles,
ConfigurationInfo, Catalog) but have had no luck.You need to use the GetPolicies and SetPolicies methods on the web service.
You don't want to do anything directly to the database.
Here's a code snippet giving you an idea of how to use these methods:
private bool AddUserToFolderPolicy(string folder, string user, ref string
errMessage)
{
try
{
//Get the Browser role
Role[]roles = m_ReportingService.ListRoles();
Role browserRole = new Role();
foreach (Role r in roles)
{
if (r.Name == "Browser") browserRole = r;
break;
}
Role[] policyRoles = new Role[1];
policyRoles[0] = new Role();
policyRoles[0] =browserRole;
//Get the current policies of the folder in question
string path = "/" + folder;
bool inheritParent = false;
Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
inheritParent);
//If the user is currently in the current policy set just return
for(int i=0;i<currentPolicies.Length;i++)
if(currentPolicies[i].GroupUserName == user)
return true;
//Create the new policy array and add the new user
ArrayList arrPolicies = new ArrayList(currentPolicies);
Policy p = new Policy();
p.GroupUserName = user;
p.Roles = policyRoles;
arrPolicies.Add(p);
Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
//Set the policies
m_ReportingService.SetPolicies(path,finalPolicies);
}
catch (Exception e)
{
errMessage = e.Message;
return false;
}
return true;
}
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> Is there a way, using the system tables in the ReportServer or
> ReportServerTempDB to determine which Active Directory groups have access
> to
> which reports. I want to document what I would otherwise have to go
> report-by-report in the Report Manager screen to see. I've looked at
> several of the system tables (Users, DataSource, Policies, Roles,
> ConfigurationInfo, Catalog) but have had no luck.
>|||Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
developer and need to accomplish this task in T-SQL. I don't really want to
"do" anything to the tables, I just want to "get" something from them, just
as I would a system table in master or anywhere else in SQL Server. Does
anyone ([MSFT] people perhaps?) know if this is possible or if there is any
documentation on how to navigate these tables?
"Adrian M." wrote:
> You need to use the GetPolicies and SetPolicies methods on the web service.
> You don't want to do anything directly to the database.
> Here's a code snippet giving you an idea of how to use these methods:
> private bool AddUserToFolderPolicy(string folder, string user, ref string
> errMessage)
> {
> try
> {
> //Get the Browser role
> Role[]roles = m_ReportingService.ListRoles();
> Role browserRole = new Role();
> foreach (Role r in roles)
> {
> if (r.Name == "Browser") browserRole = r;
> break;
> }
> Role[] policyRoles = new Role[1];
> policyRoles[0] = new Role();
> policyRoles[0] =browserRole;
> //Get the current policies of the folder in question
> string path = "/" + folder;
> bool inheritParent = false;
> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
> inheritParent);
> //If the user is currently in the current policy set just return
> for(int i=0;i<currentPolicies.Length;i++)
> if(currentPolicies[i].GroupUserName == user)
> return true;
> //Create the new policy array and add the new user
> ArrayList arrPolicies = new ArrayList(currentPolicies);
> Policy p = new Policy();
> p.GroupUserName = user;
> p.Roles = policyRoles;
> arrPolicies.Add(p);
> Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
> //Set the policies
> m_ReportingService.SetPolicies(path,finalPolicies);
> }
> catch (Exception e)
> {
> errMessage = e.Message;
> return false;
> }
> return true;
> }
>
> --
> Adrian M.
> MCP
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> > Is there a way, using the system tables in the ReportServer or
> > ReportServerTempDB to determine which Active Directory groups have access
> > to
> > which reports. I want to document what I would otherwise have to go
> > report-by-report in the Report Manager screen to see. I've looked at
> > several of the system tables (Users, DataSource, Policies, Roles,
> > ConfigurationInfo, Catalog) but have had no luck.
> >
>
>|||Microsoft doesn't support directly access to the Report Server DB (including
queries). Supported access is through the tools provided (Report Manager,
web service, etc...)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
--
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>|||Even if we were to document the tables, there is no way to use TSQL to get
this (without some extended SPs). We use Windows APIs to resolve group
membership and determine effective permissions from the ACL we store in the
database.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>

Determine month from inside month row grouping

Hi guys,

i have a matrix that has two row groups, the first is month, the next is year. This is for year on year reporting, so the first two cells in each row are like this:

April 2005

April 2006

April 2007

May 2005

May 2006

May 2007

etc

in one of the columns of the matrix, i need to calculate a "per hour" figure, so i need to determine how many hours are in the month that groups this row.

So what i need to know is: how can i tell what month this row is? I don't care if the month is represented as text ("April") or an int (4), because the pseudo code for the expression will be:

<monthly dollar amount> / (24 * DateDiff("d", CDate("2006-<this month>-01"), DateAdd("M", CDate("2006-<this month>-01"), 1)))

Thanks for any suggestions!

sluggy

Duh, that was a stupid question... the answer was just to use the month dataset member as per usual This was the final expression for getting the number of hours in the month:

24 * DateDiff("d", CDate("2006-" + Fields!Calendar_Month.Value + "01"), DateAdd("M", 1, CDate("2006-" + Fields!Calendar_Month.Value + "01")))

Thursday, March 22, 2012

Detecting Expanded Groups, etc...

This is a multi-part message in MIME format.
--=_NextPart_000_0006_01C75F65.85B0F190
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Is it possible to base an expression on whether a group is expanded or = not?
I want to show totals in the header, unless the group is expanded. In = that case, I want to show totals in the footer.
This would have to be dynamic, but i don't know if you have that kind of = access to the report objects when the report is being displayed. = Thanks.
J
--=_NextPart_000_0006_01C75F65.85B0F190
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Is it possible to base an expression on = whether a group is expanded or not?

I want to show totals in the header, = unless the group is expanded. In that case, I want to show totals in the footer.

This would have to be dynamic, but i = don't know if you have that kind of access to the report objects when the report is = being displayed. Thanks.

J
--=_NextPart_000_0006_01C75F65.85B0F190--On Mar 5, 9:33 pm, <rlrc...@.newsgroups.nospam> wrote:
> Is it possible to base an expression on whether a group is expanded or not?
> I want to show totals in the header, unless the group is expanded. In that case, I want to show totals in the footer.
> This would have to be dynamic, but i don't know if you have that kind of access to the report objects when the report is being displayed. Thanks.
> J
As far as I know, this functionality does not exist.
Regards,
Enrique Martinez
Sr. SQL Server Developersql

Monday, March 19, 2012

Details grouping

Hi,
what is the details grouping for? Like, we have the ability to set up
groups, why do we also need details grouping?
thanks
MattMatt,
You may not want to show the lowest level of detail returned by your
query, say you returned order lines in your query, but you only want to
show order totals. Instead of having an order group and a detail
section that needs to be hidden, you could just have detail section
grouped on order.
You may say that's actually quite inefficient as you should change the
query to just return order totals, and you'd be right, but consider if
that grouping was parameter driven. You might want to group on sales
rep, customer or product. This is when this scenario makes sense.
Hope that helps.
Cheers
Chris
Matt wrote:
> Hi,
> what is the details grouping for? Like, we have the ability to set up
> groups, why do we also need details grouping?
> thanks
> Matt|||Ah I see. Thanks Chris - for taking the time to write a nice clear
explanation
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:e6XfTURiFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Matt,
> You may not want to show the lowest level of detail returned by your
> query, say you returned order lines in your query, but you only want to
> show order totals. Instead of having an order group and a detail
> section that needs to be hidden, you could just have detail section
> grouped on order.
> You may say that's actually quite inefficient as you should change the
> query to just return order totals, and you'd be right, but consider if
> that grouping was parameter driven. You might want to group on sales
> rep, customer or product. This is when this scenario makes sense.
> Hope that helps.
> Cheers
> Chris
>
> Matt wrote:
> > Hi,
> >
> > what is the details grouping for? Like, we have the ability to set up
> > groups, why do we also need details grouping?
> >
> > thanks
> >
> > Matt
>

Details Grouping

Can a table have more then one detail group? I need 4 detail groups show from a parent group. They all come from the same query dataset. Or is the a better way to do this?

Thanks

You can't have multiple detail groups in a table. However, you can add more rows for the parent group. Then in each of the rows, add a nested table/list to show the details.

Saturday, February 25, 2012

Designing groups in multiple columns

hello,
> i am designing a grade report and i got a problem, the problem is that me divided the report into two columns, me grouped the records on the basis of semesters. now i want to show the first semester's records in first column and second semester's records in second column, then third semester's records in first column and so on.. but it doesn't hapens so. i want to set the layout of the group but there is not any option to set layout of the group.there is layout option for section but not for group. what me should do ? ? ?
iam desiginging one report for one student. each student have multiple semester and each semester consist of multiple subjects.I hace CR XI, go to details, section expert, layout with multiple columns, you should see a layout tab pop up in the upper right, click on it, you can control the direction the data flows as well as turn on, layout groups with multiple columns.

Design/Modeling books and or advise wanted.

I posted this question long ago to several groups but it didn't generate a
lot of interest so I thought I would try again.
I have been looking for books on database design and or modeling but can't
determine which method or approach would best suite my needs.
I guess this comes from the fact that I'm not sure what the difference is in
the different types of modeling and or design approaches, or for that matter
what possible methods are available. ER, UML ORM....are there others? Are
these different ways to model? Are there other methods? Why should I choose
one over the other?
I mean I'm just the db guy truing to design the best database for our
application. If you can also point out any good books to build up on your
suggestions it would be helpful.
Thanks,
CharlieThere are a couple of suggestions here
http://vyaskn.tripod.com/sqlbooks.htm#rdbms
I personally enjoyed Louis Davidson's "Professionsal SQL Server 2000
Database Design"
Recommendations from "Inside Microsoft SQL Server 2000" by Kalen Delaney:
(from post by B.P.Margolin)
An Introduction to Database Systems, 7th Edition, by C. J. Date
(Addison-Wesley, 1999). A new revision of a classic book, written by a giant
in the field, that covers general relational database concepts. A must-read
for everyone in the database industry.
Database Design for Mere Mortals by Michael J. Hernandez (Addison-Wesley,
1997). A very readable approach to the daunting task of designing a
relational database to solve real-world problems. The book is written in a
database-independent format, so the details can be applied to SQL Server as
well as to other RDMSs you might use.
Handbook of Relational Database Design by Candace C. Fleming and Barbara
Vonhalle (Addison-Wesley, 1988). A fine book that discusses general logical
database design and data modeling approaches and techniques.
SAMS Teach Yourself Microsoft SQL Server 2000 in 21 Days by Richard Waymire
and Rick Sawtell (SAMS Publishing, 2000). A good first book on SQL Server
2000 in tutorial format that gets readers up to speed quickly. One of the
coauthors is a program manager on the SQL Server development team. This
title includes self-test questions for each of the 21 "days."
Database: Principles, Programming, Performance by Patrick O'Neil (Morgan
Kaufmann Publishers, 1994). This thorough textbook provides excellent
introductory materials, so I've placed it high on the list. It also
carefully details a broad spectrum of database topics, including buffer
management and transaction semantics.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Charlie" <cdeaton@.corp.realcomp.com> wrote in message
news:%23$EQz$4hDHA.2296@.TK2MSFTNGP09.phx.gbl...
> I posted this question long ago to several groups but it didn't generate a
> lot of interest so I thought I would try again.
> I have been looking for books on database design and or modeling but can't
> determine which method or approach would best suite my needs.
> I guess this comes from the fact that I'm not sure what the difference is
in
> the different types of modeling and or design approaches, or for that
matter
> what possible methods are available. ER, UML ORM....are there others? Are
> these different ways to model? Are there other methods? Why should I
choose
> one over the other?
> I mean I'm just the db guy truing to design the best database for our
> application. If you can also point out any good books to build up on your
> suggestions it would be helpful.
> Thanks,
> Charlie
>|||Thanks Allen,
Can anyone else comment on approaches to design (ER,UML,ORM').
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eDIf6m%23hDHA.2512@.TK2MSFTNGP09.phx.gbl...
> There are a couple of suggestions here
> http://vyaskn.tripod.com/sqlbooks.htm#rdbms
> I personally enjoyed Louis Davidson's "Professionsal SQL Server 2000
> Database Design"
> Recommendations from "Inside Microsoft SQL Server 2000" by Kalen Delaney:
> (from post by B.P.Margolin)
> An Introduction to Database Systems, 7th Edition, by C. J. Date
> (Addison-Wesley, 1999). A new revision of a classic book, written by a
giant
> in the field, that covers general relational database concepts. A
must-read
> for everyone in the database industry.
> Database Design for Mere Mortals by Michael J. Hernandez (Addison-Wesley,
> 1997). A very readable approach to the daunting task of designing a
> relational database to solve real-world problems. The book is written in a
> database-independent format, so the details can be applied to SQL Server
as
> well as to other RDMSs you might use.
> Handbook of Relational Database Design by Candace C. Fleming and Barbara
> Vonhalle (Addison-Wesley, 1988). A fine book that discusses general
logical
> database design and data modeling approaches and techniques.
> SAMS Teach Yourself Microsoft SQL Server 2000 in 21 Days by Richard
Waymire
> and Rick Sawtell (SAMS Publishing, 2000). A good first book on SQL Server
> 2000 in tutorial format that gets readers up to speed quickly. One of the
> coauthors is a program manager on the SQL Server development team. This
> title includes self-test questions for each of the 21 "days."
> Database: Principles, Programming, Performance by Patrick O'Neil (Morgan
> Kaufmann Publishers, 1994). This thorough textbook provides excellent
> introductory materials, so I've placed it high on the list. It also
> carefully details a broad spectrum of database topics, including buffer
> management and transaction semantics.
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Charlie" <cdeaton@.corp.realcomp.com> wrote in message
> news:%23$EQz$4hDHA.2296@.TK2MSFTNGP09.phx.gbl...
> > I posted this question long ago to several groups but it didn't generate
a
> > lot of interest so I thought I would try again.
> >
> > I have been looking for books on database design and or modeling but
can't
> > determine which method or approach would best suite my needs.
> >
> > I guess this comes from the fact that I'm not sure what the difference
is
> in
> > the different types of modeling and or design approaches, or for that
> matter
> > what possible methods are available. ER, UML ORM....are there others?
Are
> > these different ways to model? Are there other methods? Why should I
> choose
> > one over the other?
> >
> > I mean I'm just the db guy truing to design the best database for our
> > application. If you can also point out any good books to build up on
your
> > suggestions it would be helpful.
> >
> > Thanks,
> > Charlie
> >
> >
>|||UML is for designing software and will produce code that doesn't fit well to
a relational database design. Unfortunately that's just the nature of OOP,
and unless you want to use an object oriented database, you just need to
deal with translating the data into your software.
ER and ORM I consider to be complementary techiques. I prefer designing in
ORM and looking at other people's design as ER diagrams. I find that ORM
gives me more insight into my goals for storing the data, while ER requires
me to focus too much on the actual layout of the data, sometimes losing
sight of the goals for storing it in the first place. Fortunately it's
possible to go back and forth between ER and ORM (at least with Visio, and
if it did a better job of syncing to the database you could even add the
implementation to the mix). I initially learned ORM by going back and forth
between ER and ORM design, until I was comfortable enough with ORM to drop
ER from the initial design process.
I'd also just like to mention that "Handbook of Relational Database" was the
very first book I ever read on database design (that was when I showed up on
my first day of my university work term with no database experience -- not
even DB-III -- to find a FoxPro box on my desk and a request to "build us an
inventory management system"). I still consider it the most useful book I've
ever read on relational databases.
Colin
"Charles Deaton" <mssql@.mssql.com> wrote in message
news:%23PyUhsBiDHA.2296@.TK2MSFTNGP09.phx.gbl...
> Thanks Allen,
> Can anyone else comment on approaches to design (ER,UML,ORM').
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eDIf6m%23hDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > There are a couple of suggestions here
> >
> > http://vyaskn.tripod.com/sqlbooks.htm#rdbms
> >
> > I personally enjoyed Louis Davidson's "Professionsal SQL Server 2000
> > Database Design"
> >
> > Recommendations from "Inside Microsoft SQL Server 2000" by Kalen
Delaney:
> > (from post by B.P.Margolin)
> >
> > An Introduction to Database Systems, 7th Edition, by C. J. Date
> > (Addison-Wesley, 1999). A new revision of a classic book, written by a
> giant
> > in the field, that covers general relational database concepts. A
> must-read
> > for everyone in the database industry.
> >
> > Database Design for Mere Mortals by Michael J. Hernandez
(Addison-Wesley,
> > 1997). A very readable approach to the daunting task of designing a
> > relational database to solve real-world problems. The book is written in
a
> > database-independent format, so the details can be applied to SQL Server
> as
> > well as to other RDMSs you might use.
> >
> > Handbook of Relational Database Design by Candace C. Fleming and Barbara
> > Vonhalle (Addison-Wesley, 1988). A fine book that discusses general
> logical
> > database design and data modeling approaches and techniques.
> >
> > SAMS Teach Yourself Microsoft SQL Server 2000 in 21 Days by Richard
> Waymire
> > and Rick Sawtell (SAMS Publishing, 2000). A good first book on SQL
Server
> > 2000 in tutorial format that gets readers up to speed quickly. One of
the
> > coauthors is a program manager on the SQL Server development team. This
> > title includes self-test questions for each of the 21 "days."
> >
> > Database: Principles, Programming, Performance by Patrick O'Neil (Morgan
> > Kaufmann Publishers, 1994). This thorough textbook provides excellent
> > introductory materials, so I've placed it high on the list. It also
> > carefully details a broad spectrum of database topics, including buffer
> > management and transaction semantics.
> >
> >
> > --
> > --
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Charlie" <cdeaton@.corp.realcomp.com> wrote in message
> > news:%23$EQz$4hDHA.2296@.TK2MSFTNGP09.phx.gbl...
> > > I posted this question long ago to several groups but it didn't
generate
> a
> > > lot of interest so I thought I would try again.
> > >
> > > I have been looking for books on database design and or modeling but
> can't
> > > determine which method or approach would best suite my needs.
> > >
> > > I guess this comes from the fact that I'm not sure what the difference
> is
> > in
> > > the different types of modeling and or design approaches, or for that
> > matter
> > > what possible methods are available. ER, UML ORM....are there others?
> Are
> > > these different ways to model? Are there other methods? Why should I
> > choose
> > > one over the other?
> > >
> > > I mean I'm just the db guy truing to design the best database for our
> > > application. If you can also point out any good books to build up on
> your
> > > suggestions it would be helpful.
> > >
> > > Thanks,
> > > Charlie
> > >
> > >
> >
> >
>|||You might as well have posted a message asking what is the best
religion ;-)
Your questions are so broad, that there's no way to answer
themsuccinctly. So I'm not surprised your previous posting along the
same lines generated little interest.
I'd do a groups.googlecom search and look for discussions on the
topics that interest you so that you can garner a wide range of
previously-expressed opinions. Amazon.com is a good place to search
for books since they post reviews.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Tue, 30 Sep 2003 16:17:56 -0400, "Charlie"
<cdeaton@.corp.realcomp.com> wrote:
>I posted this question long ago to several groups but it didn't generate a
>lot of interest so I thought I would try again.
>I have been looking for books on database design and or modeling but can't
>determine which method or approach would best suite my needs.
>I guess this comes from the fact that I'm not sure what the difference is in
>the different types of modeling and or design approaches, or for that matter
>what possible methods are available. ER, UML ORM....are there others? Are
>these different ways to model? Are there other methods? Why should I choose
>one over the other?
>I mean I'm just the db guy truing to design the best database for our
>application. If you can also point out any good books to build up on your
>suggestions it would be helpful.
>Thanks,
>Charlie
>

Friday, February 24, 2012

Design Slow

I'm building a report with dozens of grids and around 8 columns per grid with
3-4 groups each. The report runs fine (slow because of the amount of data
but that's expected). When I am in the report designer editing the report,
simply trying to widen a column, change a label, add a text box or move a
grid is UNBELIEVABLY slow.
Can this be fixed? My guess is Visual Studio is "memorizing" the action
being performed as a type of macro to enable "undos" and based on the volume
of grids, text boxes, etc... it take a long time to do this.How much RAM do you have on your PC?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> I'm building a report with dozens of grids and around 8 columns per grid
> with
> 3-4 groups each. The report runs fine (slow because of the amount of data
> but that's expected). When I am in the report designer editing the
> report,
> simply trying to widen a column, change a label, add a text box or move a
> grid is UNBELIEVABLY slow.
> Can this be fixed? My guess is Visual Studio is "memorizing" the action
> being performed as a type of macro to enable "undos" and based on the
> volume
> of grids, text boxes, etc... it take a long time to do this.|||2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
132mb when working on just this .rdl report.
"Bruce L-C [MVP]" wrote:
> How much RAM do you have on your PC?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> > I'm building a report with dozens of grids and around 8 columns per grid
> > with
> > 3-4 groups each. The report runs fine (slow because of the amount of data
> > but that's expected). When I am in the report designer editing the
> > report,
> > simply trying to widen a column, change a label, add a text box or move a
> > grid is UNBELIEVABLY slow.
> >
> > Can this be fixed? My guess is Visual Studio is "memorizing" the action
> > being performed as a type of macro to enable "undos" and based on the
> > volume
> > of grids, text boxes, etc... it take a long time to do this.
>
>|||My guess is that you are hitting some sort of resource problem (not a ram
problem however).
Dozens of grids is pretty unusual. One thing you might consider is that
instead of having all these grids on one report is to have a report with
multiple subreports. I hide the subreports in listview so the user does not
see it (that is a property of the report you can get to with report
manager). This enables you to test each subreport independently. Then you
drag and drop subreport onto the main report, hook up the parameters and
away you go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
>2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
> 132mb when working on just this .rdl report.
> "Bruce L-C [MVP]" wrote:
>> How much RAM do you have on your PC?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
>> > I'm building a report with dozens of grids and around 8 columns per
>> > grid
>> > with
>> > 3-4 groups each. The report runs fine (slow because of the amount of
>> > data
>> > but that's expected). When I am in the report designer editing the
>> > report,
>> > simply trying to widen a column, change a label, add a text box or move
>> > a
>> > grid is UNBELIEVABLY slow.
>> >
>> > Can this be fixed? My guess is Visual Studio is "memorizing" the
>> > action
>> > being performed as a type of macro to enable "undos" and based on the
>> > volume
>> > of grids, text boxes, etc... it take a long time to do this.
>>|||Ok, I'm expirementing with the sub report route and design seems faster if I
can work out my layout issues. Each subreport will be calling the same 4
store procs, each of which is a MONSTER. So, my question is if I use
subreports, am I hammering the SQL Server more by calling each ofthese 4
procs "repetitively" for each sub report, rather than a single call to each
proc from a single monster report with dozens of grids.
"Bruce L-C [MVP]" wrote:
> My guess is that you are hitting some sort of resource problem (not a ram
> problem however).
> Dozens of grids is pretty unusual. One thing you might consider is that
> instead of having all these grids on one report is to have a report with
> multiple subreports. I hide the subreports in listview so the user does not
> see it (that is a property of the report you can get to with report
> manager). This enables you to test each subreport independently. Then you
> drag and drop subreport onto the main report, hook up the parameters and
> away you go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
> > 132mb when working on just this .rdl report.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> How much RAM do you have on your PC?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> >> > I'm building a report with dozens of grids and around 8 columns per
> >> > grid
> >> > with
> >> > 3-4 groups each. The report runs fine (slow because of the amount of
> >> > data
> >> > but that's expected). When I am in the report designer editing the
> >> > report,
> >> > simply trying to widen a column, change a label, add a text box or move
> >> > a
> >> > grid is UNBELIEVABLY slow.
> >> >
> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
> >> > action
> >> > being performed as a type of macro to enable "undos" and based on the
> >> > volume
> >> > of grids, text boxes, etc... it take a long time to do this.
> >>
> >>
> >>
>
>|||Hmmm, so your grids are tied to the same dataset(s). I.e. in the monster
report you have only 4 datasets for all the grids?
Well, then the answer is yes you will be calling it once for each subreport.
If it would work for you to have 4 subreports where each subreport has all
the grids for one dataset. Then you main report does nothing other than host
the four subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:1F36FDE2-0CA5-4017-BA68-CF385753A3C0@.microsoft.com...
> Ok, I'm expirementing with the sub report route and design seems faster if
> I
> can work out my layout issues. Each subreport will be calling the same 4
> store procs, each of which is a MONSTER. So, my question is if I use
> subreports, am I hammering the SQL Server more by calling each ofthese 4
> procs "repetitively" for each sub report, rather than a single call to
> each
> proc from a single monster report with dozens of grids.
> "Bruce L-C [MVP]" wrote:
>> My guess is that you are hitting some sort of resource problem (not a ram
>> problem however).
>> Dozens of grids is pretty unusual. One thing you might consider is that
>> instead of having all these grids on one report is to have a report with
>> multiple subreports. I hide the subreports in listview so the user does
>> not
>> see it (that is a property of the report you can get to with report
>> manager). This enables you to test each subreport independently. Then you
>> drag and drop subreport onto the main report, hook up the parameters and
>> away you go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
>> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using
>> >about
>> > 132mb when working on just this .rdl report.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> How much RAM do you have on your PC?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
>> >> > I'm building a report with dozens of grids and around 8 columns per
>> >> > grid
>> >> > with
>> >> > 3-4 groups each. The report runs fine (slow because of the amount
>> >> > of
>> >> > data
>> >> > but that's expected). When I am in the report designer editing the
>> >> > report,
>> >> > simply trying to widen a column, change a label, add a text box or
>> >> > move
>> >> > a
>> >> > grid is UNBELIEVABLY slow.
>> >> >
>> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
>> >> > action
>> >> > being performed as a type of macro to enable "undos" and based on
>> >> > the
>> >> > volume
>> >> > of grids, text boxes, etc... it take a long time to do this.
>> >>
>> >>
>> >>
>>|||Yes, I did some profiling and unfortunately, based on the complexity of the
underlying stored procs, I don't think I can justify calling them each
multiple times in production just speed design/development. I will try to
achieve something similar to what you are saying by trying to use a List
Boxes for a repeated groups...this may cut down on the number of grids and
help me.
Thanks for all your input.
"Bruce L-C [MVP]" wrote:
> Hmmm, so your grids are tied to the same dataset(s). I.e. in the monster
> report you have only 4 datasets for all the grids?
> Well, then the answer is yes you will be calling it once for each subreport.
> If it would work for you to have 4 subreports where each subreport has all
> the grids for one dataset. Then you main report does nothing other than host
> the four subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:1F36FDE2-0CA5-4017-BA68-CF385753A3C0@.microsoft.com...
> > Ok, I'm expirementing with the sub report route and design seems faster if
> > I
> > can work out my layout issues. Each subreport will be calling the same 4
> > store procs, each of which is a MONSTER. So, my question is if I use
> > subreports, am I hammering the SQL Server more by calling each ofthese 4
> > procs "repetitively" for each sub report, rather than a single call to
> > each
> > proc from a single monster report with dozens of grids.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> My guess is that you are hitting some sort of resource problem (not a ram
> >> problem however).
> >>
> >> Dozens of grids is pretty unusual. One thing you might consider is that
> >> instead of having all these grids on one report is to have a report with
> >> multiple subreports. I hide the subreports in listview so the user does
> >> not
> >> see it (that is a property of the report you can get to with report
> >> manager). This enables you to test each subreport independently. Then you
> >> drag and drop subreport onto the main report, hook up the parameters and
> >> away you go.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
> >> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using
> >> >about
> >> > 132mb when working on just this .rdl report.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> How much RAM do you have on your PC?
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> >> >> > I'm building a report with dozens of grids and around 8 columns per
> >> >> > grid
> >> >> > with
> >> >> > 3-4 groups each. The report runs fine (slow because of the amount
> >> >> > of
> >> >> > data
> >> >> > but that's expected). When I am in the report designer editing the
> >> >> > report,
> >> >> > simply trying to widen a column, change a label, add a text box or
> >> >> > move
> >> >> > a
> >> >> > grid is UNBELIEVABLY slow.
> >> >> >
> >> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
> >> >> > action
> >> >> > being performed as a type of macro to enable "undos" and based on
> >> >> > the
> >> >> > volume
> >> >> > of grids, text boxes, etc... it take a long time to do this.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Design Questions on File Groups and Files

In general, what are the advantages and disadvantages to have more data files
vs. less data files? Thanks.Kathy,
Assuming filegroups are used as well:
1. Advanced placement of database objects
2. Seperation of table and indexes incl., seperation of system objects from
user-defined objects
3. Possible increase in performance w/ RAID or w/o RAID -- results will
vary
4. Expansion of the database onto seperate physical drives
5. Faster backups for larger databases
That being said most smaller to medium sized databases probably do not need
to use additional files and filegroups (just an opinion).
HTH
Jerry
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:6AEA9FF8-63C8-4A2A-8824-C3787CB79B04@.microsoft.com...
> In general, what are the advantages and disadvantages to have more data
> files
> vs. less data files? Thanks.|||Thanks Jerry. Then what are disadvantages (oeverhead) to have more data
files? and/or how many is considered too many?
"Jerry Spivey" wrote:
> Kathy,
> Assuming filegroups are used as well:
> 1. Advanced placement of database objects
> 2. Seperation of table and indexes incl., seperation of system objects from
> user-defined objects
> 3. Possible increase in performance w/ RAID or w/o RAID -- results will
> vary
> 4. Expansion of the database onto seperate physical drives
> 5. Faster backups for larger databases
> That being said most smaller to medium sized databases probably do not need
> to use additional files and filegroups (just an opinion).
> HTH
> Jerry
> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
> news:6AEA9FF8-63C8-4A2A-8824-C3787CB79B04@.microsoft.com...
> > In general, what are the advantages and disadvantages to have more data
> > files
> > vs. less data files? Thanks.
>
>|||I'm not aware of any issues nor do I have a "too many" count. I would base
it off of need...i.e., if you need an advantage exposed by using
file/filegroups the use them...if not, then I wouldn't. Additional
files/filegroups can make it a little more challenging to administer (i.e.,
future movement of the objects - emptying files etc...)
HTH
Jerry
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:34B97E17-7CF2-4825-8173-3F03E35B8367@.microsoft.com...
> Thanks Jerry. Then what are disadvantages (oeverhead) to have more data
> files? and/or how many is considered too many?
> "Jerry Spivey" wrote:
>> Kathy,
>> Assuming filegroups are used as well:
>> 1. Advanced placement of database objects
>> 2. Seperation of table and indexes incl., seperation of system objects
>> from
>> user-defined objects
>> 3. Possible increase in performance w/ RAID or w/o RAID -- results will
>> vary
>> 4. Expansion of the database onto seperate physical drives
>> 5. Faster backups for larger databases
>> That being said most smaller to medium sized databases probably do not
>> need
>> to use additional files and filegroups (just an opinion).
>> HTH
>> Jerry
>> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
>> news:6AEA9FF8-63C8-4A2A-8824-C3787CB79B04@.microsoft.com...
>> > In general, what are the advantages and disadvantages to have more data
>> > files
>> > vs. less data files? Thanks.
>>|||There is a debating here whether or not the number of files has significant
performance impact. More specifically, one data file per filegroup vs four
files per filegroup, for example. Do you have experience on it? Thanks.
"Jerry Spivey" wrote:
> I'm not aware of any issues nor do I have a "too many" count. I would base
> it off of need...i.e., if you need an advantage exposed by using
> file/filegroups the use them...if not, then I wouldn't. Additional
> files/filegroups can make it a little more challenging to administer (i.e.,
> future movement of the objects - emptying files etc...)
> HTH
> Jerry
> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
> news:34B97E17-7CF2-4825-8173-3F03E35B8367@.microsoft.com...
> > Thanks Jerry. Then what are disadvantages (oeverhead) to have more data
> > files? and/or how many is considered too many?
> >
> > "Jerry Spivey" wrote:
> >
> >> Kathy,
> >>
> >> Assuming filegroups are used as well:
> >>
> >> 1. Advanced placement of database objects
> >> 2. Seperation of table and indexes incl., seperation of system objects
> >> from
> >> user-defined objects
> >> 3. Possible increase in performance w/ RAID or w/o RAID -- results will
> >> vary
> >> 4. Expansion of the database onto seperate physical drives
> >> 5. Faster backups for larger databases
> >>
> >> That being said most smaller to medium sized databases probably do not
> >> need
> >> to use additional files and filegroups (just an opinion).
> >>
> >> HTH
> >>
> >> Jerry
> >>
> >> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
> >> news:6AEA9FF8-63C8-4A2A-8824-C3787CB79B04@.microsoft.com...
> >> > In general, what are the advantages and disadvantages to have more data
> >> > files
> >> > vs. less data files? Thanks.
> >>
> >>
> >>
>
>|||Kathy,
Check out:
http://www.databasejournal.com/features/mssql/article.php/1439801
and
http://www.sql-server-performance.com/filegroups.asp
HTH
Jerry
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:8A0149F3-1A3E-499D-B844-0E8C2F647317@.microsoft.com...
> There is a debating here whether or not the number of files has
> significant
> performance impact. More specifically, one data file per filegroup vs four
> files per filegroup, for example. Do you have experience on it? Thanks.
> "Jerry Spivey" wrote:
>> I'm not aware of any issues nor do I have a "too many" count. I would
>> base
>> it off of need...i.e., if you need an advantage exposed by using
>> file/filegroups the use them...if not, then I wouldn't. Additional
>> files/filegroups can make it a little more challenging to administer
>> (i.e.,
>> future movement of the objects - emptying files etc...)
>> HTH
>> Jerry
>> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
>> news:34B97E17-7CF2-4825-8173-3F03E35B8367@.microsoft.com...
>> > Thanks Jerry. Then what are disadvantages (oeverhead) to have more data
>> > files? and/or how many is considered too many?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Kathy,
>> >>
>> >> Assuming filegroups are used as well:
>> >>
>> >> 1. Advanced placement of database objects
>> >> 2. Seperation of table and indexes incl., seperation of system
>> >> objects
>> >> from
>> >> user-defined objects
>> >> 3. Possible increase in performance w/ RAID or w/o RAID -- results
>> >> will
>> >> vary
>> >> 4. Expansion of the database onto seperate physical drives
>> >> 5. Faster backups for larger databases
>> >>
>> >> That being said most smaller to medium sized databases probably do not
>> >> need
>> >> to use additional files and filegroups (just an opinion).
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >>
>> >> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
>> >> news:6AEA9FF8-63C8-4A2A-8824-C3787CB79B04@.microsoft.com...
>> >> > In general, what are the advantages and disadvantages to have more
>> >> > data
>> >> > files
>> >> > vs. less data files? Thanks.
>> >>
>> >>
>> >>
>>|||Jerry. Thanks very much. The included articles are very helpful to understand
the issue.
"Jerry Spivey" wrote:
> Kathy,
> Check out:
> http://www.databasejournal.com/features/mssql/article.php/1439801
> and
> http://www.sql-server-performance.com/filegroups.asp
> HTH
> Jerry
> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
> news:8A0149F3-1A3E-499D-B844-0E8C2F647317@.microsoft.com...
> > There is a debating here whether or not the number of files has
> > significant
> > performance impact. More specifically, one data file per filegroup vs four
> > files per filegroup, for example. Do you have experience on it? Thanks.
> >
> > "Jerry Spivey" wrote:
> >
> >> I'm not aware of any issues nor do I have a "too many" count. I would
> >> base
> >> it off of need...i.e., if you need an advantage exposed by using
> >> file/filegroups the use them...if not, then I wouldn't. Additional
> >> files/filegroups can make it a little more challenging to administer
> >> (i.e.,
> >> future movement of the objects - emptying files etc...)
> >>
> >> HTH
> >>
> >> Jerry
> >> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
> >> news:34B97E17-7CF2-4825-8173-3F03E35B8367@.microsoft.com...
> >> > Thanks Jerry. Then what are disadvantages (oeverhead) to have more data
> >> > files? and/or how many is considered too many?
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Kathy,
> >> >>
> >> >> Assuming filegroups are used as well:
> >> >>
> >> >> 1. Advanced placement of database objects
> >> >> 2. Seperation of table and indexes incl., seperation of system
> >> >> objects
> >> >> from
> >> >> user-defined objects
> >> >> 3. Possible increase in performance w/ RAID or w/o RAID -- results
> >> >> will
> >> >> vary
> >> >> 4. Expansion of the database onto seperate physical drives
> >> >> 5. Faster backups for larger databases
> >> >>
> >> >> That being said most smaller to medium sized databases probably do not
> >> >> need
> >> >> to use additional files and filegroups (just an opinion).
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >>
> >> >> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
> >> >> news:6AEA9FF8-63C8-4A2A-8824-C3787CB79B04@.microsoft.com...
> >> >> > In general, what are the advantages and disadvantages to have more
> >> >> > data
> >> >> > files
> >> >> > vs. less data files? Thanks.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Design Questions on File Groups and Files

Design question from data mining newbie

Hi,

We currently have about 900 stored procedures which have logic to group healthcare claims into different 'Edit Groups' depending on the logic within each 'Edit' stored procedure.

Examples of the logic for the Edit stored procedures would be something like:

Edit1: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P1, P2, P3....P345) and a diagnosis code in (D1, D2,...D123) and does NOT have a modifer code in (M1, M2, M3)

Edit2: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P7, P8, P9....Pxxx) and a diagnosis code in (D1, D2,...Dyyy) and has a modifer code in (M3, M4, M7), which are dated within 120 days of each other.

Do you think one of the SQL Server 2005 Data mining algorithms (Clustering or Classification or Association Rules) could play some part in this? Most of the 900 stored procs can be grouped based on logic, I mean the logic is similar for each group and only the parameters (in brackets above) vary for each stored proc within the same group.

We're totally new to data mining, although we do have some moderately complex cubes running. Which algorithm (if any) would be the most appropriate for our needs?

Thanks for any help,

JGP

Hi,

From you examples, you data is of multimensional nature. It is definitely helpful to use Microsoft Analysis Services to create OLAP cube(s) to efficiently support browse and explore your edit groups. If you are only interested in querying your edit groups, you don't need data mining.

Data mining is good at generalizing knowledge(such as patterns) from data. It can then use learned knowldege to analyze your (new) data. For example, if you have a given set of claims described by the following table:

ClaimID Income HaveInurance City Fraud

1 x0,000 Yes FairyLand No

2 y0,000 No FraudLand Yes

.......

Suppose you can collect the above data from your claim database. Now let's say that you are interested in predict whether or not a new claim is fraud. You can train a model with one of Microsoft data mining algorithms (such as Microsoft Decision Trees). After training, you can use your model to predict new claims like this:

ClaimID Income HaveInurance City

10001 a0,000 Yes FairyLand

1002 b0,000 No FraudLand

.......

Depends on the query you use, you can get some result like this:

ClaimID Income HaveInurance City Predicted Result of Fraud

10001 a0,000 Yes FairyLand No

1002 b0,000 No FraudLand Yes

.......

In general, data mining can play a role when you need to learn patterns from your data, and then apply the patterns to analyze (new) data.

|||

Thanks for the example.

Are you saying that Data mining is relevant only in predicting stuff and not in finding relationships based on pre-defined rules, like what I initially explained?

I did see come cases where classification was done, where based on a bunch of parameters, historic claims can be classified into Edit1(cluster1) , Edit2(cluster2) etc.

Isn't this possible?

Thanks,

JGP

|||

My post is just using predicting as an example. Data mining is relavant to predicting as well as finding relation among data. For example, you can use clustering algorithm to cluster you data, and check whether there is a natural mapping between your edit groups and the cluster1 you found (as you mentioned above). This process can help you understand you data better. For example, if an Edit group can be naturally mapped to some cluster, this Edit group can be considered as well defined, since it really maps to some existing grouping (or cluster) of your data. On the other hand, you might consider merge a few groups if they belong to the same cluster, etc.

On the other hand, data mining works on the basis of probability. In other words, it can not be 100% correct most of the times. Say, for a problem with a set of rules, you can already classify each case (such as each claim) into each target group 100% correct. You don't want to use data mining, because you can not do any better than 100% correct, and data mining does not come free. But, if you need to find something unknown about your data, such as a claim is/isn't fraud or if it belongs to some unknown group, you should resort to data mining.

Good luck,

|||

Thanks, I think I'm getting the picture now.

Just to get some hands on, would you happen to know of any good tutorials\books for clustering\classification that is available for newbies?

Preferably one that works with 'well-defined' groups....

|||

A good book is Data Mining Techniques by Berry and Linhoff.

I think in your situation you can use data mining for data discovery to learn quite a bit about your data sets. It sounds like each of your "edits" are a fairly complicated set of rules and it may be difficult to determine which rules end up being more important, or which "edits" are related (if I am correct, a single record could have multiple "edits", no?)

With this is mind, you could "reverse engineer" the edits with a simple classification model -e.g. trees - to predict what factors are the "most important" in determining an edit. If all of your edits are "and" conditions, this won't do much, but if you have any with "or" conditions, you may find that a majority of records recieve an edit for only a few of the possible conditions. Using this to classify old records, as Yimin notes, is not going to be 100% accurate though, since you already have an encoding of the 100% accurate rules.

If you created a table that had for each record all of the record data and all of the possible "edits" for that record (you would likely need a nested table) you could predict "edits" based on the record data plus other "edits". This would end up in a relationship diagram describing how record data and edits are related. If you made such a model including only the edits, you would see how they are interrelated independent of record data. You could perform a similar operation using Clustering, to see if groups of edits cluster together.

In the end you could end up with a greater understanding of your data - potentially removing redundant code or streamlining in other ways. The good thing about data mining, it that it's painless - it's kind of fun to play with and you can get some good insights, but it doesn't cause any harm in the meantime....

Enjoy, and feel free to post any follow up questions.

-Jamie

Sunday, February 19, 2012

Design Question

I am in the design phase of a relational database using OO methods...I have three groups of people that I want to track...1. Students 2. Parents 3. Adult Volunteers...

I have started with a table called people where I have attributes that are common to all three groups of people...then I have a students table and volunteer table that have attributes common only to a student or volunteer...the key in these tables is also the FK of the people table. I think this is proper design.

Here is my question:

There is a many-many relationship with parents and students. A third junction table is needed I know but how is this accomplished with subtype tables...would I create the juntion between parents table and Students table or between parents table and people table?

Any suggestion would be appreciated. Maybe this is all wrong and someone has a better solution...thanks

Tonypost what the current fields are in each table and the relationships between them.|||You have identified that Students, Parents and Volunteers are all types of people, so you have created a People table with a primary key (PeopleID). In the Student and Voluteer tables there are keys that contain PeopleID.

The task is to show the relationship between a student and its parents. Students and Parents are all sub-types of People. What you are trying to define is a relationship from one person to another, so the design should link a record from the People table to another record in the People table.

The Parent table will have a PeopleID column that represents the student and a second column with a renamed PeopleID to represent the ID of the parent. You can then add any other columns that contain parent data. The primary key will be a compound key based on the first two columns.

Referencing column 1 will list all the people who are the parents of Student A. Referencing column 2 will list all the students of parent B.

By linking people records in this way you can also represent the relationship where a parent is also a student.

As the table can be used to represent any relationship between two people records, you may want to check if any other relationships need to be represented (Student A is the brother/sister of Student B etc.) If so, you could add a RelationshipID column to the table, add it to the primary key and change the name of the table.

Whether this is the best solution for you depends on all of your requirements. Hope you found this useful.

Friday, February 17, 2012

design issue: better speed performance by separating into different databases?

Hi,
From my limited knowledge, I think we can gain some speed performance by
breaking up a database out into multiple groups of tables and create one
database for each group - given that I don't care about the relationship as
much. The reason is, each database's dasta is saved down to a physical data
file (not quite, I realize that we can create filegroups) so sometimes
reading and writing operations must get into each other ways and then end up
get serialized by sqlserver automatically. I'm hoping to get some advice
from the experienced people in this area. Would using multiple filegroups
for a single db is better in speed performance than using multiple
databases?
I'm using sqlserver 2005 Enterprise edition.
Thank you for your comment or advice.Zen,
There are a host of approaches for increasing performance of a database
system. Which one/ones to use are dependent on the circumstances and the
performance issues. Many issues can be resolved my careful analysis and
modification of the query and/or indexing. What performance problem are you
experiencing? Can you narrow it down to make the resolution easier to
identify?
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
> I'm using sqlserver 2005 Enterprise edition.
> Thank you for your comment or advice.
>|||I don't have performance issue yet, but I'm just designing a solution that
maximize the read operations - so I'm concerned about the write operations
on the shared resource (physical file etc...). It's a small part of the
bigger design. Modification of the query and indexing will be done on top
of what we can do to avoid resource sharing (if it makes perf difference)
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eQTxueCgGHA.3860@.TK2MSFTNGP02.phx.gbl...
> Zen,
> There are a host of approaches for increasing performance of a database
> system. Which one/ones to use are dependent on the circumstances and the
> performance issues. Many issues can be resolved my careful analysis and
> modification of the query and/or indexing. What performance problem are
> you experiencing? Can you narrow it down to make the resolution easier to
> identify?
> HTH
> Jerry
> "Zen" <zen@.nononospam.com> wrote in message
> news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> From my limited knowledge, I think we can gain some speed performance by
>> breaking up a database out into multiple groups of tables and create one
>> database for each group - given that I don't care about the relationship
>> as much. The reason is, each database's dasta is saved down to a physical
>> data file (not quite, I realize that we can create filegroups) so
>> sometimes reading and writing operations must get into each other ways
>> and then end up get serialized by sqlserver automatically. I'm hoping to
>> get some advice from the experienced people in this area. Would using
>> multiple filegroups for a single db is better in speed performance than
>> using multiple databases?
>> I'm using sqlserver 2005 Enterprise edition.
>> Thank you for your comment or advice.
>|||Zen wrote:
> I don't have performance issue yet, but I'm just designing a solution that
> maximize the read operations - so I'm concerned about the write operations
> on the shared resource (physical file etc...). It's a small part of the
> bigger design. Modification of the query and indexing will be done on top
> of what we can do to avoid resource sharing (if it makes perf difference)
>
That is absolutely no reason to consider using separate databases. As
Jerry says, there is a whole range of ways to optimize database
performance and all of them are available to you in a single database.
The valid reasons you might need separate databases are if the data has
different requirements for availability, security, backup or for other
administrative issues, not for performance. I'd also add that you are
going about your design the wrong way if you are considering
performance issues before you've planned and tested a logical database
design.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
>
Yes. There is absolutely, positively no reason to use multiple databases
for performance reasons.
Filegroups will allow you to slice up the storage any way you want.
But, no offense meant here, your comments reveal a nearly complete lack of
knoledge of SQL Server internals.
Unless you understand the relationship between the Data Files, the Buffer
Cache and the Transaction Log, and how a query is processed, you shouldn't
make design decisions based on performance. Just create a design that is
simple, models your business domain reasonably well and is reasonably
relationally correct, and performance will usually take care of itself. In
fact, even if you do know all the performance details, you still shoudn't
make design decisions based on performance.
To learn more about how SQL Server stores data and processes queries, there
are some OK topic in Books Online.
Write-Ahead Transaction Log
http://msdn2.microsoft.com/en-us/library/ms186259.aspx
I/O Architecture
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_3339.asp
And Inside Microsoft SQL Server 2000 is the standard reference. Inside
Microsoft SQL Server 2005 is in the process of beging released in multiple
volumes.
David|||I guess the key part of of my question is: assuming that logical database
design correction is the same, would it make perf difference if they are
separate in different databases? thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
> Zen wrote:
>> I don't have performance issue yet, but I'm just designing a solution
>> that
>> maximize the read operations - so I'm concerned about the write
>> operations
>> on the shared resource (physical file etc...). It's a small part of the
>> bigger design. Modification of the query and indexing will be done on
>> top
>> of what we can do to avoid resource sharing (if it makes perf difference)
> That is absolutely no reason to consider using separate databases. As
> Jerry says, there is a whole range of ways to optimize database
> performance and all of them are available to you in a single database.
> The valid reasons you might need separate databases are if the data has
> different requirements for availability, security, backup or for other
> administrative issues, not for performance. I'd also add that you are
> going about your design the wrong way if you are considering
> performance issues before you've planned and tested a logical database
> design.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Zen wrote:
> I guess the key part of of my question is: assuming that logical database
> design correction is the same, would it make perf difference if they are
> separate in different databases? thanks!
>
Not really the right question because performance depends on other
factors. Put it this way: using separate databases doesn't give you any
extra options for enhancing performance - for example data can be
partitioned across multiple files even within the same database. So it
would be more accurate to say that you can achieve the same performance
with one database as you can with multiple databases - assuming that
all the other database administration factors are uniform across your
data.
You haven't given us any idea what kind of data volumes you are talking
about. Multiple-terabyte databases are common currency today. If you
are at or approaching terabyte scale however, you should hire some
proper expertise now because mistakes are easily made and can be very
expensive to fix.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Zen,
It "could" in certain situations i.e., distributed partitioned views in a
federation of servers however this is a high-availability solution for
specific demonstrated performance issues and not generally a first pass
optimization technique. I would recommend you stick with one database in
this case.
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:O3G$64DgGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I guess the key part of of my question is: assuming that logical database
>design correction is the same, would it make perf difference if they are
>separate in different databases? thanks!
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
>> Zen wrote:
>> I don't have performance issue yet, but I'm just designing a solution
>> that
>> maximize the read operations - so I'm concerned about the write
>> operations
>> on the shared resource (physical file etc...). It's a small part of the
>> bigger design. Modification of the query and indexing will be done on
>> top
>> of what we can do to avoid resource sharing (if it makes perf
>> difference)
>>
>> That is absolutely no reason to consider using separate databases. As
>> Jerry says, there is a whole range of ways to optimize database
>> performance and all of them are available to you in a single database.
>> The valid reasons you might need separate databases are if the data has
>> different requirements for availability, security, backup or for other
>> administrative issues, not for performance. I'd also add that you are
>> going about your design the wrong way if you are considering
>> performance issues before you've planned and tested a logical database
>> design.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>|||Jerry Spivey wrote:
> Zen,
> It "could" in certain situations i.e., distributed partitioned views in a
> federation of servers however this is a high-availability solution for
> specific demonstrated performance issues and not generally a first pass
> optimization technique. I would recommend you stick with one database in
> this case.
> HTH
>
Ah, that's a good point. If "separate databases" also means "separate
servers" then there obviously might be a reason for doing it.
The point I was trying to make was this: If you take one database and
naively make an identical copy of that database on the same server then
you won't achieve anything useful. True, you will have created at least
two new files (one data, one log) and depending on the placement of
those files you may perhaps see some performance impact. But then you
might have achieved the same thing by creating those extra files in one
database instead of two. So performance always depends on things other
than the number of databases. As you say, it depends on the number of
servers for instance :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

design issue: better speed performance by separating into different databases?

Hi,
From my limited knowledge, I think we can gain some speed performance by
breaking up a database out into multiple groups of tables and create one
database for each group - given that I don't care about the relationship as
much. The reason is, each database's dasta is saved down to a physical data
file (not quite, I realize that we can create filegroups) so sometimes
reading and writing operations must get into each other ways and then end up
get serialized by sqlserver automatically. I'm hoping to get some advice
from the experienced people in this area. Would using multiple filegroups
for a single db is better in speed performance than using multiple
databases?
I'm using sqlserver 2005 Enterprise edition.
Thank you for your comment or advice.Zen,
There are a host of approaches for increasing performance of a database
system. Which one/ones to use are dependent on the circumstances and the
performance issues. Many issues can be resolved my careful analysis and
modification of the query and/or indexing. What performance problem are you
experiencing? Can you narrow it down to make the resolution easier to
identify?
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
> I'm using sqlserver 2005 Enterprise edition.
> Thank you for your comment or advice.
>|||I don't have performance issue yet, but I'm just designing a solution that
maximize the read operations - so I'm concerned about the write operations
on the shared resource (physical file etc...). It's a small part of the
bigger design. Modification of the query and indexing will be done on top
of what we can do to avoid resource sharing (if it makes perf difference)
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eQTxueCgGHA.3860@.TK2MSFTNGP02.phx.gbl...
> Zen,
> There are a host of approaches for increasing performance of a database
> system. Which one/ones to use are dependent on the circumstances and the
> performance issues. Many issues can be resolved my careful analysis and
> modification of the query and/or indexing. What performance problem are
> you experiencing? Can you narrow it down to make the resolution easier to
> identify?
> HTH
> Jerry
> "Zen" <zen@.nononospam.com> wrote in message
> news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
>|||Zen wrote:
> I don't have performance issue yet, but I'm just designing a solution that
> maximize the read operations - so I'm concerned about the write operations
> on the shared resource (physical file etc...). It's a small part of the
> bigger design. Modification of the query and indexing will be done on top
> of what we can do to avoid resource sharing (if it makes perf difference)
>
That is absolutely no reason to consider using separate databases. As
Jerry says, there is a whole range of ways to optimize database
performance and all of them are available to you in a single database.
The valid reasons you might need separate databases are if the data has
different requirements for availability, security, backup or for other
administrative issues, not for performance. I'd also add that you are
going about your design the wrong way if you are considering
performance issues before you've planned and tested a logical database
design.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
>
Yes. There is absolutely, positively no reason to use multiple databases
for performance reasons.
Filegroups will allow you to slice up the storage any way you want.
But, no offense meant here, your comments reveal a nearly complete lack of
knoledge of SQL Server internals.
Unless you understand the relationship between the Data Files, the Buffer
Cache and the Transaction Log, and how a query is processed, you shouldn't
make design decisions based on performance. Just create a design that is
simple, models your business domain reasonably well and is reasonably
relationally correct, and performance will usually take care of itself. In
fact, even if you do know all the performance details, you still shoudn't
make design decisions based on performance.
To learn more about how SQL Server stores data and processes queries, there
are some OK topic in Books Online.
Write-Ahead Transaction Log
http://msdn2.microsoft.com/en-us/library/ms186259.aspx
I/O Architecture
http://msdn.microsoft.com/library/d...br />
3339.asp
And Inside Microsoft SQL Server 2000 is the standard reference. Inside
Microsoft SQL Server 2005 is in the process of beging released in multiple
volumes.
David|||I guess the key part of of my question is: assuming that logical database
design correction is the same, would it make perf difference if they are
separate in different databases? thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
> Zen wrote:
> That is absolutely no reason to consider using separate databases. As
> Jerry says, there is a whole range of ways to optimize database
> performance and all of them are available to you in a single database.
> The valid reasons you might need separate databases are if the data has
> different requirements for availability, security, backup or for other
> administrative issues, not for performance. I'd also add that you are
> going about your design the wrong way if you are considering
> performance issues before you've planned and tested a logical database
> design.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Zen wrote:
> I guess the key part of of my question is: assuming that logical database
> design correction is the same, would it make perf difference if they are
> separate in different databases? thanks!
>
Not really the right question because performance depends on other
factors. Put it this way: using separate databases doesn't give you any
extra options for enhancing performance - for example data can be
partitioned across multiple files even within the same database. So it
would be more accurate to say that you can achieve the same performance
with one database as you can with multiple databases - assuming that
all the other database administration factors are uniform across your
data.
You haven't given us any idea what kind of data volumes you are talking
about. Multiple-terabyte databases are common currency today. If you
are at or approaching terabyte scale however, you should hire some
proper expertise now because mistakes are easily made and can be very
expensive to fix.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Zen,
It "could" in certain situations i.e., distributed partitioned views in a
federation of servers however this is a high-availability solution for
specific demonstrated performance issues and not generally a first pass
optimization technique. I would recommend you stick with one database in
this case.
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:O3G$64DgGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I guess the key part of of my question is: assuming that logical database
>design correction is the same, would it make perf difference if they are
>separate in different databases? thanks!
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
>|||Jerry Spivey wrote:
> Zen,
> It "could" in certain situations i.e., distributed partitioned views in a
> federation of servers however this is a high-availability solution for
> specific demonstrated performance issues and not generally a first pass
> optimization technique. I would recommend you stick with one database in
> this case.
> HTH
>
Ah, that's a good point. If "separate databases" also means "separate
servers" then there obviously might be a reason for doing it.
The point I was trying to make was this: If you take one database and
naively make an identical copy of that database on the same server then
you won't achieve anything useful. True, you will have created at least
two new files (one data, one log) and depending on the placement of
those files you may perhaps see some performance impact. But then you
might have achieved the same thing by creating those extra files in one
database instead of two. So performance always depends on things other
than the number of databases. As you say, it depends on the number of
servers for instance :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Tuesday, March 27, 2012

Determine Report Permissions with T-SQL

Determine month from inside month row grouping

Thursday, March 22, 2012

Detecting Expanded Groups, etc...

Monday, March 19, 2012

Details grouping

Details Grouping

Saturday, February 25, 2012

Designing groups in multiple columns

Design/Modeling books and or advise wanted.

Friday, February 24, 2012

Design Slow

Design Questions on File Groups and Files

Design Questions on File Groups and Files

Design Questions on File Groups and Files

Design question from data mining newbie

Sunday, February 19, 2012

Design Question

Friday, February 17, 2012

design issue: better speed performance by separating into different databases?

design issue: better speed performance by separating into different databases?

Determining Security

Blog Archive

About Me