問題描述
可以在這裡使用 Common Table 表達式來提高性能嗎？ (Can Common Table expressions be used here for performance?)
Can COMMON Table expressions be used to avoid having SQL Server perform the following string parsing twice per record? My guess is "no."
SELECT DISTINCT
Client_ID
,RIGHT('0000000' + RIGHT(Client_ID
,PATINDEX('%[^0‑9]%'
,REVERSE('?' + Client_ID)) ‑ 1)
,7) AS CorrectedClient
FROM
membob_vw
WHERE
Client_ID <> RIGHT('0000000' + RIGHT(Client_ID
,PATINDEX('%[^0‑9]%'
,REVERSE('?' + Client_ID)) ‑ 1)
,7)
ORDER BY
1
,2
Every time I try to format the SQL as a "Code Block" it looks good (displaying on multiple lines) until the page is refreshed, after which point the SQL is displayed , for me at least, all on ONE line‑ and I can't seem to corerct that.
Does it display that way for people that are using a browser new that IE6? My company imposes this POS browser on me and prevents me for using any other.
‑‑‑‑‑
參考解法
方法 1:
NO, a CTE will not do anything performance wise for this query. It may seem strange/inefficient to type in the same thing large string expression twice. However, SQL Server will only do the string expression one time per row, it has been optimized for things like that.
EDIT the CTE will reduce the duplicate code:
;WITH AllRows AS (
SELECT DISTINCT
Client_ID
,RIGHT('0000000' + RIGHT(Client_ID
,PATINDEX('%[^0‑9]%'
,REVERSE('?' + Client_ID)) ‑ 1)
,7) AS CorrectedClient
FROM
membob_vw
)
SELECT * FROM AllRows WHERE Client_ID<>CorrectedClient
ORDER BY
1
,2
but won't perform any better. USE SET SHOWPLAN_ALL ON and I'll bet you see the same query plan for each version.
BE CAREFUL trying to make queries look pretty and reduce redundant code fragments! simple looking SQL changes can have major adverse performance implications! always performance (run and/or query plan) check any changes you make. I have seen trivial changes made to queries that run instantly, that results in them then taking minutes to run. The key with SQL is performance not pretty code. If the application is slow, who cares if the code looks good.
方法 2:
If you're going to be running this query a lot, and especially if Client_ID is seldom updated, you should consider a computed column or pre‑calculating CorrectedClient and storing it separately.
(by Chad、KM.、Aaron Bertrand)