Database performance for determining entities assigned to users

Question

I am interested in insight in improving performance in the following instance:

I am currently working on an application that has a hierarchical entity structure where, depending on a user’s level within the organization, they would have an assignment to specific entity at a specific level – and thereby have access to all their children (in this case buildings within districts within regions throughout the U.S.). Most of the super users will have upward of 20,000 individual entities in their portfolio, some with as many as 40,000.

Instead of going into great detail, let’s suffice to say that a good bit of logic is needed to determine all the entities a user has access to. This logic is currently handled using a table function that is used in 95% of the stored procedures. The average stored procedure takes no more than 1 - 2 seconds to run. BUT, in an ASP.Net page that makes calls to 10+ different stored procedures, the performance hit quickly snowballs into 20+ seconds.

As an alternative, we were thinking of only calling this table function once (upon log in) and storing the results in a table (after clearing out any previous values for the same user). We would then have all the stored procedures reference this new table instead of the table function. A test revealed that a page which took 15 seconds to load could render in less than 3 seconds when selecting from the new table.

For example:

1. User logs in
2. system deletes all entities for the user in the table
3. system inserts all the user’s entities then
4. system sends them on their merry way and no longer runs the table function for that current session

Our concern is that, with the potential for hundreds of users consistently logging in and out of the application, deleting from and inserting into this table so often could result in a significant performance hit itself due to row level locking. Has anyone else used a SQL Table in such a manner? If so, should we be concerned with low performance due to the constant inserting and deleting from a single table.

Answer 1

The short answer...

This has the hallmarks of solving the wrong problem with the wrong solution. It sounds like you're looking for a workaround to a fundamental design flaw, introducing an entirely new set of problems on the way.

An attempt at a longer answer...

It's almost impossible to offer any practical advice without a better understanding of the database, the application and interaction between the two. That said, there are one or two warning signs.

Logic in a table function

It sounds like you have a hierarchy of entities, which could be represented in a relational model and navigated in a set oriented fashion. If complicated logic is required in the database to navigate the hierarchy, the model is probably wrong.

Do you have a relational database or an object store/bit bucket?

Your question refers to entities, rather than tables and records. Did you build an object model and dump it to tables, rather than map your objects to a relational model?

How much data are you returning to the user?

A 2 second stored procedure call could be sub-optimal, accessing a sub-optimal model or returning too much data. Requiring 10 calls to this procedure suggests it could well be all 3. Navigating a 40k record graph is not big data territory, unless you're application is loading 40k records from the database to determine what's navigable.

You already have a better model

Your workaround may actually be the solution, if applied differently. If the output from your table function is a model which works, perhaps it should be a permanent part of the model. Instead of building this temporary table every time a user logs in, why not keep the data in that form?