Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>A statement like <code>card_list=[Demo.objects.filter(age_group=age)[i].card_no for i in range(popn)]</code> will generate <code>popn</code> seperate queries and database hits. The query in the <code>for</code>-loop will also hit the database <code>popn</code> times. As a general rule, you should try to minimize the amount of queries you use, and you should only select the records you need. </p> <p>With a few adjustments to your code this can be done in just one query. </p> <ul> <li><p>There's generally no need to manually specify a primary_key, and in all but some very specific cases it's even better not to define any. Django automatically adds an indexed, auto-incremental primary key field. If you need the card_no field as a unique field, and you need to find rows based on this field, use this:</p> <pre><code>class Demo(models.Model): card_no = models.SlugField(max_length=20, unique=True) ... </code></pre> <p>SlugField automatically adds a database index to the column, essentially making selections by this field as fast as when it is a primary key. This still allows other ways to access the table, e.g. foreign keys (as I'll explain in my next point), to use the (slightly) faster integer field specified by Django, and will ease the use of the model in Django. </p></li> <li><p>If you need to relate an object to an object in another table, use <code>models.ForeignKey</code>. Django gives you a whole set of new functionality that not only makes it easier to use the models, it also makes a lot of queries faster by using <code>JOIN</code> clauses in the SQL query. So for you example:</p> <pre><code>class Fact_table(models.Model): card = models.ForeignKey(Demo, related_name='facts') ... </code></pre> <p>The related_name fields allows you to access all Fact_table objects related to a Demo instance by using <code>instance.facts</code> in Django. (See <a href="https://docs.djangoproject.com/en/dev/ref/models/fields/#module-django.db.models.fields.related" rel="nofollow">https://docs.djangoproject.com/en/dev/ref/models/fields/#module-django.db.models.fields.related</a>)</p></li> </ul> <p>With these two changes, your query (including the loop over the different age_groups) can be changed into a blazing-fast one-hit query giving you the average duration of calls made by each age_group:</p> <pre><code>age_groups = Demo.objects.values('age_group').annotate(duration_avg=Avg('facts__duration')) for group in age_groups: print "Age group: %s - Average duration: %s" % group['age_group'], group['duration_avg'] </code></pre> <p><code>.values('age_group')</code> selects just the age_group field from the Demo's database table. <code>.annotate(duration_avg=Avg('facts__duration'))</code> takes every unique result from <code>values</code> (thus each unique age_group), and for each unique result will fetch all Fact_table objects related to any Demo object within that age_group, and calculate the average of all the duration fields - all in a single query. </p>
    singulars
    1. This table or related slice is empty.
    1. PODjango Query Optimisation
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USknbk
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COi edited models and also mysql table accordingly but in duration_avg only 'None' value comes. Actually 'None' comes only in one case but not in others. Is it so if 'None' is faced annotate doesn't work and why it affected other age_group?? Anyway, optimisation strategy was quite nice.
      singulars
      1. PO
      1. USTisal
    2. CO@Bishal That's strange, because a quick test with my own project shows that a `None` result should not affect the other results. Can you update your code in the question?
      singulars
      1. PO
      1. USknbk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload