How It Works: XEvent String Comparison Performance Tip

Moved from: bobsql.com

I​​ ran across​​ a​​ check in the​​ XEvent​​ string comparison logic​​ which​​ can​​ be used to improve performance of the​​ ‘like’ comparison.

At first glance the string comparison seems straightforward. ​​ However, the logic​​ may require complex comparison​​ (SQL Server collations and/or​​ surrogate​​ characters​​ involved in UNICODE, UTF_16, etc.)

When performing comparison,​​ simple checks are attempted first. ​​ For example:

• If the compare value is ‘Bob%’ the logic​​ may​​ compare the first 3 characters and can avoid a more involved check.

• If the compare value is ‘%Bob’ the logic may compare the last 3 characters and avoid a more involved check

However, a compare value of ‘%Bob%’ may require more work than simply scanning for ‘Bob’ anywhere in the string. ​​ Values padded with pre or post spaces may require more work, etc.

The performance tip that is hidden in the implementation is​​ based on​​ the length of the compare value. ​​ When the length is greater than 28 characters a complex comparison requires memory allocation. ​​ Doing an additional memory allocation elongates the​​ comparison.

Keeping your comparison pattern less than 28 characters can avoid the additional memory allocation, improving performance.

Note:​​ The XEvent team and I are looking​​ at additional optimizations that allow the pattern to be greater than 28 bytes, without requiring the additional memory allocations.