Hi, we're just using UNION instead of OR because the result is (here, for the given SQL) the same (due to the ORDER BY).
Regards, Mark On 28 Mrz., 20:50, Ayende Rahien <[email protected]> wrote: > Can you explain the difference between the two options?I am not good at > doing diffs in my head > > On Fri, Mar 27, 2009 at 2:52 AM, mjcoder <[email protected]> wrote: > > > Hi, > > > we're having heavy performance problems with Rhino Security. We use: > > - Rhino Security from trunk as of 2009-03-25 > > - Sybase SQL Anywhere 10.0.1 as DB with latest updates (10.0.1.3835) > > > The produced (slow, ~ 7 sec.) SQL is: > > > SELECT > > count(*) > > FROM > > Adressen this_ > > WHERE > > 1 = ( > > SELECT TOP 1 START AT 1 > > this_0_.Allow as y0_ > > FROM > > security_Permissions this_0_ > > inner join > > security_Operations op1_ > > on this_0_.Operation=op1_.Id > > left outer join > > security_EntitiesGroups entitygrou2_ > > on this_0_.EntitiesGroup=entitygrou2_.Id > > left outer join > > security_EntityReferencesToEntitiesGroups entities7_ > > on entitygrou2_.Id=entities7_.GroupId > > left outer join > > security_EntityReferences entitykey3_ > > on entities7_.EntityReferenceId=entitykey3_.Id > > WHERE > > op1_.Name in ('/DataAccess/Address/Select', '/DataAccess/ > > Address', '/DataAccess') > > and this_0_.UsersGroup = > > 'a99075b1-05c7-4fe0-91e5-9bd80112ff11' > > and ( > > (this_.fldsecurityid = this_0_.EntitySecurityKey or > > this_.fldsecurityid = entitykey3_.EntitySecurityKey) > > or (this_0_.EntitySecurityKey is null and > > this_0_.EntitiesGroup is null) > > ) > > ORDER BY > > this_0_.Level desc, this_0_.Allow asc > > ) > > > But the only one that's fast enough (< 0.1 sec) is: > > > SELECT > > count(*) as c > > FROM > > Adressen this_ > > WHERE > > 1 = ( > > SELECT TOP 1 START AT 1 > > t.y0_ > > from ( > > SELECT > > this_0_.Allow as y0_, this_0_.Level , > > this_0_.Allow > > FROM > > security_Permissions this_0_ > > inner join > > security_Operations op1_ > > on this_0_.Operation=op1_.Id > > left outer join > > security_EntitiesGroups entitygrou2_ > > on > > this_0_.EntitiesGroup=entitygrou2_.Id > > left outer join > > security_EntityReferencesToEntitiesGroups > > entities7_ > > on > > entitygrou2_.Id=entities7_.GroupId > > left outer join > > security_EntityReferences entitykey3_ > > on > > entities7_.EntityReferenceId=entitykey3_.Id > > WHERE > > op1_.Name in ('/DataAccess/Address/Select', > > '/DataAccess/Address', > > '/DataAccess') > > and this_0_.UsersGroup = > > 'a99075b1-05c7-4fe0-91e5-9bd80112ff11' > > and ( > > (this_.fldsecurityid = > > entitykey3_.EntitySecurityKey) > > ) > > UNION > > SELECT > > this_0_.Allow as y0_, this_0_.Level , > > this_0_.Allow > > FROM > > security_Permissions this_0_ > > inner join > > security_Operations op1_ > > on this_0_.Operation=op1_.Id > > left outer join > > security_EntitiesGroups entitygrou2_ > > on > > this_0_.EntitiesGroup=entitygrou2_.Id > > left outer join > > security_EntityReferencesToEntitiesGroups > > entities7_ > > on > > entitygrou2_.Id=entities7_.GroupId > > left outer join > > security_EntityReferences entitykey3_ > > on > > entities7_.EntityReferenceId=entitykey3_.Id > > WHERE > > op1_.Name in ('/DataAccess/Address/Select', > > '/DataAccess/Address', > > '/DataAccess') > > and this_0_.UsersGroup = > > 'a99075b1-05c7-4fe0-91e5-9bd80112ff11' > > and ( > > (this_0_.EntitySecurityKey is null > > and this_0_.EntitiesGroup is > > null) > > ) > > ) > > as t > > order by > > t.Level desc, t.Allow asc > > ) > > > The main problem is that the OR operator at this position is very > > slow ... > > Any idea how we can modify Rhino Security to produce the second SQL > > statement? > > > Regards, > > Mark Junker --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en -~----------~----~----~----~------~----~------~--~---
