Database Unit Testing: fairly

Showing posts with label fairly. Show all posts

Monday, March 19, 2012

Database schema design

Hi All,
First of all, please excuse me for the long post. The post is long but
hopefully the problem described is fairly simple for somewhat experienced
data modellers.
I am a beginner in database design so I was hoping that maybe you can give
me some advice on how to design a schema for the following situation:
I have 8 sensors; each sensor has a bunch of parameters and I would like to
store all this information in a SQL database.
The information is organized the following way:
Each sensor is identified by an "Index" and its data parameters by
"SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say that all
sensors have two pieces of data associated with them "Speed" and
"Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000 and
"Threshold" is 10000. These two pieces of data, as mentioned before, are
described by "SubIndexes", so in this case, SubIndex 1 would have a value of
5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could be
described the following way:
Index 1001
SubIndex1 5000
SubIndex2 10000
As I mentioned before, I will have 8 sensors, so the whole information would
be (w/ some example values) like this:
Index: 1001
SubIndex1: 5000
SubIndex2: 10000
Index: 1002
SubIndex1: 3000
SubIndex2: 17000
...
Index: 1008
SubIndex1: 2500
SubIndex2: 20000
The challening part is that I also have to store several "configurations" of
the system where each configuration could have the sensors hold different
data. For example:
Config 1:
Index: 1001
SubIndex1: 2000
SubIndex2: 3000
...
Config 2:
Index: 1001
SubIndex1: 1000
SubIndex2: 7000
Please note that the number of available configurations is not known at
design time.
What is the best way to model this? What I came up with doesn't seem the
most efficient. This is something that I thought it would work:
Create a table that has the following columns: Index, SubIndex, Data,
ConfigNo.
For example:
Index SubIndex Data ConfigNo.
1001 1 2000 1
1001 2 3000 1
...
1001 1 1000 2
1001 2 7000 2
..
It seems as though I am repeating all the "indexes" and "subindexes" when
these stay "constant" and only the data and configuration number changes. Is
there any way to specify all the indexes and subindexes (all sensors) one
time and to somehow make them "point" to various data given a certain
configuration number? Or what would be a better method of doing it? I know
all about primary/foreign key relationships but I still couldn't find a way
to efficiently store this data.
Thank you for your time!I have changed the schema a little bit, and now I have it the following way:
I have two tables:
Table 1:
SensorID Index
1 1001
2 1002
...
8 1008
Table 2:
SensorID SubIndex Value ConfigNo.
1 1 7000 1
1 2 10000 1
2 1 1000 1
2 2 5000 1
...
1 1 2000 2
...
This way, at least I tried to "normalize" the data so that if the "Index" of
a sensor changes, I have to only change it in one place (in Table 1.) I am
still not sure if I can make further improvements to this schema.
Thanks again!
"vvf" <novvfspam@.hotmail.com> wrote in message
news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hi All,
> First of all, please excuse me for the long post. The post is long but
> hopefully the problem described is fairly simple for somewhat experienced
> data modellers.
> I am a beginner in database design so I was hoping that maybe you can give
> me some advice on how to design a schema for the following situation:
> I have 8 sensors; each sensor has a bunch of parameters and I would like
to
> store all this information in a SQL database.
> The information is organized the following way:
> Each sensor is identified by an "Index" and its data parameters by
> "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say that
all
> sensors have two pieces of data associated with them "Speed" and
> "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000 and
> "Threshold" is 10000. These two pieces of data, as mentioned before, are
> described by "SubIndexes", so in this case, SubIndex 1 would have a value
of
> 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could be
> described the following way:
> Index 1001
> SubIndex1 5000
> SubIndex2 10000
> As I mentioned before, I will have 8 sensors, so the whole information
would
> be (w/ some example values) like this:
> Index: 1001
> SubIndex1: 5000
> SubIndex2: 10000
> Index: 1002
> SubIndex1: 3000
> SubIndex2: 17000
> ...
> Index: 1008
> SubIndex1: 2500
> SubIndex2: 20000
> The challening part is that I also have to store several "configurations"
of
> the system where each configuration could have the sensors hold different
> data. For example:
> Config 1:
> Index: 1001
> SubIndex1: 2000
> SubIndex2: 3000
> ...
> Config 2:
> Index: 1001
> SubIndex1: 1000
> SubIndex2: 7000
> Please note that the number of available configurations is not known at
> design time.
> What is the best way to model this? What I came up with doesn't seem the
> most efficient. This is something that I thought it would work:
> Create a table that has the following columns: Index, SubIndex, Data,
> ConfigNo.
> For example:
> Index SubIndex Data ConfigNo.
> 1001 1 2000 1
> 1001 2 3000 1
> ...
> 1001 1 1000 2
> 1001 2 7000 2
> ..
> It seems as though I am repeating all the "indexes" and "subindexes" when
> these stay "constant" and only the data and configuration number changes.
Is
> there any way to specify all the indexes and subindexes (all sensors) one
> time and to somehow make them "point" to various data given a certain
> configuration number? Or what would be a better method of doing it? I know
> all about primary/foreign key relationships but I still couldn't find a
way
> to efficiently store this data.
> Thank you for your time!
>|||I'd think you'd want something more like:
Table Configs
Config SensorId Parm1 Parm2
1 1001 123 456
1 1002 234 567
...
2 1001 987 654
2 1002 876 543
...
Table Samples
SampleId SensorId Value
1 1001 1.3
1 1002 -7.2
...
Table SampleIdConfig
SampleId Config
1 7
2 3939
...
Or you could at small cost eliminate the last table and just add a
config column to the Samples table, it requires denormalizing the
column, but it's not like it ever changes!
J.
On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>I have changed the schema a little bit, and now I have it the following way:
>I have two tables:
>Table 1:
>SensorID Index
> 1 1001
> 2 1002
> ...
> 8 1008
>Table 2:
>SensorID SubIndex Value ConfigNo.
> 1 1 7000 1
> 1 2 10000 1
> 2 1 1000 1
> 2 2 5000 1
> ...
> 1 1 2000 2
> ...
>This way, at least I tried to "normalize" the data so that if the "Index" of
>a sensor changes, I have to only change it in one place (in Table 1.) I am
>still not sure if I can make further improvements to this schema.
>Thanks again!
>"vvf" <novvfspam@.hotmail.com> wrote in message
>news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> Hi All,
>> First of all, please excuse me for the long post. The post is long but
>> hopefully the problem described is fairly simple for somewhat experienced
>> data modellers.
>> I am a beginner in database design so I was hoping that maybe you can give
>> me some advice on how to design a schema for the following situation:
>> I have 8 sensors; each sensor has a bunch of parameters and I would like
>to
>> store all this information in a SQL database.
>> The information is organized the following way:
>> Each sensor is identified by an "Index" and its data parameters by
>> "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say that
>all
>> sensors have two pieces of data associated with them "Speed" and
>> "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000 and
>> "Threshold" is 10000. These two pieces of data, as mentioned before, are
>> described by "SubIndexes", so in this case, SubIndex 1 would have a value
>of
>> 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could be
>> described the following way:
>> Index 1001
>> SubIndex1 5000
>> SubIndex2 10000
>> As I mentioned before, I will have 8 sensors, so the whole information
>would
>> be (w/ some example values) like this:
>> Index: 1001
>> SubIndex1: 5000
>> SubIndex2: 10000
>> Index: 1002
>> SubIndex1: 3000
>> SubIndex2: 17000
>> ...
>> Index: 1008
>> SubIndex1: 2500
>> SubIndex2: 20000
>> The challening part is that I also have to store several "configurations"
>of
>> the system where each configuration could have the sensors hold different
>> data. For example:
>> Config 1:
>> Index: 1001
>> SubIndex1: 2000
>> SubIndex2: 3000
>> ...
>> Config 2:
>> Index: 1001
>> SubIndex1: 1000
>> SubIndex2: 7000
>> Please note that the number of available configurations is not known at
>> design time.
>> What is the best way to model this? What I came up with doesn't seem the
>> most efficient. This is something that I thought it would work:
>> Create a table that has the following columns: Index, SubIndex, Data,
>> ConfigNo.
>> For example:
>> Index SubIndex Data ConfigNo.
>> 1001 1 2000 1
>> 1001 2 3000 1
>> ...
>> 1001 1 1000 2
>> 1001 2 7000 2
>> ..
>> It seems as though I am repeating all the "indexes" and "subindexes" when
>> these stay "constant" and only the data and configuration number changes.
>Is
>> there any way to specify all the indexes and subindexes (all sensors) one
>> time and to somehow make them "point" to various data given a certain
>> configuration number? Or what would be a better method of doing it? I know
>> all about primary/foreign key relationships but I still couldn't find a
>way
>> to efficiently store this data.
>> Thank you for your time!
>>
>|||On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>Create a table that has the following columns: Index, SubIndex, Data,
>ConfigNo.
>For example:
>Index SubIndex Data ConfigNo.
>1001 1 2000 1
>1001 2 3000 1
>...
>1001 1 1000 2
>1001 2 7000 2
Index is a bad choice of name, it is a reserved word in SQL. From the
given data I would be inclined to call it Sensor. Likewise SubIndex
sounds like it would better be named Measure, and for Data I might be
inclined toward Value. These are small things, but they are worth
getting right as early in the process as possible.
That structure looks fine to me, as far as I can say from the given
data. It has a three part key (Index, SubIndex, ConfigNo), and one
column of data.
Does SubIndex have the same meaning across all values of Index? If
SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
other Index that has a SubIndex of 1? In general it would be a VERY
good idea to maintain that sort of consistency if measures are common
across multiple indexes.
Does ConfigNo have any meaning across multiple values of Index? Is
someone going to say "now for ConfigNo = 7, show me each Index and the
associated Data"?
Any time I have seen sensor data in a database there has been a time
dimension somewhere. As I read your description I kept wondering when
it would show up.
Roy Harvey
Beacon Falls, CT|||Thanks JXStern.
I should have mentioned that the sensors could have as many as 255
subindexes (or params as you call them.)
The problem is that a table would have to have quite a few columns to go
with the schema that you proposed. I wasn't sure if this would pose a
problem in terms of efficiency or if it is even possible to have more than
255 columns in SQL Mobile (I should check books online for that.)
Thanks again,
vvf.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:snp0q2p4akk3k2mc7jj2ja2sc6tjjkuvkc@.4ax.com...
> I'd think you'd want something more like:
> Table Configs
> Config SensorId Parm1 Parm2
> 1 1001 123 456
> 1 1002 234 567
> ...
> 2 1001 987 654
> 2 1002 876 543
> ...
> Table Samples
> SampleId SensorId Value
> 1 1001 1.3
> 1 1002 -7.2
> ...
> Table SampleIdConfig
> SampleId Config
> 1 7
> 2 3939
> ...
>
> Or you could at small cost eliminate the last table and just add a
> config column to the Samples table, it requires denormalizing the
> column, but it's not like it ever changes!
> J.
>
> On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
> wrote:
> >I have changed the schema a little bit, and now I have it the following
way:
> >
> >I have two tables:
> >
> >Table 1:
> >
> >SensorID Index
> > 1 1001
> > 2 1002
> > ...
> > 8 1008
> >
> >Table 2:
> >
> >SensorID SubIndex Value ConfigNo.
> > 1 1 7000 1
> > 1 2 10000 1
> > 2 1 1000 1
> > 2 2 5000 1
> > ...
> > 1 1 2000 2
> > ...
> >
> >This way, at least I tried to "normalize" the data so that if the "Index"
of
> >a sensor changes, I have to only change it in one place (in Table 1.) I
am
> >still not sure if I can make further improvements to this schema.
> >
> >Thanks again!
> >
> >"vvf" <novvfspam@.hotmail.com> wrote in message
> >news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
> >> Hi All,
> >>
> >> First of all, please excuse me for the long post. The post is long but
> >> hopefully the problem described is fairly simple for somewhat
experienced
> >> data modellers.
> >>
> >> I am a beginner in database design so I was hoping that maybe you can
give
> >> me some advice on how to design a schema for the following situation:
> >>
> >> I have 8 sensors; each sensor has a bunch of parameters and I would
like
> >to
> >> store all this information in a SQL database.
> >>
> >> The information is organized the following way:
> >>
> >> Each sensor is identified by an "Index" and its data parameters by
> >> "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say
that
> >all
> >> sensors have two pieces of data associated with them "Speed" and
> >> "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000
and
> >> "Threshold" is 10000. These two pieces of data, as mentioned before,
are
> >> described by "SubIndexes", so in this case, SubIndex 1 would have a
value
> >of
> >> 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could
be
> >> described the following way:
> >>
> >> Index 1001
> >> SubIndex1 5000
> >> SubIndex2 10000
> >>
> >> As I mentioned before, I will have 8 sensors, so the whole information
> >would
> >> be (w/ some example values) like this:
> >>
> >> Index: 1001
> >> SubIndex1: 5000
> >> SubIndex2: 10000
> >>
> >> Index: 1002
> >> SubIndex1: 3000
> >> SubIndex2: 17000
> >>
> >> ...
> >>
> >> Index: 1008
> >> SubIndex1: 2500
> >> SubIndex2: 20000
> >>
> >> The challening part is that I also have to store several
"configurations"
> >of
> >> the system where each configuration could have the sensors hold
different
> >> data. For example:
> >>
> >> Config 1:
> >>
> >> Index: 1001
> >> SubIndex1: 2000
> >> SubIndex2: 3000
> >>
> >> ...
> >>
> >> Config 2:
> >> Index: 1001
> >> SubIndex1: 1000
> >> SubIndex2: 7000
> >>
> >> Please note that the number of available configurations is not known at
> >> design time.
> >>
> >> What is the best way to model this? What I came up with doesn't seem
the
> >> most efficient. This is something that I thought it would work:
> >>
> >> Create a table that has the following columns: Index, SubIndex, Data,
> >> ConfigNo.
> >>
> >> For example:
> >>
> >> Index SubIndex Data ConfigNo.
> >> 1001 1 2000 1
> >> 1001 2 3000 1
> >> ...
> >> 1001 1 1000 2
> >> 1001 2 7000 2
> >> ..
> >>
> >> It seems as though I am repeating all the "indexes" and "subindexes"
when
> >> these stay "constant" and only the data and configuration number
changes.
> >Is
> >> there any way to specify all the indexes and subindexes (all sensors)
one
> >> time and to somehow make them "point" to various data given a certain
> >> configuration number? Or what would be a better method of doing it? I
know
> >> all about primary/foreign key relationships but I still couldn't find a
> >way
> >> to efficiently store this data.
> >>
> >> Thank you for your time!
> >>
> >>
> >
>|||Hi Roy,
Thank you for your detailed answer. Please see comments embedded below:
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:rvp1q2lju44nfsfoc8e1kiu222cgcjkil6@.4ax.com...
> On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
> wrote:
> >Create a table that has the following columns: Index, SubIndex, Data,
> >ConfigNo.
> >
> >For example:
> >
> >Index SubIndex Data ConfigNo.
> >1001 1 2000 1
> >1001 2 3000 1
> >...
> >1001 1 1000 2
> >1001 2 7000 2
> Index is a bad choice of name, it is a reserved word in SQL.
Correct. I had to resort to OLEDB to rename the column as I would get an
error trying to run SQL commands against this type of schema.
> From the
> given data I would be inclined to call it Sensor. Likewise SubIndex
> sounds like it would better be named Measure, and for Data I might be
> inclined toward Value. These are small things, but they are worth
> getting right as early in the process as possible.
Right; In my real schema, I have "Value" instead of "Data" and "SensorIndex"
instead of "Index" now. I "imported" the terminology (i.e., "Index",
"SubIndex") from the communications protocol "CANOPEN" as this is what we
use in our systems and had to have some sort of "consistency" throughout our
design.
> That structure looks fine to me, as far as I can say from the given
> data. It has a three part key (Index, SubIndex, ConfigNo), and one
> column of data.
> Does SubIndex have the same meaning across all values of Index? If
> SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
> other Index that has a SubIndex of 1?
Yes. All the SubIndexes have the exact same meaning across sensors. For
example, "SubIndex1" is "Speed" for all 8 sensors.
> In general it would be a VERY
> good idea to maintain that sort of consistency if measures are common
> across multiple indexes.
> Does ConfigNo have any meaning across multiple values of Index? Is
> someone going to say "now for ConfigNo = 7, show me each Index and the
> associated Data"?
Yes, they could say that. Basically, it is saving various configurations of
the sensors. For example, I could set up my sensors so that Sensor 1 has
SubIndex1(Speed) set to 10 and SubIndex2(Threshold) set to 20 and Sensor 2
to have SubIndex1(Speed) set to 30 and SubIndex2(Threshold) set to 40. Then,
I would save this "scenario" as ConfigNo "1". After that, I could change the
settings for these two sensors to something else and then save that
"scenario" to ConfigNo "2". Later, based on what ConfigNo I load, my sensors
would be filled with the corresponding values saved previously. Of course,
in this example I only showed 2 sensors while in the real scenario I would
have 8 sensors set to certain data.
> Any time I have seen sensor data in a database there has been a time
> dimension somewhere. As I read your description I kept wondering when
> it would show up.
The sensor data shown here is more like "configuration" data; In other
words, it is "static" data in a way because it only defines how the sensors
should behave when acquiring their data. That particular data (acquired
data) is saved in a different table and indeed has a time associated with
it. The data that we talked about here only configures the sensors so they
act in a certain way when acquiring data, and, as I mentioned earlier, I
have to have various pre-defined configurations for them.
Thanks again for your answer and analysis!
vvf.|||Your responses confirm to me that the design still looks good. The
only other comment I have is that you should be sure to have "master"
tables for Index, SubIndex and Configuration, even if the data is no
more than a key and description. But I expect you already have done
that.
Roy Harvey
Beacon Falls, CT
On Sun, 7 Jan 2007 18:24:08 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>Hi Roy,
>Thank you for your detailed answer. Please see comments embedded below:
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:rvp1q2lju44nfsfoc8e1kiu222cgcjkil6@.4ax.com...
>> On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
>> wrote:
>> >Create a table that has the following columns: Index, SubIndex, Data,
>> >ConfigNo.
>> >
>> >For example:
>> >
>> >Index SubIndex Data ConfigNo.
>> >1001 1 2000 1
>> >1001 2 3000 1
>> >...
>> >1001 1 1000 2
>> >1001 2 7000 2
>> Index is a bad choice of name, it is a reserved word in SQL.
>Correct. I had to resort to OLEDB to rename the column as I would get an
>error trying to run SQL commands against this type of schema.
>> From the
>> given data I would be inclined to call it Sensor. Likewise SubIndex
>> sounds like it would better be named Measure, and for Data I might be
>> inclined toward Value. These are small things, but they are worth
>> getting right as early in the process as possible.
>Right; In my real schema, I have "Value" instead of "Data" and "SensorIndex"
>instead of "Index" now. I "imported" the terminology (i.e., "Index",
>"SubIndex") from the communications protocol "CANOPEN" as this is what we
>use in our systems and had to have some sort of "consistency" throughout our
>design.
>> That structure looks fine to me, as far as I can say from the given
>> data. It has a three part key (Index, SubIndex, ConfigNo), and one
>> column of data.
>> Does SubIndex have the same meaning across all values of Index? If
>> SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
>> other Index that has a SubIndex of 1?
>Yes. All the SubIndexes have the exact same meaning across sensors. For
>example, "SubIndex1" is "Speed" for all 8 sensors.
>> In general it would be a VERY
>> good idea to maintain that sort of consistency if measures are common
>> across multiple indexes.
>
>> Does ConfigNo have any meaning across multiple values of Index? Is
>> someone going to say "now for ConfigNo = 7, show me each Index and the
>> associated Data"?
>Yes, they could say that. Basically, it is saving various configurations of
>the sensors. For example, I could set up my sensors so that Sensor 1 has
>SubIndex1(Speed) set to 10 and SubIndex2(Threshold) set to 20 and Sensor 2
>to have SubIndex1(Speed) set to 30 and SubIndex2(Threshold) set to 40. Then,
>I would save this "scenario" as ConfigNo "1". After that, I could change the
>settings for these two sensors to something else and then save that
>"scenario" to ConfigNo "2". Later, based on what ConfigNo I load, my sensors
>would be filled with the corresponding values saved previously. Of course,
>in this example I only showed 2 sensors while in the real scenario I would
>have 8 sensors set to certain data.
>> Any time I have seen sensor data in a database there has been a time
>> dimension somewhere. As I read your description I kept wondering when
>> it would show up.
>The sensor data shown here is more like "configuration" data; In other
>words, it is "static" data in a way because it only defines how the sensors
>should behave when acquiring their data. That particular data (acquired
>data) is saved in a different table and indeed has a time associated with
>it. The data that we talked about here only configures the sensors so they
>act in a certain way when acquiring data, and, as I mentioned earlier, I
>have to have various pre-defined configurations for them.
>Thanks again for your answer and analysis!
>vvf.
>|||On Sun, 7 Jan 2007 18:06:51 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>Thanks JXStern.
>I should have mentioned that the sensors could have as many as 255
>subindexes (or params as you call them.)
Hmm. Well, for now I'd say just go for it, 255 columns. You could
further normalize by breaking them into a table keyed by config,
sensorid, and parm#, but that's probably overkill. Depending on what
you do with the data downstream, it's probably more efficient just to
have the 255 columns. Such natural sets of columns are formally a
"repeating group" which is forbidden in even 1NF, ... BUT frequently
kept together anyway, AS LONG AS THEY CONSTITUTE A SET, that is,
PARM#1 is not really the same thing as PARM#2.
>The problem is that a table would have to have quite a few columns to go
>with the schema that you proposed. I wasn't sure if this would pose a
>problem in terms of efficiency or if it is even possible to have more than
>255 columns in SQL Mobile (I should check books online for that.)
>Thanks again,
>vvf.
>
>"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>news:snp0q2p4akk3k2mc7jj2ja2sc6tjjkuvkc@.4ax.com...
>> I'd think you'd want something more like:
>> Table Configs
>> Config SensorId Parm1 Parm2
>> 1 1001 123 456
>> 1 1002 234 567
>> ...
>> 2 1001 987 654
>> 2 1002 876 543
>> ...
>> Table Samples
>> SampleId SensorId Value
>> 1 1001 1.3
>> 1 1002 -7.2
>> ...
>> Table SampleIdConfig
>> SampleId Config
>> 1 7
>> 2 3939
>> ...
>>
>> Or you could at small cost eliminate the last table and just add a
>> config column to the Samples table, it requires denormalizing the
>> column, but it's not like it ever changes!
>> J.
>>
>> On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
>> wrote:
>> >I have changed the schema a little bit, and now I have it the following
>way:
>> >
>> >I have two tables:
>> >
>> >Table 1:
>> >
>> >SensorID Index
>> > 1 1001
>> > 2 1002
>> > ...
>> > 8 1008
>> >
>> >Table 2:
>> >
>> >SensorID SubIndex Value ConfigNo.
>> > 1 1 7000 1
>> > 1 2 10000 1
>> > 2 1 1000 1
>> > 2 2 5000 1
>> > ...
>> > 1 1 2000 2
>> > ...
>> >
>> >This way, at least I tried to "normalize" the data so that if the "Index"
>of
>> >a sensor changes, I have to only change it in one place (in Table 1.) I
>am
>> >still not sure if I can make further improvements to this schema.
>> >
>> >Thanks again!
>> >
>> >"vvf" <novvfspam@.hotmail.com> wrote in message
>> >news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> >> Hi All,
>> >>
>> >> First of all, please excuse me for the long post. The post is long but
>> >> hopefully the problem described is fairly simple for somewhat
>experienced
>> >> data modellers.
>> >>
>> >> I am a beginner in database design so I was hoping that maybe you can
>give
>> >> me some advice on how to design a schema for the following situation:
>> >>
>> >> I have 8 sensors; each sensor has a bunch of parameters and I would
>like
>> >to
>> >> store all this information in a SQL database.
>> >>
>> >> The information is organized the following way:
>> >>
>> >> Each sensor is identified by an "Index" and its data parameters by
>> >> "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say
>that
>> >all
>> >> sensors have two pieces of data associated with them "Speed" and
>> >> "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000
>and
>> >> "Threshold" is 10000. These two pieces of data, as mentioned before,
>are
>> >> described by "SubIndexes", so in this case, SubIndex 1 would have a
>value
>> >of
>> >> 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could
>be
>> >> described the following way:
>> >>
>> >> Index 1001
>> >> SubIndex1 5000
>> >> SubIndex2 10000
>> >>
>> >> As I mentioned before, I will have 8 sensors, so the whole information
>> >would
>> >> be (w/ some example values) like this:
>> >>
>> >> Index: 1001
>> >> SubIndex1: 5000
>> >> SubIndex2: 10000
>> >>
>> >> Index: 1002
>> >> SubIndex1: 3000
>> >> SubIndex2: 17000
>> >>
>> >> ...
>> >>
>> >> Index: 1008
>> >> SubIndex1: 2500
>> >> SubIndex2: 20000
>> >>
>> >> The challening part is that I also have to store several
>"configurations"
>> >of
>> >> the system where each configuration could have the sensors hold
>different
>> >> data. For example:
>> >>
>> >> Config 1:
>> >>
>> >> Index: 1001
>> >> SubIndex1: 2000
>> >> SubIndex2: 3000
>> >>
>> >> ...
>> >>
>> >> Config 2:
>> >> Index: 1001
>> >> SubIndex1: 1000
>> >> SubIndex2: 7000
>> >>
>> >> Please note that the number of available configurations is not known at
>> >> design time.
>> >>
>> >> What is the best way to model this? What I came up with doesn't seem
>the
>> >> most efficient. This is something that I thought it would work:
>> >>
>> >> Create a table that has the following columns: Index, SubIndex, Data,
>> >> ConfigNo.
>> >>
>> >> For example:
>> >>
>> >> Index SubIndex Data ConfigNo.
>> >> 1001 1 2000 1
>> >> 1001 2 3000 1
>> >> ...
>> >> 1001 1 1000 2
>> >> 1001 2 7000 2
>> >> ..
>> >>
>> >> It seems as though I am repeating all the "indexes" and "subindexes"
>when
>> >> these stay "constant" and only the data and configuration number
>changes.
>> >Is
>> >> there any way to specify all the indexes and subindexes (all sensors)
>one
>> >> time and to somehow make them "point" to various data given a certain
>> >> configuration number? Or what would be a better method of doing it? I
>know
>> >> all about primary/foreign key relationships but I still couldn't find a
>> >way
>> >> to efficiently store this data.
>> >>
>> >> Thank you for your time!
>> >>
>> >>
>> >
>

Friday, February 17, 2012

Database performance degrading (again)

We have an application with a SQL Server 2000 back end that is fairly
database intensive -- lots of fairly frequent queries, inserts, updates
-- the gamut. The application does not make use of performance hogs
like cursors, but I know there are lots of ways the application could
be made more efficient database-wise. The server code is running VB6
of all things, using COM+ database interfaces. There are some
clustered and non-clustered indexes defined, but I'm pretty sure
there's room for improvement there as well.

Some tables have grown into the millions of records in recent months,
and performance of the application slowed to a crawl. Optimizing the
database helped a little, but not much. We know that several million
records in a table is a lot, but one would think that SQL Server should
be able to still handle that pretty well. We do have plans to archive
a lot of old data, but in the meantime, we were hurting for a quick
fix.

So we threw hardware at the problem, and transferred the database to a
new, more powerful server. The performance improvement was dramatic.
Transactions were many many times faster than before. Without
implementing any of the other more difficult performance improvements
we have planned, we suddenly became minor heros. :-)

Well, the honeymoon seems to be somewhat over. While performance is
still much better than when the database resided on our old server,
performance appears to have degraded rather significantly again.
Performance is also not significantly better with fewer users on our
system. What the heck?

Yes, the database continues to grow unchecked as we haven't quite got
an archive utility in place yet, but the growth is relatively gradual,
so you wouldn't think that would be the issue. The database is
optimized on a weekly basis, and our web and database servers are both
rebooted monthly. Our database administrators don't seem to have
answers, so I appeal to the experts reading this forum to maybe offer
some clues.

Prior to posting I did do a fair amount of research to see what people
have suggested in similar situations, and ran this by our database
admin. Here's what I can tell you from this research:

- Statistics are updated weekly along with whatever else the database
optimization does

- We do not use the "autoshrink" option for automatically shrinking log
files

- Regarding preallocating space and setting growth factors for log and
data files to minimize time spent allocating disk space, our admin
says, "We do allow database files to grow unchecked, but we do monitor
growth and manually expand as needed. Autogrow is typically set in
50MB increments or less as the amount of time it takes to expand this
amount is negligible."

- Transaction logging is turned on, and data and log devices are on
separate physical disks

- The database server is monitored to ensure no process is hogging all
of the CPU, I/O or memory> We know that several million records in a table is a lot
Not really. It all depends on your hardware and how your application
has been written. I've used tables that are in the tens of millions of
records (even close to 100 million) without encountering performance
problems.

Have you run Profiler to see where the slowness is originating? A good
idea would be to run profiler, logging to a table, for a day. Use that
data to find the longest running SQL statements as well as the most
commonly used SQL statements. Concentrate your efforts on improving
those queries that appear in both categories - i.e. SQL statements that
take a long time to run and which are frequently used. If you check out
Microsoft's website I'm sure that you can find a whitepaper on using
Profiler to optimize a database application.

Another thing to look at is how the front end application is accessing
the server. Is it making a lot of unnecessary round trips to the SQL
Server? Network latency can also play into this a lot. If your frontend
application is scrolling through records in your table one at a time,
retrieving each one individually, it doesn't matter how fast your
database is, the application will be slow.

This is one problem with throwing hardware at a database issue. The
problem that is causing the slowness will almost always come up again
until you fix the part of the database/application that is causing the
issue in the first place.

Hopefully these ideas will yield some improvements for you.

Good luck,
-Tom.|||(teedilo@.hotmail.com) writes:
> Some tables have grown into the millions of records in recent months,
> and performance of the application slowed to a crawl. Optimizing the
> database helped a little, but not much. We know that several million
> records in a table is a lot, but one would think that SQL Server should
> be able to still handle that pretty well.

Well, let me put it this way: a couple of million row tables is
enough to make SQL Server slower than a snail if you have poor indexing
or malformed queries. But as Thomas said: you can have over 100 million
rows, and excellent performance. All depends on the matching between
queries and indexes.

> So we threw hardware at the problem, and transferred the database to a
> new, more powerful server. The performance improvement was dramatic.
> Transactions were many many times faster than before.

Since SQL Server uses a cost-based optimizer, the same query with the
same indexes can give different qurey plans, with a little difference
in statistics. If there was such a drastic changes, it seems that the
tables were turned in favour of better plans.

> Well, the honeymoon seems to be somewhat over. While performance is
> still much better than when the database resided on our old server,
> performance appears to have degraded rather significantly again.
> Performance is also not significantly better with fewer users on our
> system. What the heck?

And you ask us who don't even see the system!

As Thomas said, use the Profiler to track down slow-running queries,
and address them one by one; either by rewriting or modifying indexes.
Or take a shortcut: save a day's workload, and feed it to the Index
Tunig Wizard.

> - Statistics are updated weekly along with whatever else the database
> optimization does

Nevertheless, if can be good idea to do DBCC SHOWCONTIG on some
big table, to check for fragmentation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your replies, Tom and Erland.

Yes, I know that several million records in a table is not necessarily
a lot, especially if the application, database, indexes, etc. are all
well designed. I'll concede that that's probably not the case here!

And yes, I'm sure that network latency contributes to our problems, as
there are a fair amount of round trips going on that probably wouldn't
be necessary if the application were better designed. The consistency
of the bad performance seems to suggest that the degradation in
performance has more to do with the database, however.

I like the Profiler suggestion, which we've been talking about doing
anyway. It's too bad that we didn't do that right after moving to this
new server so we could maybe pinpoint what has changed to make it run
slower.

I also like the suggestion of running DBCC SHOWCONTIG on the bigger
tables to see if there's significant fragmentation even though we've
been optimizing every week.

I also appreciate Erland's comment that it's difficult for someone on
the outside to know what's really going on. I just figured that there
*might* be fairly common/general things to look for/do in the situation
where 1) performance was awful on an old server, 2) performance was
vastly improved when the database was moved to a new server, and 3)
performance quickly degraded again on the new server.

Yes, I know that throwing hardware at the problem is not a silver
bullet. Our old server needed replacing anyway, so we figured it was
worth doing, at least as a stopgap measure to improve performance,
while we were still working on the more difficult fixes. And while I
am not surprised that some slowness would arise again as Tom suggests,
I am rather surprised that the degradation was as significant as it was
in a relatively few short months since the new server was put into
place. In fact, my theory is that reformatting the new server and
starting completely from scratch would suddenly give us improved
performance again -- just a hunch.

One thing that confuses me a little in relation to Erland's comments
about SQL Server's cost-based optimizer. I've read a little about
this, how SQL Server continually tries to find better execution plans,
etc. But wouldn't that lead to *improved* performance instead of worse
performance? Or is it possible that SQL Server will occasionally make
the "wrong choice" in terms of finding execution plans that offer
better performance?

Thanks much again for your suggestions. They're probably about as good
as I can expect given the lack of specific information that I can offer
on this problem.|||> One thing that confuses me a little in relation to Erland's comments
> about SQL Server's cost-based optimizer. I've read a little about
> this, how SQL Server continually tries to find better execution plans,
> etc. But wouldn't that lead to *improved* performance instead of worse
> performance?

Indexes are the key to an efficient execution plan. Statistics on
non-indexed columns can help too (but to a lesser extent) and SQL Server can
generate column statistics automatically. However, indexes are entirely up
to you because you need to balance the cost of maintaining indexes with data
retrieval performance benefits. This is where Profiler and execution plan
analysis can help you out.

When you start with a well-designed database and application, the SQL Server
cost-based optimizer usually does a pretty good job of adapting to changes
in data volume and cardinality. When you don't have appropriate indexes,
the optimizer can't do much to help you out and performance degrades with
data volume. This appears to be the case in your environment based on your
description of the problem.

> Or is it possible that SQL Server will occasionally make
> the "wrong choice" in terms of finding execution plans that offer
> better performance?

Yes, it possible that the optimizer makes the wrong choice. When you find
an execution plan that isn't using indexes effectively, first ensure stats
are up-to-date and expressions are sargable. You can specify hints to
override the cost-based optimizer choices but this should be done as a only
as a last resort.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<teedilo@.hotmail.com> wrote in message
news:1109981213.142149.22520@.o13g2000cwo.googlegro ups.com...
> Thanks for your replies, Tom and Erland.
> Yes, I know that several million records in a table is not necessarily
> a lot, especially if the application, database, indexes, etc. are all
> well designed. I'll concede that that's probably not the case here!
> And yes, I'm sure that network latency contributes to our problems, as
> there are a fair amount of round trips going on that probably wouldn't
> be necessary if the application were better designed. The consistency
> of the bad performance seems to suggest that the degradation in
> performance has more to do with the database, however.
> I like the Profiler suggestion, which we've been talking about doing
> anyway. It's too bad that we didn't do that right after moving to this
> new server so we could maybe pinpoint what has changed to make it run
> slower.
> I also like the suggestion of running DBCC SHOWCONTIG on the bigger
> tables to see if there's significant fragmentation even though we've
> been optimizing every week.
> I also appreciate Erland's comment that it's difficult for someone on
> the outside to know what's really going on. I just figured that there
> *might* be fairly common/general things to look for/do in the situation
> where 1) performance was awful on an old server, 2) performance was
> vastly improved when the database was moved to a new server, and 3)
> performance quickly degraded again on the new server.
> Yes, I know that throwing hardware at the problem is not a silver
> bullet. Our old server needed replacing anyway, so we figured it was
> worth doing, at least as a stopgap measure to improve performance,
> while we were still working on the more difficult fixes. And while I
> am not surprised that some slowness would arise again as Tom suggests,
> I am rather surprised that the degradation was as significant as it was
> in a relatively few short months since the new server was put into
> place. In fact, my theory is that reformatting the new server and
> starting completely from scratch would suddenly give us improved
> performance again -- just a hunch.
> One thing that confuses me a little in relation to Erland's comments
> about SQL Server's cost-based optimizer. I've read a little about
> this, how SQL Server continually tries to find better execution plans,
> etc. But wouldn't that lead to *improved* performance instead of worse
> performance? Or is it possible that SQL Server will occasionally make
> the "wrong choice" in terms of finding execution plans that offer
> better performance?
> Thanks much again for your suggestions. They're probably about as good
> as I can expect given the lack of specific information that I can offer
> on this problem.|||(teedilo@.hotmail.com) writes:
> I also appreciate Erland's comment that it's difficult for someone on
> the outside to know what's really going on. I just figured that there
> *might* be fairly common/general things to look for/do in the situation
> where 1) performance was awful on an old server, 2) performance was
> vastly improved when the database was moved to a new server, and 3)
> performance quickly degraded again on the new server.

So how did you migrate the database? If you now say "Copy Database
Wizard" or any other methods that include rebuild of the database,
I'm tempted to place my bets on fragmentation as the main issue. To
wit, if you move the database in this way, you get quite a well-
defragmented database in the other end.

If you migrated the database my means of backup/restore or detach/attach,
your also copied the fragmentation. In this case... I could go into
speculation, but it would not really address the actual problem.

> One thing that confuses me a little in relation to Erland's comments
> about SQL Server's cost-based optimizer. I've read a little about
> this, how SQL Server continually tries to find better execution plans,
> etc. But wouldn't that lead to *improved* performance instead of worse
> performance? Or is it possible that SQL Server will occasionally make
> the "wrong choice" in terms of finding execution plans that offer
> better performance?

To say that SQL Server constantly try to find better query plans, is
not maybe really correct. (That's more applicable to the SQL Server
dev team.) But SQL Server do occasionally review query plans, because
statistics have changed - or because the query plan has fallen out of
cache.

And plans are bulit on estimates from statistics, and they are indeed
a gamble. Sometimes the opimtizer makes very bad picks for one reason
or another. This also applies to different SQL Server versions. MS
does some tweak to the optimizer, which in many cases are beneficiary,
but at some sites can backfire.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Glancing through the posts it is not clear what maintenace you are doing
other than the weekly updating the statistics, so I will add my two pence
worth!!!

Your DBCC SHOWCONTIG will probably indicate fragmentation if you don't have
maintenace in place that calls DBCC INDEXDEFRAG and/or DBCC DBREINDEX. You
may also want to look at running the index tuning wizard on your profiles to
see if it suggests alternate fill factors. You may want to try alternate
fill factors on a different (controlled) system and replay the profiles
against it to see what fragmentation and speed you can achieve.

John

Tuesday, February 14, 2012

Database Optimizations

We have a database that is getting fairly heavy use. Our client complained
that on a certain task it was performing slower than usual. The last time
this happened I manually ran our optimazations job and it fixed the problem.
After that I set up our optimazations job to run every night. This kept
anything from happening for a while but just today we got the same call
again and again it was fixed when I ran the optimazations job. How often
are heavly used databases typically suppose to run the optimizations? Any
help on this topic?
Thanks,
BrandonBrandon Owensby wrote:
> We have a database that is getting fairly heavy use. Our client
> complained that on a certain task it was performing slower than
> usual. The last time this happened I manually ran our optimazations
> job and it fixed the problem. After that I set up our optimazations
> job to run every night. This kept anything from happening for a
> while but just today we got the same call again and again it was
> fixed when I ran the optimazations job. How often are heavly used
> databases typically suppose to run the optimizations? Any help on
> this topic?
> Thanks,
> Brandon
What optimizations? How can you be sure you don't have performance
problems related to query performance? What about disk? CPU? There are a
number of issues that can be the cause. Most times it's the SQL itself.
Properly tuned queries reduce disk and CPU overhead greatly.
--
David Gugick
Imceda Software
www.imceda.com|||Brandon
It is time to start tuning your queries/sp/views. In my experience 80% of
such kind of problems is caused by application (poor written queries).
"Brandon Owensby" <123@.abc.com> wrote in message
news:%23KDUctfvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> We have a database that is getting fairly heavy use. Our client
complained
> that on a certain task it was performing slower than usual. The last time
> this happened I manually ran our optimazations job and it fixed the
problem.
> After that I set up our optimazations job to run every night. This kept
> anything from happening for a while but just today we got the same call
> again and again it was fixed when I ran the optimazations job. How often
> are heavly used databases typically suppose to run the optimizations?
Any
> help on this topic?
> Thanks,
> Brandon
>

Database Optimizations

We have a database that is getting fairly heavy use. Our client complained
that on a certain task it was performing slower than usual. The last time
this happened I manually ran our optimazations job and it fixed the problem.
After that I set up our optimazations job to run every night. This kept
anything from happening for a while but just today we got the same call
again and again it was fixed when I ran the optimazations job. How often
are heavly used databases typically suppose to run the optimizations? Any
help on this topic?
Thanks,
Brandon
Brandon Owensby wrote:
> We have a database that is getting fairly heavy use. Our client
> complained that on a certain task it was performing slower than
> usual. The last time this happened I manually ran our optimazations
> job and it fixed the problem. After that I set up our optimazations
> job to run every night. This kept anything from happening for a
> while but just today we got the same call again and again it was
> fixed when I ran the optimazations job. How often are heavly used
> databases typically suppose to run the optimizations? Any help on
> this topic?
> Thanks,
> Brandon
What optimizations? How can you be sure you don't have performance
problems related to query performance? What about disk? CPU? There are a
number of issues that can be the cause. Most times it's the SQL itself.
Properly tuned queries reduce disk and CPU overhead greatly.
David Gugick
Imceda Software
www.imceda.com
|||Brandon
It is time to start tuning your queries/sp/views. In my experience 80% of
such kind of problems is caused by application (poor written queries).
"Brandon Owensby" <123@.abc.com> wrote in message
news:%23KDUctfvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> We have a database that is getting fairly heavy use. Our client
complained
> that on a certain task it was performing slower than usual. The last time
> this happened I manually ran our optimazations job and it fixed the
problem.
> After that I set up our optimazations job to run every night. This kept
> anything from happening for a while but just today we got the same call
> again and again it was fixed when I ran the optimazations job. How often
> are heavly used databases typically suppose to run the optimizations?
Any
> help on this topic?
> Thanks,
> Brandon
>

Database Optimizations

We have a database that is getting fairly heavy use. Our client complained
that on a certain task it was performing slower than usual. The last time
this happened I manually ran our optimazations job and it fixed the problem.
After that I set up our optimazations job to run every night. This kept
anything from happening for a while but just today we got the same call
again and again it was fixed when I ran the optimazations job. How often
are heavly used databases typically suppose to run the optimizations? Any
help on this topic?
Thanks,
BrandonBrandon Owensby wrote:
> We have a database that is getting fairly heavy use. Our client
> complained that on a certain task it was performing slower than
> usual. The last time this happened I manually ran our optimazations
> job and it fixed the problem. After that I set up our optimazations
> job to run every night. This kept anything from happening for a
> while but just today we got the same call again and again it was
> fixed when I ran the optimazations job. How often are heavly used
> databases typically suppose to run the optimizations? Any help on
> this topic?
> Thanks,
> Brandon
What optimizations? How can you be sure you don't have performance
problems related to query performance? What about disk? CPU? There are a
number of issues that can be the cause. Most times it's the SQL itself.
Properly tuned queries reduce disk and CPU overhead greatly.
David Gugick
Imceda Software
www.imceda.com|||Brandon
It is time to start tuning your queries/sp/views. In my experience 80% of
such kind of problems is caused by application (poor written queries).
"Brandon Owensby" <123@.abc.com> wrote in message
news:%23KDUctfvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> We have a database that is getting fairly heavy use. Our client
complained
> that on a certain task it was performing slower than usual. The last time
> this happened I manually ran our optimazations job and it fixed the
problem.
> After that I set up our optimazations job to run every night. This kept
> anything from happening for a while but just today we got the same call
> again and again it was fixed when I ran the optimazations job. How often
> are heavly used databases typically suppose to run the optimizations?
Any
> help on this topic?
> Thanks,
> Brandon
>

Monday, March 19, 2012

Database schema design

Database schema design

Database schema design

Friday, February 17, 2012

Database performance degrading (again)

Tuesday, February 14, 2012

Database Optimizations

Database Optimizations

Database Optimizations

Database Unit Testing

Blog Archive

About Me