Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

C# strings silently kill your SQL Server indexes in Dapper

consultwithgriff.com

Post reply on HN
Print view View original

71–80 of 131 posts

Re: C# strings silently kill your SQL Server indexes in Dapper

#71

Post by pllbnk »

_vertigo wrote: post #61
diath wrote: post #36

It's weird that the article does not show any benchmarks but crappy descriptions like "milliseconds to microseconds" and "tens of thousands to single digits". This is the kind of vague performance description LLMs like to give when you ask them about performance differences between solutions and don't explicitly ask for a benchmark suite.

> No schema changes. No new indexes. No query rewrites. Just telling Dapper the correct parameter type.

Are we automatically discarding everything that might or might not have been written or assisted by an LLM? I get it when the articles are the type of meaningless self improvement or similar kind of word soup. However, if hypothetically an author uses LLM assistance to improve their styling to their liking, I see nothing wrong with that as long as the core message stands out.

Re: C# strings silently kill your SQL Server indexes in Dapper

#72

Post by munch117 »

wvenable wrote: post #57
SigmundA wrote: post #41

Earlier quoted context omitted.

Can you give an example whats dangerous about converting a nvarchar with only ascii (0-127) then using the index otherwise fallback to a scan? If we simply went to UTF-8 collation using varchar then this wouldn't be an issue either, which is why you would use varchar in 2026, best of both worlds so to speak.

For a literal/parameter that happens to be ASCII, a person might know it would fit in varchar, but the optimizer has to choose a plan that stays correct in the general case, not just for that one runtime value. By telling SQL server the parameter is a nvarchar value, you're the one telling it that might not be ASCII.

Making a plan that works for the general case, but is also efficient, is rather trivial. Here's pseudocode from spending two minutes on the problem:

    # INPUT: lookfor: unicode
    var lower, upper: ascii
    lower = ascii_lower_bound(lookfor)
    upper = ascii_upper_bound(lookfor)
    for candidate:ascii in index_lookup(lower .. upper):
        if expensive_correct_compare_equal(candidate.field, lookfor):
            yield candidate
The magic is to have functions ascii_lower_bound and ascii_upper_bound, that compute an ASCII string such that all ASCII strings that compare smaller (greater) cannot be equal to the input. Those functions are not hard to write. Although you might have to implement versions for each supported locale-dependent text comparison algorithm, but still, not a big deal.

Worst case, 'lower' and 'upper' span the whole table - could happen if you have some really gnarly string comparison rules to deal with. But then you're no worse off than before. And most of the time you'll have lower==upper and excellent performance.

Re: C# strings silently kill your SQL Server indexes in Dapper

#73

Post by exceptione »

gfody wrote: post #56
dspillett wrote: post #26

Earlier quoted context omitted.

> I'm not sure why anyone would choose varchar for a column in 2026 The same string takes roughly half the storage space, meaning more rows per page and therefore a smaller working set needed in memory for the same queries and less IO. Also, any indexes on those columns will also be similarly smaller. So if you are storing things that you know won't break out of the standard ASCII set⁰, stick with [VAR]CHARs¹, otherw…

utf16 is more efficient if you have non-english text, utf8 wastes space with long escape sequences. but the real reason to always use nvarchar is that it remains sargeable when varchar parameters are implicitly cast to nvarchar.

What do you mean with non-english text? I don't think "Ä" will be more efficient in utf16 than in utf8. Or do you mean utf16 wins in cases of non-latin scripts with variable width? I always had the impression that utf8 wins on the vast majority of symbols, and that in case of very complex variable width char sets it depends on the wideness if utf16 can accommodate it. On a tangent, I wonder if emoji's would fit that bill too..

Re: C# strings silently kill your SQL Server indexes in Dapper

#74

Post by downsplat »

Did this post come out of a freezer from 1998? Who on earth creates databases in Latin1 in 2026?

Nevermind, looks like Sql Server didn't add utf8 collations until 2019 (!) and for decades people had to choose column by column between the 16-bit overhead of "nvarchar" and latin1... And still do if they want a bit of backwards compatibility. Amazing.

Re: C# strings silently kill your SQL Server indexes in Dapper

#75

Post by tialaramex »

gfody wrote: post #56
dspillett wrote: post #26

Earlier quoted context omitted.

> I'm not sure why anyone would choose varchar for a column in 2026 The same string takes roughly half the storage space, meaning more rows per page and therefore a smaller working set needed in memory for the same queries and less IO. Also, any indexes on those columns will also be similarly smaller. So if you are storing things that you know won't break out of the standard ASCII set⁰, stick with [VAR]CHARs¹, otherw…

utf16 is more efficient if you have non-english text, utf8 wastes space with long escape sequences. but the real reason to always use nvarchar is that it remains sargeable when varchar parameters are implicitly cast to nvarchar.

UTF-16 is maybe better if your text is mostly made of codepoints which need 3 UTF-8 code units but only one (thus 2 bytes) UTF-16 code unit. This is extremely rare for general text and so you definitely shouldn't begin by assuming UTF-16 is a good choice without having collected actual data.

Re: C# strings silently kill your SQL Server indexes in Dapper

#76

Post by Tuna-Fish »

exceptione wrote: post #73
gfody wrote: post #56

Earlier quoted context omitted.

utf16 is more efficient if you have non-english text, utf8 wastes space with long escape sequences. but the real reason to always use nvarchar is that it remains sargeable when varchar parameters are implicitly cast to nvarchar.

What do you mean with non-english text? I don't think "Ä" will be more efficient in utf16 than in utf8. Or do you mean utf16 wins in cases of non-latin scripts with variable width? I always had the impression that utf8 wins on the vast majority of symbols, and that in case of very complex variable width char sets it depends on the wideness if utf16 can accommodate it. On a tangent, I wonder if emoji's would fit that…

Japanese, Chinese, Korean and Indic scripts are mostly 2 bytes per character on UTF-16 and mostly 3 bytes per character in UTF-8.

Re: C# strings silently kill your SQL Server indexes in Dapper

#77

Post by elmigranto »

Third party dependencies are very easy: you just have to intimately know how it is implemented in addition to knowing your own code and stack, and then you are golden!

Nothing to learn, just focus on making your app, it’s all taken care of by This One Simple Package ;)

These things are so far from free as our tooling presents with “just nuget it or whatever”.

Re: C# strings silently kill your SQL Server indexes in Dapper

#78

Post by nmeofthestate »

diath wrote: post #36

It's weird that the article does not show any benchmarks but crappy descriptions like "milliseconds to microseconds" and "tens of thousands to single digits". This is the kind of vague performance description LLMs like to give when you ask them about performance differences between solutions and don't explicitly ask for a benchmark suite.

The writing style certainly screams LLM.

Re: C# strings silently kill your SQL Server indexes in Dapper

#80

Post by divingdragon »

Tuna-Fish wrote: post #76
exceptione wrote: post #73

Earlier quoted context omitted.

What do you mean with non-english text? I don't think "Ä" will be more efficient in utf16 than in utf8. Or do you mean utf16 wins in cases of non-latin scripts with variable width? I always had the impression that utf8 wins on the vast majority of symbols, and that in case of very complex variable width char sets it depends on the wideness if utf16 can accommodate it. On a tangent, I wonder if emoji's would fit that…

Japanese, Chinese, Korean and Indic scripts are mostly 2 bytes per character on UTF-16 and mostly 3 bytes per character in UTF-8.

Really, as an East Asian language user the rest of the comments here make me want to scream.
Post reply on HN