The race with Time for Efficiency occur every day. Time is always a moving target and the prize of Efficiency can be hard to reach. While being a marathon runner, Time moves at a steady pace but seems to gain speed when interruptions slow us down or when things that should be working are not.
Some projects you need to get done by a date and it may be a tight race to that finish line. So you take the clear path in front of you that you know will allow you to meet the deadline. After the project is complete, if you have the time, you get to go back and make it more efficient. Of course, having that time can be a bit of a luxury.
Suppose you have 20 somewhat related specifications in front of you that make up the project. The clear path is to code up a stored procedure for each specification. Some number of specifications/stored procedures may require pulling queries and responses. Another bunch may look at orders. Yet another may look at medications. Others may look at all of those details. So the clear path is that every stored procedure pulls a base population of patients and then pulls the details needed by spec. In each of the 20 stored procedures you select that base population and join accordingly to pull the data needed for that specification. This clear path may take you to 1 table or set of tables to grab the base population in every stored procedure. And then for the details you may be going to the same set of tables for the queries, the same set of tables for orders, the same set for medications. That works but is fairly inefficient. When you go grocery shopping do you go into the store and buy your vegetables and bring them back to the car? Then go back in the store to pick up meats from the meat department and bring those back to the car? Then to the dairy department? I doubt it.
Looking at the bigger picture you can see the link between the 20 specs is that the base population of patients is essentially the same. There can be a few differences here and there amongst the 20 specs with regards to the base patients. This means that you want to pull those details about the patients so that you can filter properly for the individual reports. Perhaps one spec wants admitted patients within a date range, others want discharged patients within a date range.
The more efficient path is to compile the base population up front with all the information to allow filtering for the 20 specs and store that in a local table. Then in each of the 20 stored procedures select from that base population (filtering appropriately) and join accordingly to pull the data needed for that specification. Adding to the efficiency is you can pull all the queries from all the specs for all the patients in the base population at one time and put it in a local table. You can pull all the details needed for all the specs for all the patients in your base population and put them in local tables. You can put a non-clustered index on that table to speed things up when you start pulling for the various reports. Each store procedure then gets it base population filtering as needed, joins to the local table of details needed by the spec and produces the results you need.
Beyond the increased efficiency of the SQL code a greater asset in winning the battle against time (to develop that SQL) is a second set of eyes. How long do you spend looking at code that should work but does not? You have looked at the code and it is exactly what you need but when you run the code it does not work as you expect. Sometimes that second set of eyes can see it right away and put you ahead of time. Could it be that you joined between table A and Table B but in the join you have A.TransactionProcedureID = A.TransactionProcedureID? Your eyes and your mind don’t allow you to see that you’re not referencing B. You are looking for a query with a response of ‘THEONENEEDED’ but you have an extra space at the front of it ‘ THEONENEEDED’. You just aren’t seeing it. You have a case statement but you forgot the ELSE clause. Then when you try to roll it up from the #Temp table, you are rolling up a NULL rather than a 0.
It is something simple but you just cannot see it without a second pair of eyes and that allows Time to get ahead of you.
By Geoff Grouten at 22 Apr 2016, 14:54 PM