id,summary,reporter,owner,description,type,status,priority,milestone,component,version,resolution,keywords,cc 52,Performance improvement in sql.py,bse,jerome,"This same message went to pykota-devel. Sorry. I have noticed a performance problem in the command pkusers In short: Possible performance improvement by making special case in sql.py for getMatchingUser when userpattern is a single username can save many CPU-hours per day. File: /usr/lib/python2.4/site-packages/pykota/storages/sql.py Versions: 1.26 and 1.27 alpha Symptom: When adjusting balance for a user, a lot of CPU and wall time is used. time pkusers --balance +1 bse Extracting datas... Modification... Done. Average speed : 1.28 entries per second. real 0m0.963s user 0m0.630s sys 0m0.130s This is with updated .pyc and .pyo Impact: This command is run once for each job to account for duplex (and other) (yes, i could be smarter). 15000 jobs a day * 1 second is a lot of CPU. Problem: in sql.py at line 477, a 'SELECT * from USERS;' is done. I have 25000 users in the system, and this select takes a while Solution: Make a special case for userpattern consisting only of a single user name. diff sql.py.work sql.py.DIST 477,481c477 < if ( "","" not in userpattern ) and ( ""*"" not in userpattern ): < result = self.doSearch(""SELECT * FROM users WHERE username='%s'"" % (userpattern,)) < else: < result = self.doSearch(""SELECT * FROM users"") < --- > result = self.doSearch(""SELECT * FROM users"") Result: time pkusers --balance +1 bse Extracting datas... Modification... Done. Average speed : 102.07 entries per second. real 0m0.138s user 0m0.080s sys 0m0.040s A saving of 0.82 seconds wall time, and 0.65 seconds CPU time Other improvements The same special case can probably be used for getMatchingPrinters getMatchingGroups getMatchingBillingCodes //Bse",enhancement,closed,minor,,pykota,stable,fixed,,