[Date Prev][Date Next] [Chronological] [Thread] [Top]

Re: back-sql sqlquery



Hello again,

On Wed, Oct 19, 2005 at 11:27:29AM +0200, Thomas Hoth wrote:
> On Wed, Oct 19, 2005 at 10:14:55AM +0200, Pierangelo Masarati wrote:
> > 
> > > Hello,
> > >
> > > Why do back-sql querys like
> > > (2=2 OR (ldap_entries.id=ldap_entry_objclasses.entry_id AND
> > > ldap_entry_objclasses.oc_name='" /* '))
> > >
> > > For what is the 2=2 ? This makes my query very slow.
> > 
> > The "2=2" is a placeholder for "TRUE".  Different numbers indicate
> > different places in the code where the replacement took place.  I'd be
> > much more worried about the "ldap_entry_objclasses.oc_name='" /* '" which
> > clearly indicates an error in the code, or some abnormal behavior at
> > least.  Unfortunately you don't provide enough info to track where it
> > might have happened.
> 
> I think the "ldap_entry_objclasses.oc_name='" /* '" ist not a bug. It
> will fill out at the runtime the full query is:
> 
> SELECT DISTINCT ldap_entries.id,groups.id,text('posixGroup') AS
> objectClass,ldap_entries.dn AS dn FROM ldap_entries,groups,ldap_entry_objclasses,posix_account,groups_users
> WHERE groups.id=ldap_entries.keyval AND ldap_entries.oc_map_id=4 AND 9=9 AND groups_users.memberuid=posix_account.uidnumber AND
> groups_users.gidnumber=groups.gidnumber AND ((2=2 OR (ldap_entries.id=ldap_entry_objclasses.entry_id AND
> ldap_entry_objclasses.oc_name='posixGroup')) AN ((posix_account.uid='lml34') OR 7=7));
> 
> It's from line 738 in servers/slapd/back-sql/search.c
> Iam not an SQL-geek but what ist the sense to ask "true OR query" ?

someone tells me this:

16:51 *»*     SELECT ldap_entries.id,groups.id,text('posixGroup') AS
objectClass,ldap_entries.dn AS dn FROM +ldap_entries,groups,ldap_entry_objclasses,posix_account,groups_users
WHERE groups.id=ldap_entries.keyval AND ldap_entries.oc_map_id=4 AND 9=9 AND
+groups_users.memberuid=posix_account.uidnumber AND groups_users.gidnumber=groups.gidnumber AND ((2=2) AND
((upper(posix_account.uid)='LML23') OR 7=7));
16:51 **      omg
16:52 *»*     14 Seconds, originally says SELECt DISTINCT -> 900 seconds
16:52 **      its combining 5 tables in an outside join
16:52 *»*     yes
16:53 *»*     my conclusion 2h ago had been: rewrite the query generator. I fear this is still true
16:54 **      yes and whoever re-writes it needs to understand joins properly
16:55 *»*     this might be a problem, then. :->
16:58 **      this I think is the same query written with joins
select ldap_entries.id,groups.id,text('posixGroup') AS
objectClass,ldap_entries.dn AS dn FROM ldap_entries join groups on
groups.id = ldap_entries.keyval join  groups_users on
groups_users.gidnumber=groups.gidnumber join posix_account on
posix_account.uidnumber =  groups_users.memberuid where
ldap_entries.oc_map_id=4 AND (upper(posix_account.uid)='LML23')

17:04 *»*     a) it runs only a split of a second
17:05 **      does it return the same results ?
17:05 *»*     b) it gives only 3 line sof results, the other query gave
these 3 lines over and over
17:05 *»*     so after the distinct it is the same


Iam not an C - programmer, but my question is it is possible to rewrite the
query generator to get faster sql?

Thanks 
  Thomas