使用SQL排序规则影响性能 [英] Affecting performance with SQL Collation

问题描述

我只想检查几件事:

Q1)Latin1_General_CI_AS不区分大小写，区分口音: SQL会将以下内容视为相等-"hello"和"HELLO"

Q1) Latin1_General_CI_AS is Case Insensitive, Accent Sensitive : I.e. SQL will see the following as equal - "hello" and "HELLO"

我经常使用LINQ安静:

With LINQ I quiet often do:

db.Where(v => v.Email == "some email".ToLower())

Q2)假设我对Q1的理解是正确的，我是否只是在浪费查询时间调用ToLower()的处理时间?

Q2) Assuming my understanding to Q1 is correct, am I just wasting processing time calling ToLower() in my queries?

Q3)有人知道使用Latin1_General_bin而不是Latin1_General_CI_AS是否会提高性能?也就是说，已经在博客等上进行了性能测试(我在写帖子时已经想到了这一点，所以还没看过我自己)

Q3) Does anybody know if there would be a performance improvement in using Latin1_General_bin over Latin1_General_CI_AS? I.e have there already been performance tests done on a blog etc (thought of this as I was writing the post, so not looked my self yet)

推荐答案

通常，SQL比较不区分大小写.
但是也有例外在MySQL中，如果使用binary varchar，比较将区分大小写.

In general SQL comparisons are case insensitive.
However there are exceptions, e.g. in MySQL if you use a binary varchar comparisons will be case sensitive.

因此您的ToLower可能不会完全浪费时间.

So your ToLower might not be a complete waste of time.

Latin1_General_bin区分大小写.
而Latin1_General_CI_AS不是.

The Latin1_General_bin is case sensitive.
Whereas the Latin1_General_CI_AS is not.

区分大小写的比较在数据库中将更快，但是如果您想将某些电子邮件"与某些电子邮件"匹配，则必须付出一定的代价，您必须将其转换为小写字母，从而失去所有的速度提升.
我没有计时，但我认为这不值得麻烦.
我建议在进行微优化之前明智地使用索引和查询.

Case sensitive comparison will be faster in the database, but you pay a price if you want to match "some email" to "Some email" you will have to cast to lowercase, losing all that speed gain.
I haven't timed it but I don't think it is worth the hassle.
I recommend smart use of indexes and queries before this micro-optimizations.

-过早的优化是万恶之源，Donald Knuth.

这篇关于使用SQL排序规则影响性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！