Earlier this week I experimented, once again, with using temporary hash tables to speed up intermediate processing. Temp tables can be very useful for creating “optimizer fences” that serve to simplify queries to the point where the optimizer will consistently (more or less) come up with a good plan if not a great one. Temp heaps seem well suited to this purpose, when used simply as low-overhead FIFO structures.
Update: I have much more to report about the project, but I have to make choices between writing about it and developing, and right now the development work is taking precedence. Look for a new post soon. I hope. If this work sounds like something you might want to contribute to financially in a small way, please let me know that you are interested.
Update (3/28/17): As a simple workaround, I added a string split UDF that does the same thing (only much more slowly). If I need to compile the DB project, I replace the intrinsic function with the UDF in the two places it is called. Otherwise I switch back to the … Continue reading →
119 SQL Code Smells.
Why Local Variables are Bad for Tuning Stored ProceduresBrent Ozar Unlimited®.
Cursor-Killing: Retrieving Recently Modified Data – SQLServerCentral.
TSQL User-Defined Functions: Ten Questions You Were Too Shy To Ask.
The LookupCTE trick described here is pretty slick. These days I find myself working with large databases where these things really matter and I found this post quite interesting. Improving Query Performance by using correct Search Arguments – SQLServerCentral.
Clever. I don’t think I had ever heard of EXCEPT. Using EXCEPT to Determine Row Differences – SQLServerCentral.
Here we go with developers again. I wonder if the ones that go around creating this particular kind of mess are also the ones that tend to flunk the index/performance quiz? Fixing Cache Bloat Problems With Guide Plans and Forced Parameterization.