Hi Mark, I don't know much about sqlite perfomance, but 390k inserts in 4 minuts are 1625 inserts/second which I think is pretty impressive :D One of the things that affects most insert and update performance is index set up on fields. For each indexed field inserted, the db must write the data record AND update the index. Removing unneeded indexes would certainly speed up something.
Cheers, 2011/8/4 Mark Erbaugh <m...@microenh.com> > I'm using SA (with SQLite) with a schema like: > > A -< B -< C -< D > > where -< means that the tables have a one to many relationship > > I'm populating a sample data set where there are 25 rows in A, 25 rows in > B for each row in A, 25 rows in C for each row in B and 25 rows in D for > each row in C. This results in about 390k rows in D. The database itself > is only about 12 MB, but it takes a long time (several minutes) to write the > data to the file. > > I'm taking the approach of appending items to the table's relationship > column. > > for i in range(25): > x = A() > session.add(A) > for j in range(25): > y = B() > x.b.append(y) > for k in range(25): > z = C() > y.c.append(z) > for l in range(25): > xx = D() > z.d.append(xx) > session.flush() > > > The bulk of the delay seems to be the session.flush call. > > I'm using the Pyramid framework which used Python's transaction module. I > call transaction.begin() prior to adding the rows. According to the SQLite > FAQ, this should speed things up. > > Are there any suggestions on how to speed things up? > > Thanks, > Mark > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- ---------------------------------- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 ---------------------------------- -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
