問題描述
背景.我正在使用 SQL Server.我在數(shù)據(jù)庫中有兩個(gè)表:
Background. I'm using SQL Server. I have two tables in database:
Vendors(Id, Name, Description)
Products(Id, VendorId, Name, Description)
Id 列中的值使用 Vendor 表中的前綴 'ID_' 進(jìn)行格式化.
Values in Id column are formatted with prefix 'ID_' in Vendor table.
VendorId 列中的值使用 Products 表中的前綴 'VE_' 進(jìn)行格式化.
Values in VendorId column are formatted with prefix 'VE_' in Products table.
例如 Products中的'VE_001245'是指Vendors中的'ID_001245'>.
(請(qǐng)不要提議改變這個(gè)概念，不關(guān)心數(shù)據(jù)庫方案，不建議添加外鍵.只是為了說明.)
(Please, do not propose to change this concept, do not care about database scheme, do not suggest adding foreign key. All it is just for illustration.)
問題:以下哪個(gè)查詢?cè)谛阅芊矫孀詈茫瑸槭裁?
Question: which one of following queries is best in performance context and why?
在內(nèi)部
select中使用replace函數(shù):
select v.* from Vendors v
inner join
(
select distinct replace(VendorId, 'VE_', 'ID_') as Id
from Products
) list
on v.Id = list.Id
在on語句中使用replace函數(shù):
select v.* from Vendors v
inner join
(
select distinct VendorId as Id
from Products
) list
on v.Id = replace(list.Id, 'VE_', 'ID_')
編輯.每個(gè)表中只有聚集索引(按Id列).每個(gè)表可以包含數(shù)百萬行.
Edit. There is only clustered index in each table (by Id column). Each table can contains millions rows.
推薦答案
兩個(gè)查詢?cè)谛阅芊矫鎺缀跸嗤?在第一個(gè)查詢中，排序 進(jìn)行了兩次，一次是在選擇不同記錄時(shí)，一次是在執(zhí)行內(nèi)部聯(lián)接時(shí)，最后是 合并聯(lián)接選擇最終結(jié)果集.而在第二個(gè)查詢中，排序只完成一次，但正在執(zhí)行 Hash join，這比合并連接更昂貴.因此，在表上沒有任何索引的情況下，這兩個(gè)查詢?cè)谛阅芊矫媸窍嗤?
Both the queries are almost same in terms of performance. In the first query sorting is done twice, once when you are selecting the distinct records and again when it is performing an inner join, and in the end a merge join is there to select the final result set. Whereas in second query sorting is done only once but Hash join is being performed which is more expensive then merge join. So both the queries are same performance wise in the scenario when you don't have any index on the table.
這篇關(guān)于TSQL:在 select with join 中使用替換函數(shù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！