Sql查询字符串操作的性能？ [英] Sql Query performance for string operations?

问题描述

我想在自连接条件操作中剥离最后4个字符的varchar（255）字符串。

I want to strip last 4 char of a varchar(255) string in a self join condition operation. which of these string operation is faster.

Example: link field 

1, '100-200-300-'
2, '100-200-400-'
3, '100-200-300-400-'

1. left(a.link, len(a.link)-4) = left(b.link, len(b.link)-4)
// OR 
2. substring(reverse(a.link), 4, 255) = substring(reverse(b.link), 4, 255)

应该使用一些其他方式，如果我考虑性能？

or I should use some other way, if I consider performance?

推荐答案

如果你真的关心性能，那么你使用一个适当的索引。创建一个计算持久列，对其进行索引并加入：

If you really care about performance then you use a proper index. Create a computed persisted column, index it and join on it:

alter table tablename add sublink =  left(a.link, len(a.link)-4) persisted;
create index indexname on tablename(sublink);

...

select...
from...
join... on a.sublink = b.sublink;

这只是一个例子。在实际情况下，索引的正确形状和组成将必须被适当地分析（例如，决定什么其他列需要作为键，包括或过滤索引）。它的好处woudl必须与增加的更新成本进行平衡。

This is just an example. In a real case, the correct shape and composition of index would have to be properly analyzed (eg. decide what other columns are needed as keys, include or filter on the index). Its benefits woudl have to be balanced against the update costs added.

设计索引< a>是开始的好地方。

Designing Indexes is a good place to start.

这篇关于Sql查询字符串操作的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！