oh, we never use web.py's db module, we only use it's web app func, why doesn't use the powerful orm tool sqlalchemy and the owner michael bayer will do his best to reply your question
2009/1/3 mb <[email protected]> > > I think I've isolated the performance issue to the web.py database > module, specifically its handling of char fields in the db result > Storage object. > > To isolate the issue, I simplified the code and compared results of > the web.py database module to the same logic using MySQL-python 1.2.2 > for the database connection. > > Here are the times to extract 4000 records from the database and > render 28,003 lines of XML by iterating over the database result set. > Each version produced identical output. > > With the web.py database module: > Database time: 0.17220 second > Render time: 176.08740 seconds <---- Here's the problem > Total time: 176.25962 seconds > > With MySQL-python 1.2.2: > Database time: 0.12736 second > Render time: 0.07125 second <---- No problem here > Total time: 0.22433 second > > All the fields in the database result set are integers, floats and > dates except one -- a char(50) field. When I eliminate the char(50) > field, the problem goes away: > > With the web.py database module, eliminating the char(50) field: > Database time: 0.15156 second > Render time: 0.11541 seconds <---- Nice and fast now! > Total time: 0.26698 seconds > > With MySQL-python 1.2.2, eliminating the char(50) field: > Database time: 0.11003 second > Render time: 0.04565 second > Total time: 0.18564 second > > So it looks like I can work around the issue by using MySQL-python > instead of the new web.py database module. Or I could revert to > version 0.23 since it works fine, and this appears to be a new issue > with 0.31. > > For what it's worth, here's my test code that uses the web.py database > module: > > class diag_webpydb: > def GET(self): > query = """SELECT d.id, d.item, d.store, d.tdate, i.descr > FROM t09_diag d > JOIN t07_items i > ON d.item = i.item > WHERE d.id BETWEEN 170001 AND 174000""" > > result = db.query(query) > > xml = '<?xml version=\"1.0\"?>\n' > xml += '<root>\n' > for row in result: > xml += ' <product>' > xml += ' <id>%s</id>\n' % row.id > xml += ' <item>%s</item>\n' % row.item > xml += ' <store>%s</store>\n' % row.store > xml += ' <date>%s</date>\n' % row.tdate > > # comment out the char(50) field to solve perf issue > # xml += ' <descr>%s</descr>\n' % row.descr > > xml += ' </product>\n' > xml += '</root>\n' > return xml > > And here's the test code using MySQL-python: > > class diag_mysqldb: > def GET(self): > query = """SELECT d.id, d.item, d.store, d.tdate, i.descr > FROM t09_diag d > JOIN t07_items i > ON d.item = i.item > WHERE d.id BETWEEN 170001 AND 174000""" > > cursor = mydb.cursor() > numrows = cursor.execute(query) > > xml = '<?xml version=\"1.0\"?>\n' > xml += '<root>\n' > for i in range(numrows): > row = cursor.fetchone() > xml += ' <product>' > xml += ' <id>%s</id>\n' % row[0] > xml += ' <item>%s</item>\n' % row[1] > xml += ' <store>%s</store>\n' % row[2] > xml += ' <date>%s</date>\n' % row[3] > > # comment out the char(50) to compare with web.py > # xml += ' <descr>%s</descr>\n' % row[4] > > xml += ' </product>\n' > xml += '</root>\n' > return xml > > > On Jan 2, 7:26 am, mb <[email protected]> wrote: > > The render function didn't change between version 0.23 and 0.31, but > > the speed went from 0.16659 second to 67.98967 seconds for the same > > action. > > > > But there were changes to the database module, so I'm wondering if > > iterating over the db result object is causing the performance hit. > > > > On Jan 2, 2:35 am, hhsuper <[email protected]> wrote: > > > > > apparently, the render method use string concat often cause the > problem, it > > > should have other method to process > > > string concat method you can try simulate other languages > StringBuilder, use > > > a list append your string, > > > and then use a join method get the string to output > > > > > On Fri, Jan 2, 2009 at 12:52 PM, mb <[email protected]> wrote: > > > > > > It looks like iterating over the db result object is what's causing > > > > the slowdown. > > > > > > With a small result set (20 records), here are the times in 0.31 to > do > > > > the db query and to render xml by iterating over the result object: > > > > db: 0.01993 second > > > > render: 0.00087 second > > > > total: 0.02671 second > > > > > > Then with a large result set (17,142 records): > > > > db: 0.09306 second > > > > render: 67.98967 seconds > > > > total: 68.08277 > > > > > > On version 0.23 and the large result set (17,142 records): > > > > db: 0.06552 > > > > render: 0.16659 > > > > total: 0.23248 > > > > > > Clearly the render function is the problem. This function takes the > > > > db result object as a parameter, and iterates over it to create the > > > > xml: > > > > > > def render_xml(items): > > > > xml = '<?xml version=\"1.0\"?>\n' > > > > xml += '<rootnode>\n' > > > > for item in items: > > > > xml += '<itemnumber>%s</itemnumber>\n' % item.id > > > > xml += '<description>%s</description>\n' % item.descr > > > > xml += '<quantity>%s</quantity>\n' % item.qty > > > > ... > > > > return xml > > > > > > dbresult = db.query(querystring) > > > > return render_xml(dbresult) > > > > > > On Jan 1, 8:04 pm, "Aaron Swartz" <[email protected]> wrote: > > > > > Can you share more details? Code? Profiler output? > > > > > > > On Jan 1, 2009 10:55 PM, "mb" <[email protected]> wrote: > > > > > > > I have a web service that queries a database and returns a fairly > > > > > large xml document based on parameters in the URL. > > > > > > > This morning, with web.py 0.23, it would return a 17,142 line > response > > > > > (491k) in 200 ms. > > > > > > > This afternoon I upgraded to 0.31, and it now takes 56 seconds for > the > > > > > same action. > > > > > > > For the entire 56 seconds, the cpu is pegged at 100% on the python > > > > > process with just one request, where on 0.23 I could run many > > > > > simultaneous requests and the cpu was rarely over 40%. > > > > > > > Any ideas why my performance has dropped by a factor of 300? > > > > > -- > > > Su zhaohui 苏召辉 > > > -- Su zhaohui 苏召辉 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web.py" 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/webpy?hl=en -~----------~----~----~----~------~----~------~--~---
