This is almost my first attempt at using SQLAlchemy, or indeed any ORM system...
I'm trying to do a "query or create" style of operation, along the lines mentioned here: http://groups.google.com/group/sqlalchemy/browse_frm/thread/912c79e8ce005717, but the performance is really slow. I tried initially without any additional column indexes, and (observed by watching debug output) the program started off quickly enough, but after a hundred or so records the rate of insertion started to drop off dramatically. I reasoned that this was because the code was being forced to perform a linear search for each new entry processed, and hypothesized that this would be improved by judicious addition of some additional column indexes. I duly did this, but the performance is not observably improved. Code snippets below. I'm using Python 2.5.1, SA 0.4.4 and SQLite 3.5.7 I'm not looking for blinding performance, just "some time today". I'm trying to process about 1300 source records, and so far it's taken a couple of hours at 100% CPU utilization. The sqlite data file is still zero length. I can't tell how far it's got through the source data. My questions, then, are: 1. does the SQLAlchemy ORM/query/filter_by use column indexes to optimize queries? 2. if so, are there any particular steps I need to take to ensure this happens? As a supplementary question, are there any good reference materials for SA whose intended audience level lies somewhere between the excellent introductory tutorials and concise API reference available at the web site? Thanks for any hints. #g My table looks something like this: dbaccess.emails_table = Table('emails', dbaccess.metadata, Column('recordid', Integer, Sequence('recordid_seq'), primary_key=True), Column('sendadr', String(100), index=True), Column('sendnam', String(100)), Column('fromadr', String(100), index=True), Column('fromnam', String(100)), Column('toadr', String(100), index=True), Column('tonam', String(100)), Column('subject', String(100)), Column('received', String(40)), Column('spam', String(20)), Column('mailbox', String(40)), Column('folderid', Integer), Column('occurs', Integer) # How many occurrences? ) my object like this: class Email(object): def __init__(self, sendadr='', sendnam='', fromadr='', fromnam='', toadr='', tonam='', subject='', received='', spamlevel='', mailbox='', folderid=0): self.recordid = None self.sendadr = sendadr self.sendnam = sendnam self.fromadr = fromadr self.fromnam = fromnam self.toadr = toadr self.tonam = tonam self.subject = subject self.received = received self.spam = spamlevel self.mailbox = mailbox self.folderid = folderid self.occurs = 0 return and the access code like this: # Update occurrence count or add new message emlq = session.query(Email).filter_by( sendadr = sadr, fromadr = fadr, toadr = tadr, sendnam = snam, fromnam = fnam, tonam = tnam, subject = msgsubj, received = msgrcvd, spam = msgspam, folderid = folderid) try: eml = emlq.one() except Exception, e: eml = Email( sadr, snam, fadr, fnam, tadr, tnam, msgsubj, msgrcvd, msgspam, mboxname, folderid) eml.occurs += 1 session.save_or_update(eml) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
