Ticket #52 (closed enhancement: fixed)
Performance improvement in sql.py
| Reported by: | bse | Owned by: | jerome |
|---|---|---|---|
| Priority: | minor | Milestone: | |
| Component: | pykota | Version: | stable |
| Keywords: | Cc: |
Description
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
