2 ms·

I once had a Bug in SQL Server, where a SUBSTRING(field, 1, 255) in a varchar field that was 255 in size improved the query performance about 1.5 times. We nev

I once had a Bug in SQL Server, where a SUBSTRING(field, 1, 255) in a varchar field that was 255 in size improved the query performance about 1.5 times. We never found out, what the problem was, but we traced it down to the SUBSTRING - removing it made the query significantly SLOWER. That was weird.

langcss 2y ago
Did you check the execution plans before and after? Probably caused a different plan that happened to be faster by luck.
- sandreas 2y ago
  It was long time ago when I was an intern. I had no clue of Nothing back then... :)
  - langcss 2y ago
    Thanks, I only knew about execution plans after doing a DB course, so understood!
orf 2y ago
Seems likely that the substring gives a hint to the optimizer/executor about the size of the field, otherwise it could be whatever the max varchar size is. There are likely specialisations for text fields under 256 bytes (and more generally, all common fixed-sized subsets of variable-length fields) which are made available when substr is used. For example, these values could be stack allocated rather than read from the heap.