Select Based on List of Random Values — Dynamic SQL or CSV Parsing UDF?

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.

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |