Rhino Security: Resulting SQL produced by AddPermissionsToQuery is slow

[mjcoder]

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

[Bart Reyserhove]

We came to a very acceptable performance by adding two indexes. Still we should probably think about denormalizing the data model like Ayende said before.

USE [FleetBox]

GO

/****** Object: Index ['testindex'] Script Date: 02/04/2009 10:58:55 ******/

CREATE NONCLUSTERED INDEX ['testindex'] ON [dbo].[security_Permissions]

(

[Operation] ASC,

[UsersGroup] ASC

)

INCLUDE ( [EntitySecurityKey],

[Allow],

[Level],

[EntitiesGroup]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

 

 

USE [FleetBox]

GO

/****** Object: Index [IX_security_Operations_Name] Script Date: 02/04/2009 10:59:29 ******/

CREATE UNIQUE NONCLUSTERED INDEX [IX_security_Operations_Name] ON[dbo].[security_Operations]

(

[Name] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

[Ayende Rahien]

Can you explain the difference between the two options?

I am not good at doing diffs in my head

[mjcoder]

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 <aye...@ayende.com> wrote:
> Can you explain the difference between the two options?I am not good at

[mjcoder]

Hi,

I tried those indexes but I'm a bit puzzled about the "CREATE
INDEX ... INCLUDE" syntax. This doesn't seem to exist for Sybase SQL
Anywhere. However, I created the "testindex" containing all the
columns (before and after the "INCLUDE") but this doesn't help either.
The performance is still extremely low.

> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
> IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =

[mjcoder]

We've currently "fixed" it by enhancing Rhino.Security. Now we're
allowed to avoid the "OR" statements by specifying that we only want
to recognize entity groups instead of entities, entity groups, and
global access rights.

You can download the patch from here:

http://s244184881.online.de/downloads/rhino-tools-security-speed.patch

Going this we're limited to using entity groups (which perfectly fits
our current needs) - but at least it's fast now ...
I It would be ideal if s/o comes up with a solution that uses tree
SELECT statements combined with UNIONs but I don't know how to do this
using NHibernate. Any idea?

Regards,
Mark

On 28 Mrz., 20:50, Ayende Rahien <aye...@ayende.com> wrote:

> Can you explain the difference between the two options?I am not good at

[Ayende Rahien]

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.

[Bart Reyserhove]

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.

[mjcoder]

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 <bart.reyserh...@gmail.com> 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 <aye...@ayende.com> 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