On Aug 13, 2:59 pm, tom <toab...@googlemail.com> wrote:
> Hi,
>
> i want to save a lot of data (measurement values). The structure is,
> that i have dataloggers (which produce data), sensors (are connected
> to the datalogger with foreignkey), and projects. every logger is
> connected to one project (foreignkey). then, there is a app called
> Data.
>
> i have the following data model:
>
> class Entry(models.Model):
> """
> a data entry. Normaly, it is one row from a measurement file
> a new entry has one datetime, a project, and a logger_serial
> """
> project = models.ForeignKey(Project)
> logger = models.ForeignKey(Logger)
> original_file = models.ForeignKey(OriginalFile)
> datetime = models.DateTimeField(help_text='the date and time of
> the measurement entry', db_index=True)
>
> class Meta:
> unique_together = [('logger', 'datetime')]
> ordering = ['project', 'logger', 'datetime']
>
> class Measurand(models.Model):
> """
> A Model for measurement data
> """
> entry = models.ForeignKey(Entry)
> sensor = models.ForeignKey(Sensor)
>
> class Meta:
> unique_together = [('entry','sensor')]
> ordering = ['entry', 'status']
>
> class MeasurandAverage(models.Model):
> """
> Model to store the average value for a measurand
> """
> measurand = models.OneToOneField(Measurand, parent_link=True)
> value = models.FloatField(help_text='the average measurement
> value', db_index=True)
> unit = models.CharField(help_text='the unit of the measurement
> value', max_length=20, db_index=True)
>
> class MeasurandMax(models.Model):
> """
> Model to store the max value for a measurand
> """
> measurand = models.OneToOneField(Measurand, parent_link=True)
> value = models.FloatField(help_text='the max measurement value',
> db_index=True)
> unit = models.CharField(help_text='the unit of the measurement
> value', max_length=20, db_index=True)
>
> class MeasurandMin(models.Model):
> """
> Model to store the min value for a measurand
> """
> measurand = models.OneToOneField(Measurand, parent_link=True)
> value = models.FloatField(help_text='the min measurement value',
> db_index=True)
> unit = models.CharField(help_text='the unit of the measurement
> value', max_length=20, db_index=True)
>
> class MeasurandSigma(models.Model):
> """
> Model to store the standard derivation (sigma) value for a
> measurand
> """
> measurand = models.OneToOneField(Measurand, parent_link=True)
> value = models.FloatField(help_text='the sigma measurement value',
> db_index=True)
> unit = models.CharField(help_text='the unit of the measurement
> value', max_length=20, db_index=True)
>
> now, i want to receive a list with all Measurand and also the Average,
> Max, Min, Sigma Values for every measurand.
> The problem is, that when i have a list with 50 measurands and access
> the measurandmax in my template with {{measurand.measurandmax.value}},
> this produce a query. and also every access to max, min and sigma
> produces a query.
>
> When i have a list with 100 Measurand, i have about 400 queries to get
> the data.
>
> The queries look like this:
>
> SELECT "data_measurandaverage"."id",
> "data_measurandaverage"."measurand_id",
> "data_measurandaverage"."value", "data_measurandaverage"."unit" FROM
> "data_measurandaverage" WHERE "data_measurandaverage"."measurand_id" =
> 1
> SELECT "data_measurandmin"."id", "data_measurandmin"."measurand_id",
> "data_measurandmin"."value", "data_measurandmin"."unit" FROM
> "data_measurandmin" WHERE "data_measurandmin"."measurand_id" = 1
> SELECT "data_measurandmax"."id", "data_measurandmax"."measurand_id",
> "data_measurandmax"."value", "data_measurandmax"."unit" FROM
> "data_measurandmax" WHERE "data_measurandmax"."measurand_id" = 1
> SELECT "data_measurandsigma"."id",
> "data_measurandsigma"."measurand_id", "data_measurandsigma"."value",
> "data_measurandsigma"."unit" FROM "data_measurandsigma" WHERE
> "data_measurandsigma"."measurand_id" = 1
>
> Any ideas how i can improve the queries or the structure of my models?
>
> cheers,
>
> tom
You haven't shown any of the ORM calls that are generating the
queries. But you will almost certainly find that select_related is
your friend here - it can massively cut down the number of queries
when accessing related items.
I would wonder however if Max, Min, Average and Sigma really need to
live in separate models. Since they're just one-to-ones, you're not
gaining anything in terms of normalization by keeping them separate -
you still have seperate value and unit fields for each one. I would
recommend bringing all that back into the main Measurand model, so you
would have average_value and average_unit, etc.
Also, it won't help with efficiency, but you might want to set choices
on the unit fields, to avoid people typing in slightly different
versions of the same unit type - eg centimetre, centimeter, cm...
--
DR.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
