As I've mentioned previously the application I have inherited has a very screwy database design. Today though I discovered a wtf that actually manages to span two databases.
To understand whats going on you first have to know a little about how the application is setup.
We have three servers, a webserver which hosts the web app, a database server (DB1) which sits in the same rack as the webserver and services the web app (ie it stores users accounts and tool permission). Finally we have a second database server (DB2) which operates as the primary datasource for client data.
Now when a user logs in they get all their permissions from DB1, however there is one tool which is only available to users if a certain type of record (specialReport) in DB2 is associated with the user account.
The way this record is maintained is that the users table is replicated from DB1 to DB2. Then an association table links the user record on DB2 to the specialReport record. This user table replication by itself is a major wtf, but its not the star of the show. The star is the way the webapp handles checking if a user has this special report is this.
On DB2 there is a stored procedure which returns a two column query userid, specialreportid. specialreportid is a unique val but not relevent here as we only want to know if any association exists. The webapp automatically calls this stored proc every 15 minutes and loops through the list of userId's and compares it with the user group list in DB1. If any user is not in the group on db1 the new userid gets added to the group. The group access is then checked at login.
The reason I discovered this was that I kept seeing spikes in memory usage every 15 minutes that I could not attribute to normal user activity.
When I checked the stored proc I discovered that it was returning 34,970 records each time it was being called.
- 24hrs * 4 (every 15 minutes) * 34970 records = 3,357,120 records pulled every day. While not enough to bring down the web server by itself it certainly adds to the many other performance issues.
Further checking showed that the majority of these where duplicates. Adding a "distinct" to the stored proc query reduced the records returned from 34,970 to 975.
- So now we are down to 24 * 4 * 975 = 93,600 records pulled each day
You'd think great, except that we only have on average 600 users logging in each day, and the majority of them dont even have this specialReport.
So I pull out this entire code block and add a single query to DB2 for report access by userid to the login code so now we only have a maximum 600 aprox records returned from the database.
If I tried I could not have come up with a more inneficient way to handle this. The real beauty of this is that it the original developer claims to be a former dba. Now I understand the "former" part.