Hey Brent for that last question there could be a "driver" difference in connection flags. I've had ARITHABORT cause significant slowness when set to false. Some of the drivers out there set it to false as a default, unlike what SSMS defaults, for instance. Thanks for what you do for us! Always learn something...
Ya, I was thinking of slow in the application, fast in ssms and going “the set options being different between the drivers would be the first thing I would check”
For the last question: jtds odbc is an pure java driver. I'd guess that the MS odbc driver is writte in C++. That might by the reason for the performance improvement.
At least these "presentations" many times make people feel shame, I'm guessing those stick with them, so you are kind of using the teaching method already 🤔. And give the rest of us a few chuckles along the way 😁
There is in fact a performance benefit to using N'..' Unicode strings in WHERE clauses. I've seen many, many cases where comparing a Unicode column to a non-Unicode string (or even another non-Unicode column) causes SQL Server not to use the available index on the Unicode column.
@@BrentOzarUnlimited So my original reply was backwards. The problem occurs with VARCHAR columns and Unicode strings in WHERE clauses, where SQL Server decides to do an implicit_convert on the column to NVARCHAR instead of the constant to VARCHAR, thus reading every row and getting an index/table scan instead of an index seek. Sent some screenshots as requested.
@@brandonwiese4615right, you had it backwards. The scenario we discussed on the video, the N doesn’t matter, as I explained there. But glad you learned something about implicit conversions!
