My tests showed that such an implementation is faster in cases where the list of values varies a lot.
The main drawback of this solution is that the number of rows in the orderStatusIDListMembers table grows exponentially with each status added. If there are too many statuses, the table will contain huge number of rows and its maintenance may become a problem. This solution is clearly not applicable to the “selected mail deletion” problem.
Conclusion
The implementation using a table function clearly doesn’t perform as well as the other two solutions for the test cases included in the script. An implementation based on a pre-populated list of values members table was the fastest. However, implementing this solution may be overkill when there is a low variety of list values or there is a low frequency of the query. As usual, it is up to you to test each possible solution and apply one that suits your query pattern the best.
]]>