Wednesday, March 28, 2012
Performance different
data and one hold July data. I ran the query that hits 4 tables. Tables on
both databases are identical including indexes and keys. It took 40 seconds
when I run under June database. When I run under July, it did not return
the result set and it been running more than 2 hours. I checked the number
of data on those 4 tables and they are basically the same. Is there a way
for me to know what is going on? I used Profiler but no help. The query is
single commit transaction. Please help. Thanks!Your most likely blocked. Run sp_who2 to see who is blocking you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> I have 2 databases that are identical in schema and structure, one hold
June
> data and one hold July data. I ran the query that hits 4 tables. Tables
on
> both databases are identical including indexes and keys. It took 40
seconds
> when I run under June database. When I run under July, it did not return
> the result set and it been running more than 2 hours. I checked the
number
> of data on those 4 tables and they are basically the same. Is there a way
> for me to know what is going on? I used Profiler but no help. The query
is
> single commit transaction. Please help. Thanks!
>|||I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
found was shared locks. This is for the data warehouse environment and we
only have select statments. Thanks for the recommendation.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> Your most likely blocked. Run sp_who2 to see who is blocking you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > I have 2 databases that are identical in schema and structure, one hold
> June
> > data and one hold July data. I ran the query that hits 4 tables.
Tables
> on
> > both databases are identical including indexes and keys. It took 40
> seconds
> > when I run under June database. When I run under July, it did not
return
> > the result set and it been running more than 2 hours. I checked the
> number
> > of data on those 4 tables and they are basically the same. Is there a
way
> > for me to know what is going on? I used Profiler but no help. The
query
> is
> > single commit transaction. Please help. Thanks!
> >
> >
>|||Is the estimated query plan the same for both? What is the status of the
spid while it is running? Is there activity going on (disk, cpu etc)?
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> found was shared locks. This is for the data warehouse environment and we
> only have select statments. Thanks for the recommendation.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > Your most likely blocked. Run sp_who2 to see who is blocking you.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > I have 2 databases that are identical in schema and structure, one
hold
> > June
> > > data and one hold July data. I ran the query that hits 4 tables.
> Tables
> > on
> > > both databases are identical including indexes and keys. It took 40
> > seconds
> > > when I run under June database. When I run under July, it did not
> return
> > > the result set and it been running more than 2 hours. I checked the
> > number
> > > of data on those 4 tables and they are basically the same. Is there a
> way
> > > for me to know what is going on? I used Profiler but no help. The
> query
> > is
> > > single commit transaction. Please help. Thanks!
> > >
> > >
> >
> >
>|||I was not able to see the execution plan for the slow one because it didn't
stop. But the query structure, tables, indexes are identical. The SPID for
both are SELECT and using parallelism. The CPU for both are spiking, disk
write/sec, page write/sec, data map hits, and lazy write pages/sec are at
constant flat lines.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Is the estimated query plan the same for both? What is the status of the
> spid while it is running? Is there activity going on (disk, cpu etc)?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> > found was shared locks. This is for the data warehouse environment and
we
> > only have select statments. Thanks for the recommendation.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > I have 2 databases that are identical in schema and structure, one
> hold
> > > June
> > > > data and one hold July data. I ran the query that hits 4 tables.
> > Tables
> > > on
> > > > both databases are identical including indexes and keys. It took 40
> > > seconds
> > > > when I run under June database. When I run under July, it did not
> > return
> > > > the result set and it been running more than 2 hours. I checked the
> > > number
> > > > of data on those 4 tables and they are basically the same. Is there
a
> > way
> > > > for me to know what is going on? I used Profiler but no help. The
> > query
> > > is
> > > > single commit transaction. Please help. Thanks!
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kevin,
You can do an Estimated query plan without actually running the query. In
Query Analyzer you can highlight the query and press Ctrl + L to see it. By
"all flat lines" do you mean maxed out? If so then your are most likely
doing a full table scan. The estimated query plan will tell you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> I was not able to see the execution plan for the slow one because it
didn't
> stop. But the query structure, tables, indexes are identical. The SPID
for
> both are SELECT and using parallelism. The CPU for both are spiking, disk
> write/sec, page write/sec, data map hits, and lazy write pages/sec are at
> constant flat lines.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > Is the estimated query plan the same for both? What is the status of
the
> > spid while it is running? Is there activity going on (disk, cpu etc)?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks
I
> > > found was shared locks. This is for the data warehouse environment
and
> we
> > > only have select statments. Thanks for the recommendation.
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > I have 2 databases that are identical in schema and structure, one
> > hold
> > > > June
> > > > > data and one hold July data. I ran the query that hits 4 tables.
> > > Tables
> > > > on
> > > > > both databases are identical including indexes and keys. It took
40
> > > > seconds
> > > > > when I run under June database. When I run under July, it did not
> > > return
> > > > > the result set and it been running more than 2 hours. I checked
the
> > > > number
> > > > > of data on those 4 tables and they are basically the same. Is
there
> a
> > > way
> > > > > for me to know what is going on? I used Profiler but no help.
The
> > > query
> > > > is
> > > > > single commit transaction. Please help. Thanks!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you for your suggestion. After figuring what the Estimated query plan
does, it is caused by recursive loops. One month return 2,500 rows while
the other one return 15,000. These recursively 3 times and that make the
query run on 2nd database longer. I was able fix the query so it run
faster.
THANK YOU Andrew for your help !!!!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ufQspyXrDHA.2964@.tk2msftngp13.phx.gbl...
> Kevin,
> You can do an Estimated query plan without actually running the query. In
> Query Analyzer you can highlight the query and press Ctrl + L to see it.
By
> "all flat lines" do you mean maxed out? If so then your are most likely
> doing a full table scan. The estimated query plan will tell you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > I was not able to see the execution plan for the slow one because it
> didn't
> > stop. But the query structure, tables, indexes are identical. The SPID
> for
> > both are SELECT and using parallelism. The CPU for both are spiking,
disk
> > write/sec, page write/sec, data map hits, and lazy write pages/sec are
at
> > constant flat lines.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > > Is the estimated query plan the same for both? What is the status of
> the
> > > spid while it is running? Is there activity going on (disk, cpu etc)?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > > I ran sp_lock & sp_who2 and found no exclusive locks. The only
locks
> I
> > > > found was shared locks. This is for the data warehouse environment
> and
> > we
> > > > only have select statments. Thanks for the recommendation.
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > > I have 2 databases that are identical in schema and structure,
one
> > > hold
> > > > > June
> > > > > > data and one hold July data. I ran the query that hits 4
tables.
> > > > Tables
> > > > > on
> > > > > > both databases are identical including indexes and keys. It
took
> 40
> > > > > seconds
> > > > > > when I run under June database. When I run under July, it did
not
> > > > return
> > > > > > the result set and it been running more than 2 hours. I checked
> the
> > > > > number
> > > > > > of data on those 4 tables and they are basically the same. Is
> there
> > a
> > > > way
> > > > > > for me to know what is going on? I used Profiler but no help.
> The
> > > > query
> > > > > is
> > > > > > single commit transaction. Please help. Thanks!
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Monday, March 12, 2012
Performance and data structure question
I have a table structure question. I will have a table 'Models' that
has one to many 'incomes' and one to many 'costs'. These 2 entities
have exactly the same structure, which is 7 smallmoney and a name. Is
it better to create a table 'Incomes' and a table 'Costs', with both
the same number of fields like this :
Incomes
----
in_idmodel
in_1
in_2
in_3
in_4
in_5
in_6
in_7
in_name
Costs
----
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name
or is it better to create one single table that will contain both
entities like that :
Incomes_Costs
----
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome
which only differs from the 2 above by the isIncome field to know which
row is an income and which row is a cost.
I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.
Thanks all for your time!
ibizaBy no means a "Guru"...but If I'm following you...
This is the age old question of "to normalize" or not to "normalize".
By splitting the tables you achieve a higher level of normalization.
This will cause queries etc to run somewhat quicker..but you wll take a
hit in terms of storage space.
By making one table...you will speed up your queries...but waste space.
HTH
MJKulangara
http://sqladventures.blogspot.com|||Ok, I am really speeding up my queries (by 1/1000ths of second -_-)with
two table? I thought it would be the inverse...how come?
And about the storage, why would it take more space? I mean, if I have
4 incomes and 4 costs splitted into two tables, it's about the same
amout of data as 4+4 rows in the same table (I suppose the only
difference is one bit for each row for the 1 table layout VS some tiny
space used for the definition of one more table in the 2 tables
layout?)
The point here is I want to get to know these little subtleties...
thanks for your advice!
ibiza|||ibiza (lambertb@.gmail.com) writes:
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :
That is not really possible to answer without further knowledge about
the business domain. And, even I would have it, I would maybe still be
a trade-off for me which way to do it.
A key here is how related they are. If they mirror each other, and
are two sides of the same coin, it may make sense to have them in same
table. If they are unrelated, they should not be in the same table.
Another observation:
> Incomes
> ----
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name
Maybe it makes sense to have seven columns, but a more conventional
design would be to have a main table:
CREATE TABLE incomes (in_idmodel int NOT NULL,
in_name varchar(30) NOT NULL,
CONSTRAINT pk PRIMARY KEY(in_idmodel))
CREATE TABLE incomerows (in_idmodel int NOT NULL,
rowno smallint NOT NULL,
value smallmoney NOT NULL,
CONSTRAINT pk2 PRIMARY KEY (id_idmodel, rowno))
An important advantage is that this design is not tied to fixed number
of levels.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hmmm. i'd think you would be happier with even more abstraction then
either option.
have one table for models. it has the modelid, and the modelname, and
one row for each model.
then have one table for "dollars."
this will have "dollarid", a flag for cost or income, and a human
name. there will be one row for each type of income or cost, so the
total rows will be something like 14.
then have the "xref" table. It will have modelid, dollarid, and
dollaramt.
this will have one row for each model for each cost/income.
the advantage is that over time new costs or incomes will show up, and
you can easily add them. further, you don't have to have really awful
program logic to add up all of your costs and income for a specfiic
model.
hope this helps,
doug
Performance and data structure question
I have a table structure question. I will have a table 'Models' that
has one to many 'incomes' and one to many 'costs'. These 2 entities
have exactly the same structure, which is 7 smallmoney and a name. Is
it better to create a table 'Incomes' and a table 'Costs', with both
the same number of fields like this :
Incomes
in_idmodel
in_1
in_2
in_3
in_4
in_5
in_6
in_7
in_name
Costs
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name
or is it better to create one single table that will contain both
entities like that :
Incomes_Costs
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome
which only differs from the 2 above by the isIncome field to know which
row is an income and which row is a cost.
I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.
Thanks all for your time!
ibiza
Ibiza,
for a while no response to your post, I'll post mine. I would not claim me
being an SQL guru (the reason people shied away from responding?), though I
consider myself a seasoned SQL Server professional.
To me there is no doubt that they should be two separate tables. Even
though they look structurally alike, logically they are different things.
They may be used together in some purpose (like calculating net profit), but
one is the cost and the other one is the income.
One thing made them seemingly alike is your way of naming the columns, which
I think is not so optimal and introduced the illusion. Instead of in_1,
in_2, c_1, c_2 etc., I would rather name them TV_ad, Newspaper_ad, etc.,
that way you know from the name of the columns what for income or expense is
being referred to, and you would not have the illusion that they might be
the same thing. Beyond, after the 7 or so items, when in the future you
realize there are other cost that not have an income counterpart (we
actually can not talk about counterpart, but just let's say so), you would
benefit from the separated tables.
Further beyond, I am questioning whether those two tables are the right
approach. To me I should have a income table and a cost table, with the
columns id, source (or expense_item for cost), amount. your id would map to
the Model table so you would not have the name in the cost or income tables.
You may want to have check tables for the source or expense_item as
constraint for input to the income or cost table. Whether to use these
check tables is a question of denormalization, you may choose not to use it,
just like you have chosen to denormalize your Models and Income/Cost tables.
Quentin
"ibiza" <lambertb@.gmail.com> wrote in message
news:1138898200.231048.286540@.g47g2000cwa.googlegr oups.com...
> Hi SQL gurus,
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :
> Incomes
> --
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name
> Costs
> --
> c_idmodel
> c_1
> c_2
> c_3
> c_4
> c_5
> c_6
> c_7
> c_name
> or is it better to create one single table that will contain both
> entities like that :
> Incomes_Costs
> --
> ic_idmodel
> ic_1
> ic_2
> ic_3
> ic_4
> ic_5
> ic_6
> ic_7
> ic_name
> ic_isIncome
> which only differs from the 2 above by the isIncome field to know which
> row is an income and which row is a cost.
> I'd like to know which method is the best in terms of performance and
> general structure and would greatly appreciate if you explain a little
> the reasons that drove you to suggest me a method over the other.
> Thanks all for your time!
> ibiza
>
|||Thank you very much for your advice Quentin.
As for my simplistic naming of rows with only numbers, it's because an
income/cost consists of 7 values, each one corresponding to a year. The
middle column(in_4) corresponds to the value of the actual year, the 3
columns before correspond each to years before the actual year and the
3 columns after correspond each to years before the actual year. For
example, if the model was created in 2006, then in_1 would represent
income for year 2003, in_2 : 2004, in_3 : 2005, in_4 : 2006, in_5 :
2007, in_6 : 2008 and in_7 : 2009. As a models can be created for any
year, I cannot really associate each column of income/cost to a
name...I hope I'm not too foggy here and you still understand my logic.
Am I still doing it the correct way?
Thanks a lot!
ibiza
Performance and data structure question
I have a table structure question. I will have a table 'Models' that
has one to many 'incomes' and one to many 'costs'. These 2 entities
have exactly the same structure, which is 7 smallmoney and a name. Is
it better to create a table 'Incomes' and a table 'Costs', with both
the same number of fields like this :
Incomes
--
in_idmodel
in_1
in_2
in_3
in_4
in_5
in_6
in_7
in_name
Costs
--
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name
or is it better to create one single table that will contain both
entities like that :
Incomes_Costs
--
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome
which only differs from the 2 above by the isIncome field to know which
row is an income and which row is a cost.
I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.
Thanks all for your time!
ibizaIbiza,
for a while no response to your post, I'll post mine. I would not claim me
being an SQL guru (the reason people shied away from responding?), though I
consider myself a seasoned SQL Server professional.
To me there is no doubt that they should be two separate tables. Even
though they look structurally alike, logically they are different things.
They may be used together in some purpose (like calculating net profit), but
one is the cost and the other one is the income.
One thing made them seemingly alike is your way of naming the columns, which
I think is not so optimal and introduced the illusion. Instead of in_1,
in_2, c_1, c_2 etc., I would rather name them TV_ad, Newspaper_ad, etc.,
that way you know from the name of the columns what for income or expense is
being referred to, and you would not have the illusion that they might be
the same thing. Beyond, after the 7 or so items, when in the future you
realize there are other cost that not have an income counterpart (we
actually can not talk about counterpart, but just let's say so), you would
benefit from the separated tables.
Further beyond, I am questioning whether those two tables are the right
approach. To me I should have a income table and a cost table, with the
columns id, source (or expense_item for cost), amount. your id would map to
the Model table so you would not have the name in the cost or income tables.
You may want to have check tables for the source or expense_item as
constraint for input to the income or cost table. Whether to use these
check tables is a question of denormalization, you may choose not to use it,
just like you have chosen to denormalize your Models and Income/Cost tables.
Quentin
"ibiza" <lambertb@.gmail.com> wrote in message
news:1138898200.231048.286540@.g47g2000cwa.googlegroups.com...
> Hi SQL gurus,
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :
> Incomes
> --
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name
> Costs
> --
> c_idmodel
> c_1
> c_2
> c_3
> c_4
> c_5
> c_6
> c_7
> c_name
> or is it better to create one single table that will contain both
> entities like that :
> Incomes_Costs
> --
> ic_idmodel
> ic_1
> ic_2
> ic_3
> ic_4
> ic_5
> ic_6
> ic_7
> ic_name
> ic_isIncome
> which only differs from the 2 above by the isIncome field to know which
> row is an income and which row is a cost.
> I'd like to know which method is the best in terms of performance and
> general structure and would greatly appreciate if you explain a little
> the reasons that drove you to suggest me a method over the other.
> Thanks all for your time!
> ibiza
>|||Thank you very much for your advice Quentin.
As for my simplistic naming of rows with only numbers, it's because an
income/cost consists of 7 values, each one corresponding to a year. The
middle column(in_4) corresponds to the value of the actual year, the 3
columns before correspond each to years before the actual year and the
3 columns after correspond each to years before the actual year. For
example, if the model was created in 2006, then in_1 would represent
income for year 2003, in_2 : 2004, in_3 : 2005, in_4 : 2006, in_5 :
2007, in_6 : 2008 and in_7 : 2009. As a models can be created for any
year, I cannot really associate each column of income/cost to a
name...I hope I'm not too foggy here and you still understand my logic.
Am I still doing it the correct way?
Thanks a lot!
ibiza
Performance and data structure question
I have a table structure question. I will have a table 'Models' that
has one to many 'incomes' and one to many 'costs'. These 2 entities
have exactly the same structure, which is 7 smallmoney and a name. Is
it better to create a table 'Incomes' and a table 'Costs', with both
the same number of fields like this :
Incomes
--
in_idmodel
in_1
in_2
in_3
in_4
in_5
in_6
in_7
in_name
Costs
--
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name
or is it better to create one single table that will contain both
entities like that :
Incomes_Costs
--
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome
which only differs from the 2 above by the isIncome field to know which
row is an income and which row is a cost.
I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.
Thanks all for your time!
ibizaIbiza,
for a while no response to your post, I'll post mine. I would not claim me
being an SQL guru (the reason people shied away from responding?), though I
consider myself a seasoned SQL Server professional.
To me there is no doubt that they should be two separate tables. Even
though they look structurally alike, logically they are different things.
They may be used together in some purpose (like calculating net profit), but
one is the cost and the other one is the income.
One thing made them seemingly alike is your way of naming the columns, which
I think is not so optimal and introduced the illusion. Instead of in_1,
in_2, c_1, c_2 etc., I would rather name them TV_ad, Newspaper_ad, etc.,
that way you know from the name of the columns what for income or expense is
being referred to, and you would not have the illusion that they might be
the same thing. Beyond, after the 7 or so items, when in the future you
realize there are other cost that not have an income counterpart (we
actually can not talk about counterpart, but just let's say so), you would
benefit from the separated tables.
Further beyond, I am questioning whether those two tables are the right
approach. To me I should have a income table and a cost table, with the
columns id, source (or expense_item for cost), amount. your id would map to
the Model table so you would not have the name in the cost or income tables.
You may want to have check tables for the source or expense_item as
constraint for input to the income or cost table. Whether to use these
check tables is a question of denormalization, you may choose not to use it,
just like you have chosen to denormalize your Models and Income/Cost tables.
Quentin
"ibiza" <lambertb@.gmail.com> wrote in message
news:1138898200.231048.286540@.g47g2000cwa.googlegroups.com...
> Hi SQL gurus,
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :
> Incomes
> --
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name
> Costs
> --
> c_idmodel
> c_1
> c_2
> c_3
> c_4
> c_5
> c_6
> c_7
> c_name
> or is it better to create one single table that will contain both
> entities like that :
> Incomes_Costs
> --
> ic_idmodel
> ic_1
> ic_2
> ic_3
> ic_4
> ic_5
> ic_6
> ic_7
> ic_name
> ic_isIncome
> which only differs from the 2 above by the isIncome field to know which
> row is an income and which row is a cost.
> I'd like to know which method is the best in terms of performance and
> general structure and would greatly appreciate if you explain a little
> the reasons that drove you to suggest me a method over the other.
> Thanks all for your time!
> ibiza
>|||Thank you very much for your advice Quentin.
As for my simplistic naming of rows with only numbers, it's because an
income/cost consists of 7 values, each one corresponding to a year. The
middle column(in_4) corresponds to the value of the actual year, the 3
columns before correspond each to years before the actual year and the
3 columns after correspond each to years before the actual year. For
example, if the model was created in 2006, then in_1 would represent
income for year 2003, in_2 : 2004, in_3 : 2005, in_4 : 2006, in_5 :
2007, in_6 : 2008 and in_7 : 2009. As a models can be created for any
year, I cannot really associate each column of income/cost to a
name...I hope I'm not too foggy here and you still understand my logic.
Am I still doing it the correct way?
Thanks a lot!
ibiza
Performance 1proc vs. 2proc
pricing structure of PER PROCESSOR pricing, can someone tell me is the
benefit of a 2nd processor worth the cost on a database server that is
primarily a backend to multiple web based apps?
ThanksMSUTech,
There is no way to say without knowing your load. It is not a matter of the
client (e.g. web app, VB app, etc.) but a matter of the kind of queries that
you are running. If your workload is not very heavy and the queries return
only small row sets, then you are probably fine.
However, a second processor can come in very handy with a greater load or a
mixture of long and short running queries. Why? Because it actually has
another physical processor (and that does matter) to run additional requests
when one processor is very busy. Most of the benefits you can get with a
multi-core processor (not hyperthreaded) since the cores are mostly
independent AND because Microsoft views a multi-core processor as a single
processor. (Very nice of them.)
But, bottom line is that you have to figure the cost/benefit based on your
workload. How much time do you spend waiting? How long do your average and
maximum length queries run? How much are you updating the database? What
kind of locking or blocking are you seeing?
And get a multi-core processor since it has no impact on your licensing.
RLF
"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
> the
> pricing structure of PER PROCESSOR pricing, can someone tell me is the
> benefit of a 2nd processor worth the cost on a database server that is
> primarily a backend to multiple web based apps?
> Thanks|||Russell,
If you have more than 1 processor then at the very least SQL and have its
own processor and Windows and other processes could run on the other
processor if you configure the server that way. Sometimes we forget the
processes that Windows wants/needs to do while SQL is running.
Chris
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
> MSUTech,
> There is no way to say without knowing your load. It is not a matter of
> the client (e.g. web app, VB app, etc.) but a matter of the kind of
> queries that you are running. If your workload is not very heavy and the
> queries return only small row sets, then you are probably fine.
> However, a second processor can come in very handy with a greater load or
> a mixture of long and short running queries. Why? Because it actually
> has another physical processor (and that does matter) to run additional
> requests when one processor is very busy. Most of the benefits you can
> get with a multi-core processor (not hyperthreaded) since the cores are
> mostly independent AND because Microsoft views a multi-core processor as a
> single processor. (Very nice of them.)
> But, bottom line is that you have to figure the cost/benefit based on your
> workload. How much time do you spend waiting? How long do your average
> and maximum length queries run? How much are you updating the database?
> What kind of locking or blocking are you seeing?
> And get a multi-core processor since it has no impact on your licensing.
> RLF
> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
>> the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>|||Chris,
True enough. For example, even staying within the SQL Server domain,
running DTS / SSIS is another process.
If you have a dual processor machine, however, you need to license SQL
Server for two processors. The licensing does not have a provision for
promising never to use the other processor, although it is possible to do
so.
But a multi-core (dual or quad) processor brings most of the benefits of two
processors while still being licensed as a single processor. So, the
multi-core processor benefits would extend to Windows and other processes
that might run on the machine.
RLF
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:OhOpU9upIHA.6096@.TK2MSFTNGP06.phx.gbl...
> Russell,
> If you have more than 1 processor then at the very least SQL and have its
> own processor and Windows and other processes could run on the other
> processor if you configure the server that way. Sometimes we forget the
> processes that Windows wants/needs to do while SQL is running.
> Chris
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
>> MSUTech,
>> There is no way to say without knowing your load. It is not a matter of
>> the client (e.g. web app, VB app, etc.) but a matter of the kind of
>> queries that you are running. If your workload is not very heavy and the
>> queries return only small row sets, then you are probably fine.
>> However, a second processor can come in very handy with a greater load or
>> a mixture of long and short running queries. Why? Because it actually
>> has another physical processor (and that does matter) to run additional
>> requests when one processor is very busy. Most of the benefits you can
>> get with a multi-core processor (not hyperthreaded) since the cores are
>> mostly independent AND because Microsoft views a multi-core processor as
>> a single processor. (Very nice of them.)
>> But, bottom line is that you have to figure the cost/benefit based on
>> your workload. How much time do you spend waiting? How long do your
>> average and maximum length queries run? How much are you updating the
>> database? What kind of locking or blocking are you seeing?
>> And get a multi-core processor since it has no impact on your licensing.
>> RLF
>> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
>> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
>> the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>>
>|||I should have been a little clearer with multiple CPU's meaning multi-core
for licencing benefits.
Chris
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e3Bs3PvpIHA.4928@.TK2MSFTNGP04.phx.gbl...
> Chris,
> True enough. For example, even staying within the SQL Server domain,
> running DTS / SSIS is another process.
> If you have a dual processor machine, however, you need to license SQL
> Server for two processors. The licensing does not have a provision for
> promising never to use the other processor, although it is possible to do
> so.
> But a multi-core (dual or quad) processor brings most of the benefits of
> two processors while still being licensed as a single processor. So, the
> multi-core processor benefits would extend to Windows and other processes
> that might run on the machine.
> RLF
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:OhOpU9upIHA.6096@.TK2MSFTNGP06.phx.gbl...
>> Russell,
>> If you have more than 1 processor then at the very least SQL and have its
>> own processor and Windows and other processes could run on the other
>> processor if you configure the server that way. Sometimes we forget the
>> processes that Windows wants/needs to do while SQL is running.
>> Chris
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
>> MSUTech,
>> There is no way to say without knowing your load. It is not a matter of
>> the client (e.g. web app, VB app, etc.) but a matter of the kind of
>> queries that you are running. If your workload is not very heavy and
>> the queries return only small row sets, then you are probably fine.
>> However, a second processor can come in very handy with a greater load
>> or a mixture of long and short running queries. Why? Because it
>> actually has another physical processor (and that does matter) to run
>> additional requests when one processor is very busy. Most of the
>> benefits you can get with a multi-core processor (not hyperthreaded)
>> since the cores are mostly independent AND because Microsoft views a
>> multi-core processor as a single processor. (Very nice of them.)
>> But, bottom line is that you have to figure the cost/benefit based on
>> your workload. How much time do you spend waiting? How long do your
>> average and maximum length queries run? How much are you updating the
>> database? What kind of locking or blocking are you seeing?
>> And get a multi-core processor since it has no impact on your licensing.
>> RLF
>> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
>> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due
>> to the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>>
>>
>