Ticket #52 (closed enhancement: fixed)

Opened 14 years ago

Last modified 14 years ago

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

Change History

Changed 14 years ago by jerome

  • status changed from new to accepted

Changed 14 years ago by jerome

  • status changed from accepted to closed
  • resolution set to fixed

(In [3521]) Removed some code specific to Python v2.2 and earlier.
Improved the work done by bse@… to fix #52.
IMPORTANT : the same optimisation is currently done for users only, not for
users groups, printers or billing codes. And more importantly the code
was not ported to the LDAP backend. I need more time to do all this.

Changed 14 years ago by jerome

(In [3524]) Improved the retrival of printers and billingcodes as well as
users. References #52. For users groups it will be harder to do, next
time :-)

Changed 14 years ago by jerome

(In [3525]) Backported the improvement of the fix for #52. References #52.

Note: See TracTickets for help on using tickets.