Hi, just some information about our test:
1. we had one entity group 2. this entity group was attached to every record in the table (~1500 records) 3. we had one user group 4. this user group had a permission for the given entity group getting the first 20 records (select * from ...) and the number of records (select count(*) from ...) took ~7 (unsorted) to ~15 (sorted) seconds. The time increases when fetching 20 records from within the full record set (i.e. records 101 to 120). That's the reason why we're limiting the functionality of Rhino.Security and are avoiding ORs. Now we have times from <0.1 sec. I hope that there are ways to create a Rhino.Secority that's fast on DB systems other than MS SQL Server. Regards, Mark On 31 Mrz., 12:17, Bart Reyserhove <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
