Is this a quarterly report because that is how long it takes to run?
It takes about 7 seconds to run. I suppose if I optimized it I could save a minute every couple of years.
I usually get concerned about performance issues when they're actually causing problems. I'm generally more concerned about how long it takes to write queries, and how cumbersome the SQL involved is. And since arrays are relatively new to me, I've been trying to understand generally the best ways to query information out of them, or when their behavior just doesn't make sense to me. I'll say I answer 99.99% of my own questions before they ever make it to the list, and by the time they do I invariably have read the documentation as best as I can. This has been my favorite list ever to read, as people are invariably helpful, patient and polite to each other.
I would suggest considering how to use functions to encapsulate some of the "medical code collecting" logic. And consider WITH/CTE constructs as well, like I used in my last message, to effectively create temporary named tables for different parts of the query.
Might want to move the whole thing into function and pass in the various
parameters - namely the date range - instead of hard-coding the values into the view.
Thanks for these constructive suggestions. I see benefits both ways. And the dates are actually parsed in by an app at run-time. (I stripped that part out to avoid confusion--I find it hard to know when submitting a list item how much to just dump a full real example, and how much to simplify down to a test case that illustrates the specific issue.)
On a smaller scale I've written queries like this. I enrolled in a
university database design course shortly thereafter...
until someone more knowledgeable (like a future you probably) comes back and
I'm not sure what the point of either of these comments were, but perhaps they made you feel better. Either way, thanks for taking the time to look my stuff over and for the other comments and explanations you made.
Ken
