Hi! I post a question last week but didn't get any responses, so trying it
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4
Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>
|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>
|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>
Showing posts with label didnt. Show all posts
Showing posts with label didnt. Show all posts
Wednesday, March 7, 2012
Logical Data Modeling Problem
I'm not sure if this is the place to post this or not but I didn't find any
newsgroups specifically for logical data modeling problems. I'm trying to
structure a relational database and the problem is that the complexity has
gotten beyond my level of knowledge. If anyone could help me out it would
be much appreciated.
This database contains a record of projects along with details about the
projects, primarily financial information. The logical entities (tables)
are:
A) Project
B) Funding Sources (many to many relationship with Project - each Project
can have multiple funding sources)
C) Years (many to many relationship with Project - each Project can span
multiple years)
Here's where I encounter my problem. For each project there is a dollar
amount associated with each funding source and year. Here's a sample
project:
Project: Sample
Funding Source A - Year 2005 - $20,000
Funding Source A - Year 2006 - $30,000
Funding Source B - Year 2005 - $10,000
Funding Source B - Year 2006 - $3,000
In tabular form Funding sources would be across the top (columns), while
Years would be across the side (rows).
So my question is that 4th table that contains the dollar amount. Would it
have a many to many relationship with all 3 other entities (Project,
Sources, Years)? Or is there another way to model this relational database?
Thanks for any assistance,
Ryan
There is sketchy information to make much sense about your needs.
Off the top, it seems like the [Years] table should really be [YearsFunding]
and contain columns such as [Year],[ProjectID],[FundSourceID],[Amount].
Unless, of course, the 'unsaid' information shows a different usage for the
[Years] table.
(It is very difficult to advise modeling issues without knowing the entire
domain to model.)
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
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> I'm not sure if this is the place to post this or not but I didn't find
> any newsgroups specifically for logical data modeling problems. I'm
> trying to structure a relational database and the problem is that the
> complexity has gotten beyond my level of knowledge. If anyone could help
> me out it would be much appreciated.
> This database contains a record of projects along with details about the
> projects, primarily financial information. The logical entities (tables)
> are:
> A) Project
> B) Funding Sources (many to many relationship with Project - each Project
> can have multiple funding sources)
> C) Years (many to many relationship with Project - each Project can span
> multiple years)
> Here's where I encounter my problem. For each project there is a dollar
> amount associated with each funding source and year. Here's a sample
> project:
> Project: Sample
> Funding Source A - Year 2005 - $20,000
> Funding Source A - Year 2006 - $30,000
> Funding Source B - Year 2005 - $10,000
> Funding Source B - Year 2006 - $3,000
> In tabular form Funding sources would be across the top (columns), while
> Years would be across the side (rows).
> So my question is that 4th table that contains the dollar amount. Would
> it have a many to many relationship with all 3 other entities (Project,
> Sources, Years)? Or is there another way to model this relational
> database?
> Thanks for any assistance,
> Ryan
>
|||This is how I originally set it up, exactly how you described. Sounds like
I had it right the first time. Thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23BrrXVnDHHA.4808@.TK2MSFTNGP03.phx.gbl...
> There is sketchy information to make much sense about your needs.
> Off the top, it seems like the [Years] table should really be
> [YearsFunding] and contain columns such as
> [Year],[ProjectID],[FundSourceID],[Amount].
> Unless, of course, the 'unsaid' information shows a different usage for
> the [Years] table.
> (It is very difficult to advise modeling issues without knowing the entire
> domain to model.)
> --
> 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
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
>
|||"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> I'm not sure if this is the place to post this or not but I didn't find
> any newsgroups specifically for logical data modeling problems. I'm
> trying to structure a relational database and the problem is that the
> complexity has gotten beyond my level of knowledge. If anyone could help
> me out it would be much appreciated.
> This database contains a record of projects along with details about the
> projects, primarily financial information. The logical entities (tables)
> are:
> A) Project
> B) Funding Sources (many to many relationship with Project - each Project
> can have multiple funding sources)
> C) Years (many to many relationship with Project - each Project can span
> multiple years)
> Here's where I encounter my problem. For each project there is a dollar
> amount associated with each funding source and year. Here's a sample
> project:
> Project: Sample
> Funding Source A - Year 2005 - $20,000
> Funding Source A - Year 2006 - $30,000
> Funding Source B - Year 2005 - $10,000
> Funding Source B - Year 2006 - $3,000
> In tabular form Funding sources would be across the top (columns), while
> Years would be across the side (rows).
In the UI maybe, but in the database the years should be on rows.
> So my question is that 4th table that contains the dollar amount. Would
> it have a many to many relationship with all 3 other entities (Project,
> Sources, Years)?
It would have a one-to-many relationship with each of those tables.
Something like:
create table ProjectFunding
(
ProjectID int not null
references Project on delete cascade,
FundingSourceID int not null
references FundingSource,
FundingYear datetime not null
references FundingYears,
FundingAmount decimal(17,2) not null,
constraint pk_Funding
primary key (ProjectID,FundingSourceID,FundingYear)
)
create index ix_Funding_Source on Funding(FundingSourceID)
David
newsgroups specifically for logical data modeling problems. I'm trying to
structure a relational database and the problem is that the complexity has
gotten beyond my level of knowledge. If anyone could help me out it would
be much appreciated.
This database contains a record of projects along with details about the
projects, primarily financial information. The logical entities (tables)
are:
A) Project
B) Funding Sources (many to many relationship with Project - each Project
can have multiple funding sources)
C) Years (many to many relationship with Project - each Project can span
multiple years)
Here's where I encounter my problem. For each project there is a dollar
amount associated with each funding source and year. Here's a sample
project:
Project: Sample
Funding Source A - Year 2005 - $20,000
Funding Source A - Year 2006 - $30,000
Funding Source B - Year 2005 - $10,000
Funding Source B - Year 2006 - $3,000
In tabular form Funding sources would be across the top (columns), while
Years would be across the side (rows).
So my question is that 4th table that contains the dollar amount. Would it
have a many to many relationship with all 3 other entities (Project,
Sources, Years)? Or is there another way to model this relational database?
Thanks for any assistance,
Ryan
There is sketchy information to make much sense about your needs.
Off the top, it seems like the [Years] table should really be [YearsFunding]
and contain columns such as [Year],[ProjectID],[FundSourceID],[Amount].
Unless, of course, the 'unsaid' information shows a different usage for the
[Years] table.
(It is very difficult to advise modeling issues without knowing the entire
domain to model.)
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
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> I'm not sure if this is the place to post this or not but I didn't find
> any newsgroups specifically for logical data modeling problems. I'm
> trying to structure a relational database and the problem is that the
> complexity has gotten beyond my level of knowledge. If anyone could help
> me out it would be much appreciated.
> This database contains a record of projects along with details about the
> projects, primarily financial information. The logical entities (tables)
> are:
> A) Project
> B) Funding Sources (many to many relationship with Project - each Project
> can have multiple funding sources)
> C) Years (many to many relationship with Project - each Project can span
> multiple years)
> Here's where I encounter my problem. For each project there is a dollar
> amount associated with each funding source and year. Here's a sample
> project:
> Project: Sample
> Funding Source A - Year 2005 - $20,000
> Funding Source A - Year 2006 - $30,000
> Funding Source B - Year 2005 - $10,000
> Funding Source B - Year 2006 - $3,000
> In tabular form Funding sources would be across the top (columns), while
> Years would be across the side (rows).
> So my question is that 4th table that contains the dollar amount. Would
> it have a many to many relationship with all 3 other entities (Project,
> Sources, Years)? Or is there another way to model this relational
> database?
> Thanks for any assistance,
> Ryan
>
|||This is how I originally set it up, exactly how you described. Sounds like
I had it right the first time. Thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23BrrXVnDHHA.4808@.TK2MSFTNGP03.phx.gbl...
> There is sketchy information to make much sense about your needs.
> Off the top, it seems like the [Years] table should really be
> [YearsFunding] and contain columns such as
> [Year],[ProjectID],[FundSourceID],[Amount].
> Unless, of course, the 'unsaid' information shows a different usage for
> the [Years] table.
> (It is very difficult to advise modeling issues without knowing the entire
> domain to model.)
> --
> 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
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
>
|||"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> I'm not sure if this is the place to post this or not but I didn't find
> any newsgroups specifically for logical data modeling problems. I'm
> trying to structure a relational database and the problem is that the
> complexity has gotten beyond my level of knowledge. If anyone could help
> me out it would be much appreciated.
> This database contains a record of projects along with details about the
> projects, primarily financial information. The logical entities (tables)
> are:
> A) Project
> B) Funding Sources (many to many relationship with Project - each Project
> can have multiple funding sources)
> C) Years (many to many relationship with Project - each Project can span
> multiple years)
> Here's where I encounter my problem. For each project there is a dollar
> amount associated with each funding source and year. Here's a sample
> project:
> Project: Sample
> Funding Source A - Year 2005 - $20,000
> Funding Source A - Year 2006 - $30,000
> Funding Source B - Year 2005 - $10,000
> Funding Source B - Year 2006 - $3,000
> In tabular form Funding sources would be across the top (columns), while
> Years would be across the side (rows).
In the UI maybe, but in the database the years should be on rows.
> So my question is that 4th table that contains the dollar amount. Would
> it have a many to many relationship with all 3 other entities (Project,
> Sources, Years)?
It would have a one-to-many relationship with each of those tables.
Something like:
create table ProjectFunding
(
ProjectID int not null
references Project on delete cascade,
FundingSourceID int not null
references FundingSource,
FundingYear datetime not null
references FundingYears,
FundingAmount decimal(17,2) not null,
constraint pk_Funding
primary key (ProjectID,FundingSourceID,FundingYear)
)
create index ix_Funding_Source on Funding(FundingSourceID)
David
Subscribe to:
Posts (Atom)