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

Re: back-sql sqlquery





Thomas Hoth wrote:

Hello again,

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


Yes, of course. And since it's an open project anyone willing can work on it and propose patches, fixes and enhancements.
Oh, by the way, there are RDBMS that do not understand the sql keyword 'join'...




   SysNet - via Dossi,8 27100 Pavia Tel: +390382573859 Fax: +390382476497