back-sql performance problem workaround

[Brad Midgley <bmidgley@xmission.com>]

Hi

I think I found a fix for this. The access control was extremely locked 
down, but that seems to cause trouble with back-sql. The original problem:

When I do a straightforward search on openldap using back-sql, a select 
is run that pulls every entry from ldap_entries. That's 40,000 entries 
for this database. It means getting a user's *own* entries after 
authenticating can take another 5-15 seconds! I can understand other 
kinds of searches taking a long time, but if we already know the name of 
the object it should be quick.

bmidgley@jabbed:~$ ldapsearch -H ldap://gambit.uen.org -D 
uid=bmidgley,dc=my,dc=uen,dc=org -x -W -d 256 -z 10 "(uid=bmidgley)"
request 1 done
# extended LDIF
#
# LDAPv3
# base <> with scope sub
# filter: (uid=bmidgley)
# requesting: ALL
#

# bmidgley, my.uen.org
dn: uid=bmidgley,dc=my,dc=uen,dc=org
objectClass: inetOrgPerson
objectClass: posixAccount
cn: Brad Midgley
ou: Alpine School District
ou: Utah Education Network
sn: Midgley
uid: bmidgley
mail: bmidgley@uen.org
gidNumber: 74032
givenName: Brad
uidNumber: 74032
homeDirectory: /home/bmidgley
employeeNumber: 74032
request 2 done

# search result
search: 2
result: 4 Size limit exceeded

# numResponses: 2
# numEntries: 1
bmidgley@jabbed:~$

The complete log looks like: 
http://www.xmission.com/~bmidgley/openldap/slapd-selectall.log

The bottom line is it looks like this is being executed:

SELECT DISTINCT 
ldap_entries.id,utahlink..educator.teacher_id,('posixAccount') AS 
objectClass,ldap_entries.dn AS dn FROM ldap_entries,utahlink..educator 
WHERE utahlink..educator.teacher_id=ldap_entries.keyval AND 
ldap_entries.oc_map_id=1 AND upper(ldap_entries.dn) LIKE 
"%DC=MY,DC=UEN,DC=ORG" and 1=1
Every entry in our database matches the dn test.

Solution: changing access from

access to *
        by dn="cn=Manager,dc=my,dc=uen,dc=org" write
        by self read
        by * none

to

access to *
        by dn="cn=Manager,dc=my,dc=uen,dc=org" write
        by self read
        by * read

Caused the select to change to:

SELECT DISTINCT 
ldap_entries.id,utahlink..educator.teacher_id,('inetOrgPerson') AS 
objectClass,ldap_entries.dn AS dn FROM ldap_entries,utahlink..educator 
WHERE utahlink..educator.teacher_id=ldap_entries.keyval AND 
ldap_entries.oc_map_id=convert(integer,?) AND upper(ldap_entries.dn) 
LIKE ? AND (upper(username)='BMIDGLEY')

Which allows the db to produce only one record instead of all of them.

whew!
Brad