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 On Fri, Mar 27, 2009 at 8: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 -~----------~----~----~----~------~----~------~--~---
