sql optimisation for inner query

Question

I have a site with pages, each page may belong to several categories. Users are assigned to categories, and if a page has is assigned a category that a user belongs they can see the page.

originally each page belonged to one category, I could join with this table and check the permission like so

where Sec.[level] > 0

Now that pages can have multiple categories I've attempted to change the where statement of the SP that filters out results, and although it works it is greatly inefficient. I am getting the max permission of all the categories, and if at least one is greater than zero then access is granted

WHERE 
        (

            (TLA.RecipientTypeID = 2 and (SELECT MAX(CAST(dbo.PersonHasPermission_forSection(CS.SectionID, @pViewersID, 1) AS tinyint))
                                          FROM CONTENT_SECTION CS
                                          WHERE CS.ContentID = tla.RecipientID) > 0
            )
            OR
            (TLA.RecipientTypeID <> 2 AND Sec.[level] > 0)
        )

the or is because only pages have multiple cats, other types of content still have only one.

I don't know if there is enough info here but any optimisation tips would be greatly appreciated.

That function (dbo.PersonHasPermission_forSection(..)) is the problem. You need to either in-line it or change it into an inline Table Valued function. Post it's contents and we can show you how to do that. — RBarryYoung
– RBarryYoung, Commented Nov 21, 2013 at 14:47

valex · Accepted Answer · 2013-11-21 12:53:14Z

3

According your WHERE statement the main reason of slow execution is calling a function for each row in inner subquery. I think you shouldn't search MAX() and compare it with 0 in this case you scan all table. Instead of it try to use EXISTS like this to find just one record with permission>0 - it's enought:

and EXISTS(SELECT * FROM CONTENT_SECTION CS 
             WHERE CS.ContentID = tla.RecipientID
             AND 
            (CAST(dbo.PersonHasPermission_forSection(CS.SectionID, @pViewersID, 1) 
                       AS tinyint)>0)
           )

answered Nov 21, 2013 at 12:53

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DavidB Over a year ago

Thankyou, this did increase effiency slightly

valex Over a year ago

@DavidB You will be able to increase speed replacing dbo.PersonHasPermission_forSection call to subquery which returns the same value. Sure if it possible and function logic is simple.

Robert N · Accepted Answer · 2013-11-21 14:54:04Z

1

In cases when you have inefficiency, it is often true that repeated calls to a function are to blame. Sometimes there is a big efficiency gain by eliminating multiple calls to the function. One way is to use a temp table. So, that might be something to explore.

However, I want to take a step back from your specific SQL, because I think there may be an underlying problem with the data modeling and approach. Taking your situation as described:

I have a site with pages, each page may belong to several categories. Users are assigned to categories, and if a page has is assigned a category that a user belongs they can see the page.

The best data model for a situation like this is three "entity" tables (Category, User, and Page) with many-to-many relations among them; the many-to-many relations imply two additional tables for joining (UserCategory and PageCategory):

Example database structure with 5 tables

In this case, to get a listing of all pages a particular user can see, you can just use a query like this sample (which is quite efficient):

SELECT p.*
FROM UserCategory uc
  INNER JOIN PageCategory pc ON uc.CategoryID = pc.CategoryID
  INNER JOIN Page p ON pc.PageID = p.PageID
WHERE uc.UserID = @pViewersID

This is a semi-cartesian join, but that is what you want in this case. Also note that, for optimum SELECT efficiency with the given sample query, you would also want an index on UserCategory.UserID.

Finally, I eliminated the [Level] column since it seemed like you only use it for a true/false check ("greater than 0"), which is already handled by row-exists-or-not within UserCategory. But, if you did need multiple permission levels (more than just 0 or 1), note that you could also include a [Level] column in UserCategory and reference it in the sample query for minimal extra cost.

edited Nov 21, 2013 at 14:54

answered Nov 21, 2013 at 14:33

Robert N

1,1742 gold badges15 silver badges33 bronze badges

2 Comments

DavidB Over a year ago

Thanks for the detailed answer, unfortunately its a little late in the game to change any structure, but thanks for the info

Robert N Over a year ago

You're welcome... glad to help! If you like, I'd be happy to see if there's anything you can do within the structure you've got. It's just a little hard to infer much from the WHERE-clause-only. Might you be able to post your current table structure? Be sure to genericize to protect your business data security. If you could post your entire query (including FROM clause) and the implementation of PersonHasPermission_forSection, those would also be helpful.

Collectives™ on Stack Overflow

sql optimisation for inner query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related