Changes between Version 25 and Version 26 of DeveloperGuidelines/Optimisation

Timestamp:

06/18/10 07:59:54 (15 years ago)

Author:

Dominic König

Comment:

--

DeveloperGuidelines/Optimisation

@@ -1 @@
-== Optimisation ==
+[[TOC]]
+= Optimisation =
 
 Writing code which runs fast.
@@ -8 +9 @@
 There are more things to consider besides performance, e.g. usability and maintainability of code. New contributors should be able to easily understand the code, and bugs should be easy to find.
 
-Python Tips:
+== Python Tips ==
  * http://wiki.python.org/moin/PythonSpeed/PerformanceTips
  * http://www.python.org/doc/essays/list2str/
@@ -15 +16 @@
  * If a specific inner-loop routine cannot be optimised in Python, then consider writing a C routine for this use case.
 
-Web2Py Tips:
+== Web2Py Tips ==
  1. Optimize the models, throw away what we don't need. Every field counts.[[BR]]Especially problematic in view of performance are references (joins), as they execute implicit DB requests. The more complex references are, the slower the model loads.
 
@@ -29 +30 @@
 
  7. Consider having configurations which are read from DB frequently but written-to rarely, be set in configuration files which are written-out from the DB (like the CSS from themes)
+
+== Golden Rules for DB Queries ==
+
+  These "rules" might seem a matter of course, however, sometimes you need to take a second look at your code:
+
+ 1. '''Use joins''' - one complex query is usually more efficient than multiple simple queries (and gives the DB server a chance to optimize):
+
+{{{
+codes = db(db.mytable.name == name).select()
+for code in codes:
+    records = db(db.othertable.code == code).select()
+}}}
+
+better:
+
+{{{
+rows = db((db.mytable.name == name) & (db.othertable.code == db.mytable.code)).select()
+for row in rows:
+    mytable_record = row.mytable
+    othertable_record = row.othertable
+}}}
+
+ 2. '''Ask exactly for what you expect''':
+    - if you expect only one result, then limit the search by limitby:
+
+{{{
+db(db.mytable.id == id).select().first()
+}}}
+
+should be:
+
+{{{
+db(db.mytable.id == id).select(limitby=(0,1)).first()
+}}}
+
+    - if you need only certain fields of a record, then don't ask for all:
+
+{{{
+my_value = db(db.mytable.id == id).select(limitby=(0,1)).first().value
+}}}
+
+should be:
+
+{{{
+my_value = db(db.mytable.id == id).select(db.mytable.value, limitby=(0,1)).first().value
+}}}
+
+ 3. '''Don't ask twice for the same record''':
+
+{{{
+my_value = db(db.mytable.id == id).select(db.mytable.value, limitby=(0,1)).first().value
+...
+other_value = db(db.mytable.id == id).select(db.mytable.other_value, limitby=(0,1)).first().other_value
+}}}
+
+better:
+
+{{{
+row = db(db.mytable.id == id).select(db.mytable.value, limitby=(0,1)).first()
+if row:
+    my_value = row.value
+    other_value = row.other_value
+}}}
+
+  4. '''Don't loop over queries''', if you can avoid id:
+
+{{{
+for id in ids:
+   my_record = db(mytable.id == id).select().first()
+   ...
+}}}
+
+(much) better:
+{{{
+records = db(mytable.id.belongs(ids)).select()
+for record in records:
+   ...
+}}}
 
 === Specific Examples ===