I'm having extreme performance issues, due to some lousy
code. I'm hoping this is my codes fault or SQLite, and not the fault
of SQLObject or cherryPy.
This is a turbogears-project btw.
Here is the code:
--------------------------------------------
def editItem(self, name=None, comment=None):
## Fetch the item
try:
item = Item.byName(name)
except SQLObjectNotFound:
turbogears.flash("Error!")
redirect("/register")
## Update comment
if comment <> None:
item.comment = comment
type = item.type
category = type.category
## Fetch all loans for the item
loans = Loan.selectBy(item_id=item.id)
## Order loans by the time the items was borrowed
loans = loans.orderBy(Loan.q.borrowed)
## Reverse the order
loans = loans.reversed()
## Limit to only 10 results
loans = loans[:10]
## Get ready for some hairy code
tmp = []
i = 0
for loan in loans:
## Append the loan-dictionary to my list
tmp.append(loan)
## Add the person who registered the loan
tmp[i].loaner = User.get(loan.loaner.id)
## If item has been delivered, Add the person who
## registered the loan
if loan.deliverer == None:
tmp[i].deliverer = None
else:
tmp[i].deliverer = User.get(loan.deliverer.id)
## Get the member who loaned the item
tmp[i].member = Member.get(loan.user_id)
i = i + 1
....
--------------------------------------------
This function takes a long time to run, and all of it can be done
in SQL. I just don't know how using SQLObject.
I basically need to join 3 tables:
Loan, User and Member.
Loan has 3 foreign keys. 'borrower' and 'deliverer' are user_ids from
User and 'loaner' is user_id from Member. I also need to join on
Loan.q.item_id = item.id.
I also need to get the 10 latest loans ordered by the date they where borrowed.
Is there any easy way to do this, because this code is killing performance.
The schema looks like this:
class Loan(SQLObject):
class sqlmeta:
cacheValues = False
item_id = ForeignKey('Item')
user_id = ForeignKey('Member')
loaner = ForeignKey('User')
deliverer = ForeignKey('User', default=None)
borrowed = DateTimeCol(default=datetime.now)
delivered = DateTimeCol(default=None)
Also, for the db-gurus, is there a normalization problem in this schema?
regards
Tor
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
