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:


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.


1.26 and 1.27 alpha

When adjusting balance for a user, a lot of CPU and wall time is used.
time pkusers --balance +1 bse
Extracting datas...
Done. Average speed : 1.28 entries per second.

real 0m0.963s
user 0m0.630s
sys 0m0.130s

This is with updated .pyc and .pyo

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.

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

Make a special case for userpattern consisting only of a single user name.

diff sql.py.work sql.py.DIST
< 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")

time pkusers --balance +1 bse
Extracting datas...
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


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.