Issue: Slow queries
Author: coleifer
State: closed
Created: 2014-01-11 04:58:51

Hi I have following issue with fetching data from MySQL.

My app is based on initial peewee example (https://github.com/coleifer/peewee/blob/master/example/app.py).

My db model (shorten):

``` python
db.py
---------------------------------
#!/usr/bin/env python
# -*- coding: utf-8 -*- 

from peewee import *
from peewee import Expression
import datetime
import ConfigParser
import os

# for 
OP_REGEXP = 'regexp'

config = ConfigParser.ConfigParser()
config.read('config.cfg')

DB = config.get('database', 'db')
HOST = config.get('database', 'host')
USER = config.get('database', 'user')
PASSWORD = config.get('database', 'password')
DEBUG = int(os.getenv('RESULTS_DEBUG', 0)) == 1


# create a database instance -- our models will use this database to
# persist information
if DEBUG:
    database = SqliteDatabase(':memory:')
else:
    database = MySQLDatabase(DB,  autocommit=False, host=HOST, user=USER, passwd=PASSWORD)

# model definitions -- the standard "pattern" is to define a base model class
# that specifies which database to use.  then, any subclasses will automatically
# use the correct storage.
class BaseModel(Model):
    class Meta:
        database = database

# test db table
class Test(BaseModel):
    parent = ForeignKeyField('self', related_name='children', null=True, cascade=True)
    name = TextField()

# hw db table
class Hw(BaseModel):
    name = TextField()
    vendor = TextField()
    device = TextField()
    codec = TextField(default="")

# release db table
class Release(BaseModel):
    major = TextField()
    minor = TextField()
    is_zstream = BooleanField(default=False)

# arch db table
class Arch(BaseModel):
    name = TextField()

# component db table
class Component(BaseModel):
    n = TextField()
    v = TextField()
    r = TextField()
    rhel = ForeignKeyField(Release)

# execution db table
class Execution(BaseModel):
    time = DateTimeField(default=datetime.datetime.now, help_text="Insert time")
    log = TextField()
    identifier = TextField()
    test = ForeignKeyField(Test)
    hw = ForeignKeyField(Hw, null=True)
    release = ForeignKeyField(Release)
    arch = ForeignKeyField(Arch)
    component = ForeignKeyField(Component, related_name='component_ver')
    kernel = ForeignKeyField(Component, related_name='kernel_ver', null=True)
    tool = ForeignKeyField(Component, related_name='tool_ver', null=True)
    note = TextField()

    class Meta:
        order_by = ('time',)

EOF
-----------------------------------------
```

I have 21 records in execution table selecting all of them takes to long, like 30 sec.

``` python
import db
rows = db.Execution.select()

for row in rows:                                                                                              
    print row.identifier, row.test.name, row.component.n, row.component.v
```


You have multiple O(n) queries lurking in that loop.  Every foreign key resolution (`row.test.name`, `row.component.n`) triggers as query.

You can accomplish this in O(1) queries (tested locally using sqlite).  Try this:

``` python
rows = (
    Execution
    .select(
        Execution,
        Test,
        Component)
    .join(Test).switch(Execution)
    .join(Component))

for row in rows:
    print row.identifier, row.test.name, row.component.n, row.component.v
```

I tried to get it to "pre-fetch" the `kernel` and `tool` attributes, but this did not work as I intended...I'll look into that, but hopefully this should help speed up your query.


Hey,

here is what I got:

```
In [17]: data = db.Execution.select(db.Test,db.Result,db.Arch,db.Component,db.Phase,db.Tester).join(db.Test).switch(db.Execution).join(db.Result).switch(db.Execution).join(db.Arch).switch(db.Execution).join(db.Component).switch(db.Execution).join(db.Phase).switch(db.Execution).join(db.Tester).switch(db.Execution)

In [18]: for row in data:
   ....:     print row.identifier, row.test.name, row.component.n, row.component.v
   ....:     
   ....:     
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)

/home/tpelka/workspace/desktopqe-resultsDB/<ipython console> in <module>()

/usr/lib/python2.6/site-packages/peewee.pyc in next(self)
   1190             return inst
   1191 
-> 1192         obj = self.iterate()
   1193         self._result_cache.append(obj)
   1194         self.__ct += 1

/usr/lib/python2.6/site-packages/peewee.pyc in iterate(self)
   1178             self.initialize(self.cursor.description)
   1179             self._initialized = True
-> 1180         return self.process_row(row)
   1181 
   1182     def iterator(self):

/usr/lib/python2.6/site-packages/peewee.pyc in process_row(self, row)
   1298     def process_row(self, row):
   1299         collected = self.construct_instance(row)
-> 1300         instances = self.follow_joins(collected)
   1301         for i in instances:
   1302             i.prepared()

/usr/lib/python2.6/site-packages/peewee.pyc in follow_joins(self, collected)
   1319 
   1320     def follow_joins(self, collected):
-> 1321         prepared = [collected[self.model]]
   1322         for (lhs, attr, rhs) in self.join_map:
   1323             inst = collected[lhs]

KeyError: <class 'db.Execution'>
```


```
<cleifer> i can't replicate b/c i don't have model definitions for phase, result or tester
<cleifer> tpelka, ^^
<tpelka> cleifer: let me send you the model and addtodb.py 
<tpelka> github?
<tpelka> cleifer: can I attache files to github?
<cleifer> make a gist then link it?
<cleifer> that might b easiest
<tpelka> grr Unfortunately we don't support that file type yet. Please use PNG, GIF, or JPG. 
<tpelka> https://gist.github.com/tompelka/8400960 = db.py
<cleifer> thanks!
<tpelka> https://gist.github.com/tompelka/8400976 = addtodb.py
<tpelka> cleifer: in this case I should change memory to some sqlite file right?
<cleifer> no worries
```


I modified your test code and was not able to replicate the error.  The big change was to add `Execution` to the list of tables selected.

``` python
from peewee import *
from peewee import create_model_tables
from peewee import Expression
import datetime
import os

# for
OP_REGEXP = 'regexp'

# server not defined, create it in memory
database = SqliteDatabase(':memory:')

# model definitions -- the standard "pattern" is to define a base model class
# that specifies which database to use.  then, any subclasses will automatically
# use the correct storage.
class BaseModel(Model):
    class Meta:
        database = database


# test db table
class Test(BaseModel):
    parent = ForeignKeyField('self', related_name='children', null=True, cascade=True)
    name = TextField()


# hw db table
class Hw(BaseModel):
    name = TextField()
    vendor = TextField()
    device = TextField()
    codec = TextField(default="")


# status db table
class Status(BaseModel):
    name = TextField()


# result db table
class Result(BaseModel):
    status = ForeignKeyField(Status)
    num_passed = IntegerField()
    num_failed = IntegerField()
    num_skipped = IntegerField(null=True)


# release db table
class Release(BaseModel):
    major = TextField()
    minor = TextField()
    is_zstream = BooleanField(default=False)


# arch db table
class Arch(BaseModel):
    name = TextField()


# component db table
class Component(BaseModel):
    n = TextField()
    v = TextField()
    r = TextField()
    rhel = ForeignKeyField(Release)


# phase db table
class Phase(BaseModel):
    name = TextField()


# tester db table
class Tester(BaseModel):
    login = TextField()
    full_name = TextField()


# execution db table
class Execution(BaseModel):
    time = DateTimeField(default=datetime.datetime.now, help_text="Insert time")
    log = TextField()
    identifier = TextField()
    tcmsID = TextField(null=True)
    beakerID = TextField(null=True)
    test = ForeignKeyField(Test)
    hw = ForeignKeyField(Hw, null=True)
    result = ForeignKeyField(Result)
    release = ForeignKeyField(Release)
    arch = ForeignKeyField(Arch)
    component = ForeignKeyField(Component, related_name='component_ver')
    kernel = ForeignKeyField(Component, related_name='kernel_ver', null=True)
    tool = ForeignKeyField(Component, related_name='tool_ver', null=True)
    phase = ForeignKeyField(Phase)
    tester = ForeignKeyField(Tester)
    note = TextField()
    is_valid = BooleanField(default=False)

    class Meta:
        order_by = ('time',)

create_model_tables([Test, Hw, Result, Status, Release, Arch, Component, Phase, Tester, Execution])

t1 = Test.create(name='t1')
t2 = Test.create(name='t2')
hw1 = Hw.create(name='hw1', vendor='', device='')
hw2 = Hw.create(name='hw2', vendor='', device='')
r1 = Release.create(major='1', minor='0')
r2 = Release.create(major='2', minor='0')
a1 = Arch.create(name='a1')
a2 = Arch.create(name='a2')
c1 = Component.create(n='c1', v='', r='', rhel=r1)
c2 = Component.create(n='c2', v='', r='', rhel=r1)
k1 = Component.create(n='k1', v='', r='', rhel=r1)
k2 = Component.create(n='k2', v='', r='', rhel=r1)
o1 = Component.create(n='o1', v='', r='', rhel=r1)
o2 = Component.create(n='o2', v='', r='', rhel=r1)
p1 = Phase.create(name='p1')
p2 = Phase.create(name='p2')
ts1 = Tester.create(login='tester1', full_name='')
ts2 = Tester.create(login='tester2', full_name='')
s1 = Status.create(name='s1')
s2 = Status.create(name='s2')
rs1 = Result.create(status=s1, num_passed=0, num_failed=0)
rs2 = Result.create(status=s2, num_passed=0, num_failed=0)

for i in range(10):
    n1 = 'e%s' % i
    n2 = 'e%s' % (i + 10)
    Execution.create(
        log=n1,
        identifier=n1,
        test=t1,
        hw=hw1,
        result=rs1,
        arch=a1,
        release=r1,
        component=c1,
        kernel=k1,
        tool=o1,
        phase=p1,
        tester=ts1,
        note=n1)
    Execution.create(
        log=n2,
        identifier=n2,
        test=t2,
        hw=hw2,
        result=rs2,
        arch=a2,
        release=r2,
        component=c2,
        kernel=k2,
        tool=o2,
        phase=p2,
        tester=ts2,
        note=n2)

import logging
logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

rows = (
    Execution
    .select(
        Execution,
        Test,
        Result,
        Arch,
        Component,
        Phase,
        Tester)
    .join(Test).switch(Execution)
    .join(Result).switch(Execution)
    .join(Arch).switch(Execution)
    .join(Component).switch(Execution)
    .join(Phase).switch(Execution)
    .join(Tester).switch(Execution))

for row in rows:
    print row.identifier, row.test.name, row.component.n, row.component.v
```

Output:

```
('
SELECT 
    t1."id", t1."time", t1."log", t1."identifier", t1."tcmsID", t1."beakerID", t1."test_id", t1."hw_id", t1."result_id", t1."release_id", t1."arch_id", t1."component_id", t1."kernel_id", t1."tool_id", t1."phase_id", t1."tester_id", t1."note", t1."is_valid", 
    t2."id", t2."parent_id", t2."name", 
    t3."id", t3."status_id", t3."num_passed", t3."num_failed", t3."num_skipped", 
    t4."id", t4."name", 
    t5."id", t5."n", t5."v", t5."r", t5."rhel_id", 
    t6."id", t6."name", 
    t7."id", t7."login", t7."full_name" 
FROM "execution" AS t1 
INNER JOIN "test" AS t2 ON (t1."test_id" = t2."id") 
INNER JOIN "result" AS t3 ON (t1."result_id" = t3."id") 
INNER JOIN "arch" AS t4 ON (t1."arch_id" = t4."id") 
INNER JOIN "component" AS t5 ON (t1."component_id" = t5."id") 
INNER JOIN "phase" AS t6 ON (t1."phase_id" = t6."id") 
INNER JOIN "tester" AS t7 ON (t1."tester_id" = t7."id") 
ORDER BY t1."time" ASC', [])

e0 t1 c1 
e10 t2 c2 
e1 t1 c1 
e11 t2 c2 
e2 t1 c1 
e12 t2 c2 
e3 t1 c1 
e13 t2 c2 
e4 t1 c1 
e14 t2 c2 
e5 t1 c1 
e15 t2 c2 
e6 t1 c1 
e16 t2 c2 
e7 t1 c1 
e17 t2 c2 
e8 t1 c1 
e18 t2 c2 
e9 t1 c1 
e19 t2 c2
```


@tompelka -- I added `Release` to the list of selected tables and joins it still works:

``` python
rows = (
    Execution
    .select(
        Execution,
        Test,
        Result,
        Release,
        Arch,
        Component,
        Phase,
        Tester)
    .join(Test).switch(Execution)
    .join(Result).switch(Execution)
    .join(Release).switch(Execution)
    .join(Arch).switch(Execution)
    .join(Component).switch(Execution)
    .join(Phase).switch(Execution)
    .join(Tester).switch(Execution))
```

You mentioned in IRC that it was still slow...can you share the code you're using in the loop?  Depending on what attributes you access you may be triggering more queries.