I don't think there is an easy way of getting union statement in NH to work.What I would rather do is change the data model to a de-normalized one that would make it more efficient for reading That was always the plan for Rhino Security, but for my need, so far, I haven't needed it, so I didn't do that.
On Mon, Mar 30, 2009 at 3:55 AM, mjcoder <[email protected]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
