用文本更新varchar列的最快方法 [英] fastest way to update varchar column with text
问题描述
我在列中有以下内容:
元数据服务-------------MindWorks.AccountsMindWorks.TransactionsMindWorks.Commissions...我需要将这些列更新为以下形式:
MindWorks.Client.Accounts我最初想使用字符串函数来更新它们(LEFT、SUBSTR 等)，但是否有替代或更好的方法来更新列中的文本以插入文本?
我使用的是 SQL Server 2008，无法使用 CLR 集成.
这是一个关于最快"的问题，所以下面提供了时间
测试设置，具有 > 100 万行的表
create table MetaDataServe (id int identity primary key, vc varchar(max));插入 MetaDataServe 值('MindWorks.Accounts'),('MindWorks.Transactions'),('MindWorks.Commissions');插入元数据服务选择vc来自 MetaDataServe，master..spt_values a，master..spt_values b其中 b.number 介于 1 和 30 之间--(1090110 行受影响)东西 vs 替换 vs 子字符串
绩效总结 - STUFF >子串>替换
update MetaDataServe set vc = STUFF(vc, 9, 0, '.Client')<块引用>
SQL Server 解析和编译时间:
CPU 时间 = 0 毫秒，经过时间 = 3 毫秒.
表元数据服务".扫描计数 1，逻辑读 55402，物理读 0，预读 0，lob 逻辑读 0，lob 物理读 0，lob 预读 0.
SQL Server 执行时间:
CPU 时间 = 10094 毫秒，已用时间 = 10808 毫秒.
CPU 时间 = 10250 毫秒，经过时间 = 10896 毫秒.
(2次来自多次执行以显示可变性，非常低，因此可以认为时间在3％以内)
update MetaDataServe set vc = REPLACE(vc, '.', '.Client.')<块引用>
SQL Server 解析和编译时间:
CPU 时间 = 3 毫秒，经过时间 = 3 毫秒.
表元数据服务".扫描计数 1，逻辑读 55402，物理读 0，预读 159，lob 逻辑读 0，lob 物理读 0，lob 预读 0.
SQL Server 执行时间:
CPU 时间 = 20469 毫秒，已用时间 = 21238 毫秒.
update MetaDataServe set vc = 'MindWorks.Client.'+ SUBSTRING(vc, 11, 100)<块引用>
SQL Server 解析和编译时间:
CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.
表元数据服务".扫描计数 1，逻辑读 55402，物理读 0，预读 3，lob 逻辑读 0，lob 物理读 0，lob 预读 0.
SQL Server 执行时间:
CPU 时间 = 11219 毫秒，已用时间 = 12030 毫秒.
CPU 时间 = 11531 毫秒，经过时间 = 12148 毫秒.
固定数字 vs PATINDEX vs CHARINDEX
(固定位置版本上面已经给出)
性能总结 - 已修复 >(PATINDEX = CHARINDEX)
PATINDEX 和 CHARINDEX 之间似乎没有实质性区别
update MetaDataServe set vc = STUFF(vc, PATINDEX('%.%',vc), 0, '.Client')<块引用>
SQL Server 解析和编译时间:
CPU 时间 = 0 毫秒，经过时间 = 2 毫秒.
表元数据服务".扫描计数 1，逻辑读 55400，物理读 0，预读 0，lob 逻辑读 0，lob 物理读 0，lob 预读 0.
SQL Server 执行时间:
CPU 时间 = 15218 毫秒，经过时间 = 16167 毫秒.
update MetaDataServe set vc = STUFF(vc, CHARINDEX('.',vc), 0, '.Client')<块引用>
SQL Server 解析和编译时间:
CPU 时间 = 0 毫秒，经过时间 = 3 毫秒.
表元数据服务".扫描计数 1，逻辑读 55402，物理读 0，预读 0，lob 逻辑读 0，lob 物理读 0，lob 预读 0.
SQL Server 执行时间:
CPU 时间 = 15469 毫秒，经过时间 = 16353 毫秒.
注意事项:
- 根据您的需要，上面给出的所有更新语句都将起作用(进行一两次调整)
- 在每次测试之前，整个表都会被删除并重新创建以防止出现缓存问题
注意！
即使 STUFF 速度更快，您也可能会遇到棘手的情况.如果您的数据包含
MindWorksNoDot"然后你更新使用
update MetaDataServe set vc = STUFF(vc, CHARINDEX('.',vc), 0, '.Client')你最终得到 NULL！因为当 CHARINDEX 找不到点时，零 (0) 的 STUFF 的第二个参数会导致整个字符串变为 NULL.
最后的话
为了安全和可靠性，考虑到它只比 STUFF 方法慢 33%，我会简单地使用 REPLACE 语句，即
update MetaDataServe set vc = REPLACE(vc, '.', '.Client.')I have the following in a column:
MetaDataServe
-------------
MindWorks.Accounts
MindWorks.Transactions
MindWorks.Commissions
...
I need to update those columns to be in the form of:
MindWorks.Client.Accounts
I initially thought of using the string functions to update them (LEFT, SUBSTR etc), but is there an alternative or better method of updating text in a column to insert text?
I am using SQL Server 2008 and can't use CLR integration.
This is a question about "fastest", so timings are provided below
Test setup, table with >1 million rows
create table MetaDataServe (id int identity primary key, vc varchar(max));
insert MetaDataServe values
('MindWorks.Accounts'),
('MindWorks.Transactions'),
('MindWorks.Commissions');
insert MetaDataServe
select vc
from MetaDataServe, master..spt_values a, master..spt_values b
where b.number between 1 and 30
-- (1090110 row(s) affected)
Stuff vs Replace vs SubString
Performance Summary - STUFF > SUBSTRING > REPLACE
update MetaDataServe set vc = STUFF(vc, 9, 0, '.Client')
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
Table 'MetaDataServe'. Scan count 1, logical reads 55402, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 10094 ms, elapsed time = 10808 ms.
CPU time = 10250 ms, elapsed time = 10896 ms.
(the 2 times are from multiple executions to show the variability, it is quite low so the times can be considered accurate to within 3%)
update MetaDataServe set vc = REPLACE(vc, '.', '.Client.')
SQL Server parse and compile time:
CPU time = 3 ms, elapsed time = 3 ms.
Table 'MetaDataServe'. Scan count 1, logical reads 55402, physical reads 0, read-ahead reads 159, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 20469 ms, elapsed time = 21238 ms.
update MetaDataServe set vc = 'MindWorks.Client.' + SUBSTRING(vc, 11, 100)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'MetaDataServe'. Scan count 1, logical reads 55402, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 11219 ms, elapsed time = 12030 ms.
CPU time = 11531 ms, elapsed time = 12148 ms.
Fixed number vs PATINDEX vs CHARINDEX
(The fixed position version is already given above)
Performance Summary - FIXED > (PATINDEX = CHARINDEX)
There appears to be no material difference between PATINDEX and CHARINDEX
update MetaDataServe set vc = STUFF(vc, PATINDEX('%.%',vc), 0, '.Client')
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'MetaDataServe'. Scan count 1, logical reads 55400, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15218 ms, elapsed time = 16167 ms.
update MetaDataServe set vc = STUFF(vc, CHARINDEX('.',vc), 0, '.Client')
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
Table 'MetaDataServe'. Scan count 1, logical reads 55402, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15469 ms, elapsed time = 16353 ms.
Notes:
- All update statements given above will work (with a tweak or two) depending on your needs
- Before each test, the entire table is dropped and recreated to prevent caching issues
CAUTION !
Even though STUFF is faster, you can get into tricky situations. If your data contains
"MindWorksNoDot"
And you update using
update MetaDataServe set vc = STUFF(vc, CHARINDEX('.',vc), 0, '.Client')
You end up with NULL! Because when CHARINDEX cannot find the dot, the second parameter to STUFF of zero (0) causes the entire string to go to NULL.
FINAL WORDS
For safety and reliability, given it is only 33% slower than the STUFF approach, I would simply use a REPLACE statement, i.e.
update MetaDataServe set vc = REPLACE(vc, '.', '.Client.')
这篇关于用文本更新varchar列的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
