Sql Query performance for string operations?
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.lin开发者_如何学Ck)-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 performance is important, you put that substring into its own field and index it.
Comparing your options the left(len())
variant will almost certainly be faster than the substring(reverse())
variant since reverse()
has to create a new string from the original one and will be slower than just getting the length.
What about right(a.link, 4)
instead?
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;
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.
Designing Indexes is a good place to start.
精彩评论