We recently updated our DataWarehouse (a MS SQL 2000 database) to include a new table to control the level of access users had to information in all of the other tables. Without going into too much detail, the new table has a user ID, and a list of account IDs that they can access. All of our tables within the DataWarehouse have had a corresponding view created, and we've asked our users to use these views to access the data (and thus limits their view based on the level of access in the initial Access Control table).
For complex queries which use many of these views, we obviously have a problem whereby the same access control table is joined on many times. We can't do a lot about that at the moment as there are many queries we don't have control over accessing this resource. We need therefore to make any changes we can on the box itself to optimise the speed of access.
The Datawarehouse is only updated overnight, and to be honest the time this takes is irrelevant - insert speed is not required, only select. We can also rebuild the indexes if necassary.
The problem we have is, despite having an index on this non-unique record (the UserID column), when doing execution plan traces we see that Table Scans are used instead of Index Seek, which I understand is basically ignoring the index. This is leading to horrific performance implications - a query that last week took say a minute to execute can now take 10, and some are pushing an hour.
All of the other views that now reference this table join onto the non-indexed column (the account ID), and then the number of accounts returned are filtered out based on the NT ID of the user.
Does anyone have any suggestions on what we can do to improve the performance? Either in the short term (things we can change on the infrastructure side), or longer term (changes to the database schema, we can't do this easily though given the nature of how the database is used).
Thanks!
David
Unfortunately you don't mention what reporting tool you're using (I have the impression that users write their own queries?) or what volume of data you have, but two longer-term enhancements would be: