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.
On 27 Mrz., 20:58, Bart Reyserhove <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
