Large Data Operations in SQL Server

Large Data Operations in SQL Server

This article explains how performance is affected by choosing "table scan" or "index seek". Also it shows how memory available for SQL Server affects performance.

MS SQL Server 2000.

Interview questions

1) What do you know about this project?
2) What states did you work in?
3) What did you do in your project?
4) What did you complete in your project?
5) How would you create report page?
6) What interesting SP did you write?
7) How would you select 50 states with CA on top?
8) How would you get all records from table A which don’t have matches in table B?

Bayden Systems - IEToys

Bayden Systems - IEToys
Tools for Internet Explorer:
- Image cleanup.
- Define & Dictionary.
- Partual HTML source.
- Word Highlighter.
- Access keys.

Fiddler HTTP Debugger

If you want to trace what HTTP get/post requests your browser prepares, and trace HTML response which web server returns to your browser --- use Fiddler HTTP Debugger

Microsoft Professional Developers Conference 2005

Microsoft Professional Developers Conference 2005

How to deal with users/roles/permissions

Typical business application with many clients works with many users. These users are linked to groups. These groups could be linked to other groups. There could be recursion in groups' hierarchy.
Every group has permission to access some business components.

Typical task in the application would be to figure out if specified user has right to access specified business component. Let's refer this task as "access question".
We want to answer this "access question" as fast as possible, because "access question" would be asked every time when user accesses a component.

One of the simplest approaches would be to implement standart table construction like:
User - Role - Permission.
But that may be not flexible enough from business perspective.
Example: Sometimes business wants to add user JohnDoe, who is employee of company Company1 to role Company1Role.
In turn Company1Role belongs to ReportViewersRole.
And ReportViewersRole has permission to access "Reports" components.
This example would not fit into simple "User - Role - Permission" model.

So, what's the solution?

The two approaches below look more viable than others:

Approach #1. Limited hierarchy of roles.

Most probably this approach would have short chain of links:
User <-> User roles <-> Permissions roles <-> Permissions

Because of limited number of links in the chain, answering question “is this user has permission to access this component?” would be pretty fast.

"Limited hierarchy of roles" approach would cover 90+% or use cases. If this approach covers close to 100% of use cases we should use this approach, because it’s simpler than Approach #2.

Approach #2. "Permissions datawarehouse"

This approach can adopt unlimited hierarchy/recursion of roles and still work pretty fast.

The idea is to have two sets of tables.

First set would have the following tables:
Users
Roles
RolesRecursiveHierarchy
RolesPermissions
and optionally - Permissions table.

This First set is responsible for maintaining permissions hierarchy (adding/deleting users, adding/deleting roles, defining roles permissions, defining roles hierarchy).

This First set would not be responsible for answering question: "is this user has permission to access this component?". The First set is too slow for that: ~ 1 ... 10 seconds.

That why we would need second set. Exactly for answering this permission question.

The second set consists of only one table:
UserPermissions

UserPermissions table keeps pre-calculated information from the First set. UserPermissions directly links every user to corresponding set of permissions.

Every record would consist of 2 integer fields (UserId, PermissionId).

If we estimate to have ~10000 users, and estimate that average user would have ~500 permissions, then UserPermissions table would have ~5 million records.

Answering the question ("is this user has permission to access this component?") would be implemented in form of SQL query:
Exists(Select 1 from UserPermissions where UserId = @UserId and PermissionId = @PermissionId)

Because UserPermissions would be indexed --- the query would be pretty fast.

Every time when the First Set is updated, The Second set would be updated too.

Note 1:
Another implementation of The Second set could be: User_PermissionRole table.
Difference between User_PermissionRole and UserPermissions is that if UserPermissions caches chain of N links, User_PermissionRole would cache chain of (N-1) links. Last link would be implemented in C# components code.

Note 2:
We can cache "The Second set" in memory. It would be ~100 Megabyte.

Bottom line:

Approach #2 is more flexible, but more technically challenging than Approach #1.

If Approach #1 ("Limited hierarchy of roles") works good enough – we’d better use it.

If Approach #1 does not cover enough use cases --- we’d better use Approach #2 ("Permissions datawarehouse").