| 32 | |
| 33 | == Golden Rules for DB Queries == |
| 34 | |
| 35 | These "rules" might seem a matter of course, however, sometimes you need to take a second look at your code: |
| 36 | |
| 37 | 1. '''Use joins''' - one complex query is usually more efficient than multiple simple queries (and gives the DB server a chance to optimize): |
| 38 | |
| 39 | {{{ |
| 40 | codes = db(db.mytable.name == name).select() |
| 41 | for code in codes: |
| 42 | records = db(db.othertable.code == code).select() |
| 43 | }}} |
| 44 | |
| 45 | better: |
| 46 | |
| 47 | {{{ |
| 48 | rows = db((db.mytable.name == name) & (db.othertable.code == db.mytable.code)).select() |
| 49 | for row in rows: |
| 50 | mytable_record = row.mytable |
| 51 | othertable_record = row.othertable |
| 52 | }}} |
| 53 | |
| 54 | 2. '''Ask exactly for what you expect''': |
| 55 | - if you expect only one result, then limit the search by limitby: |
| 56 | |
| 57 | {{{ |
| 58 | db(db.mytable.id == id).select().first() |
| 59 | }}} |
| 60 | |
| 61 | should be: |
| 62 | |
| 63 | {{{ |
| 64 | db(db.mytable.id == id).select(limitby=(0,1)).first() |
| 65 | }}} |
| 66 | |
| 67 | - if you need only certain fields of a record, then don't ask for all: |
| 68 | |
| 69 | {{{ |
| 70 | my_value = db(db.mytable.id == id).select(limitby=(0,1)).first().value |
| 71 | }}} |
| 72 | |
| 73 | should be: |
| 74 | |
| 75 | {{{ |
| 76 | my_value = db(db.mytable.id == id).select(db.mytable.value, limitby=(0,1)).first().value |
| 77 | }}} |
| 78 | |
| 79 | 3. '''Don't ask twice for the same record''': |
| 80 | |
| 81 | {{{ |
| 82 | my_value = db(db.mytable.id == id).select(db.mytable.value, limitby=(0,1)).first().value |
| 83 | ... |
| 84 | other_value = db(db.mytable.id == id).select(db.mytable.other_value, limitby=(0,1)).first().other_value |
| 85 | }}} |
| 86 | |
| 87 | better: |
| 88 | |
| 89 | {{{ |
| 90 | row = db(db.mytable.id == id).select(db.mytable.value, limitby=(0,1)).first() |
| 91 | if row: |
| 92 | my_value = row.value |
| 93 | other_value = row.other_value |
| 94 | }}} |
| 95 | |
| 96 | 4. '''Don't loop over queries''', if you can avoid id: |
| 97 | |
| 98 | {{{ |
| 99 | for id in ids: |
| 100 | my_record = db(mytable.id == id).select().first() |
| 101 | ... |
| 102 | }}} |
| 103 | |
| 104 | (much) better: |
| 105 | {{{ |
| 106 | records = db(mytable.id.belongs(ids)).select() |
| 107 | for record in records: |
| 108 | ... |
| 109 | }}} |