問題描述
背景.我正在使用 SQL Server.我在數據庫中有兩個表:
Background. I'm using SQL Server. I have two tables in database:
Vendors(Id, Name, Description)
Products(Id, VendorId, Name, Description)
Id 列中的值使用 Vendor 表中的前綴 'ID_' 進行格式化.
Values in Id column are formatted with prefix 'ID_' in Vendor table.
VendorId 列中的值使用 Products 表中的前綴 'VE_' 進行格式化.
Values in VendorId column are formatted with prefix 'VE_' in Products table.
例如 Products中的'VE_001245'是指Vendors中的'ID_001245'>.
(請不要提議改變這個概念，不關心數據庫方案，不建議添加外鍵.只是為了說明.)
(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.)
問題:以下哪個查詢在性能方面最好，為什么?
Question: which one of following queries is best in performance context and why?
在內部
select中使用replace函數:
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函數:
select v.* from Vendors v
inner join
(
select distinct VendorId as Id
from Products
) list
on v.Id = replace(list.Id, 'VE_', 'ID_')
編輯.每個表中只有聚集索引(按Id列).每個表可以包含數百萬行.
Edit. There is only clustered index in each table (by Id column). Each table can contains millions rows.
推薦答案
兩個查詢在性能方面幾乎相同.在第一個查詢中，排序 進行了兩次，一次是在選擇不同記錄時，一次是在執行內部聯接時，最后是 合并聯接選擇最終結果集.而在第二個查詢中，排序只完成一次，但正在執行 Hash join，這比合并連接更昂貴.因此，在表上沒有任何索引的情況下，這兩個查詢在性能方面是相同的.
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.
這篇關于TSQL:在 select with join 中使用替換函數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！