We are still ok with the current performance but I do think a denormalized version would solve potential future issues. I might have some time next week to take a look at it.
On Mon, Mar 30, 2009 at 6:39 PM, Ayende Rahien <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
