It’s happened once again. Another application where users don’t know what they want, so they try to use a relational OLTP database like a data warehouse. The first time I ever saw this was a manager who had an Access front end to a SQL Server database, using attached tables. That wasn’t really the bad part. The problems all started when he created a view that joined every table in the database. The way he would use this view was to open it, and then apply filters in the Access result set to seek the data he was currently interested in finding.
What do you think happened? You already know. Whenever he opened this view it locked down the database, and nobody else could get any work done.
Today I experienced the same mindset once again. Professional developers did not push back on the design of a system, and created a tool that would dynamically build a query, based on the filters the end user requested. It didn’t always join every table. It only included the tables necessary to implement the desired filters from the users.
What do you think happened? Because the tool allowed users to specify so much data for filtering, it ended up designing queries joining 20 or thirty tables. Since it was entity framework, each additional item in the results expanded the volume of data that was going to be returned. Query times were awesome sometimes. Other times they took a minute or more, and locked a great portion of the database resulting in blocking and deadlocks.
Ouch, this hurts. I know, let’s throw an indexed view at the problem. Now the dominos start falling. Keeping the
view up to date when data changes is now causing other problems. You are now running that nasty query every time you modify a record used in that view…but your query filter is much faster.
Time to find out more about what the user needs to accomplish and establish less painful methods to get it done. Otherwise, you’re going to lose your users, because they won’t like the sketchy performance that occurs frequently.
Cheers,
Ben