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.
- User logs in
- system deletes all entities for the user in the table
- system inserts all the user’s entities then
- 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.