用SQL Server同学请进 customized replace function

huaxianz

wonderdream 发表于 2013-4-17 10:05
In SQL Server 2008, MS has improved the dynamic threads  allocation and parallelism over partition ...

Of course what you posted is TRUE, but not what I'm talking about. I may also say, you may put you table into memory to completely eliminate IO by Hekaton technology, I can guarantee you 10 to 100 times overall performance improvement. Does it make sense? No, when people ask for apple, don't always give orange back to complicate things.

henda

干嘛不叫 application team 把 data clean 好才insert 进 db。
对乱写 data 的 application 是万般讨厌。。。

wonderdream

huaxianz 发表于 2013-4-17 11:25
Of course what you posted is TRUE, but not what I'm talking about. I may also say, you may put you ...

Well, you said "Partition的好处不是parallelism,而是possible partition elimination"

What I am trying say is that starting from SQL Server 2008, parallelism is also one of the benefits of table partitioning.

Anyway, my original purpose is to give my 2 cents to make the query runs faster. If I'm giving the orange instead of apple which is asked originally, I would have to say sorry.

huaxianz

wonderdream 发表于 2013-4-17 11:47
Well, you said "artition的好处不是parallelism,而是possible partition elimination"

What I am tryi ...

You win dude

jl162401

因为我不做业务系统 所以不能修改表的partition设置等等

这个partition elimination的概念应该和Teradata的相似

我昨发了code上一个论坛 得到如下回复：

I would RUN. don't walk, away from that looping version as fast as you can go. If you move away fast it will be the ONLY time that function will see anything go fast. The nested replace option in your original post is going to be your best choice here. It will be a little tedious to code it but if you make it an iTVF it will be super fast. To gain the benefit of an iTVF you need to keep it to one statement. That is why I suggest using nested replaces.

我始终没有想明白咋做cross apply，今天晚上看了两篇文章似乎有了一点点感觉

jl162401

难道像这样？

SELECT 'D' AS RECORDTYPECODE
, a.[Id]
,a.[FundsMovementItemTypeId]
,a.[FundsMovementSetId]
,a.[FeeTypeId]
,AccountName = iTVF.result
,GLAccountName = iTVF1.result
,LodgementReference =  iTVF2.result
,AccountNumber = iTVF3.result
,a.[IsSundryParties]
, TraceBSB = iTVF4.result
,TraceAccountName = iTVF5.result
,TraceAccountNumber = iTVF6.result
,a.[IsSundryPartiesTraceACC]
,a.[IsAttractGST]
,a.[IsCustomerAccount]
,a.[Amount]
,a.[OnDateTime]
,AccountBankCode = iTVF7.result
,a.[U1]
,U2 = iTVF8.result
,a.[U3]
,a.[U5]
,a.[genID]
FROM %DBNAME%.FundsMovementItem a
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.AccountName) iTVF
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.GLAccountName) iTVF1
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.LodgementReference) iTVF2
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.AccountNumber) iTVF3
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.TraceBSB) iTVF4
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.TraceAccountName) iTVF5
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.TraceAccountNumber) iTVF6
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.AccountBankCode) iTVF7
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.U2) iTVF8

pureboy

phome

这个神一样的函数真的出自神马都会的阿三之手？

wonderdream

jl162401 发表于 2013-4-17 20:10
难道像这样？

SELECT 'D' AS RECORDTYPECODE

looks like it.

How's the performance?

huaxianz

jl162401 发表于 2013-4-17 20:10
难道像这样？

SELECT 'D' AS RECORDTYPECODE

If you had 100 columns to be transformed, would you wrap up the Apply operator 100 times?

Ok, my answer is just one apply with one tvf. Sorry I know you are not Sql guy, but better to understand why Microsoft introduced tvf and the proprietary apply operator.

wonderdream

I did a search on "Inline Table-Valued Functions"

My understanding is that it outperforms usual database view and the syntax should be just like:

select * from ITVF()

And the ITVF should look like:

CREATE FUNCTION dbo.ITVF()
RETURNS TABLE
AS
RETURN
        SELECT
                <your logic of cleaning the special characters, e.g. lots of nested replace()>
    FROM  <your original table>
GO

Therefore, there shuold be no cross apply should be used. Your final extract query shuold just like select * from ITVF()

wonderdream

http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function

huaxianz

wonderdream 发表于 2013-4-18 15:52
I did a search on "Inline Table-Valued Functions"

My understanding is that it outperforms usual dat ...

Dude... again, 你说的没错，肯定可行，我也不用去读那篇文章。但是我们在这里讨论什么？如果一个tvf就解决了的话，那还要tvf干嘛？直接就 “SELECT <your logic of cleaning the special characters, e.g. lots of nested replace()>  FROM  <your original table>”不行吗？还要再包一层伪装成tvf? 你。。。明白我的意思？欢迎讨论

我个人觉得：The beauty of tvf is parametrization, and particularly in this case the combination of Cross Apply + tvf has the balance between performance and ease of coding.

小声说一句，如果楼主给我加分的话，我可以帮他写出来我的方法。不过楼主太抠门了 而且估计楼主也能悟出我的意思自己写。不过写完后看一下execuation plan就知道1个cross apply和8个甚至是100个的区别的了。

wonderdream

huaxianz 发表于 2013-4-18 18:27
Dude... again, 你说的没错，肯定可行，我也不用去读那篇文章。但是我们在这里讨论什么？如果一个tvf就解 ...

事实上， 我的确觉得SELECT <your logic of cleaning the special characters, e.g. lots of nested replace()>  FROM  <your original table 就可以了，只不过是readability的问题， 毕竟 select * from itvf() 看上去好看多了。

至于corss apply的问题嘛，要用base table corss apply TVF(), 我不太确定这样是不是最efficient

我不知道怎样给分，如果可以的话，我愿意给分， 我对最后的结论挺感兴趣的

huaxianz

wonderdream 发表于 2013-4-18 21:23
事实上， 我的确觉得SELECT   FROM

没有parameter的tvf就是个view，换句话说tvf的出现就是因为parameterized view不存在。

cross apply我只能说不会比目前最优的“lots of nested replace()”慢太多，因为原理都是row-by-row的。虽然我个人还是觉得tvf+cross apply的overhead多少大于纯“lots of nested replace()”方法。

分还是算了，你比我还少，不过谢谢 而且我也是开玩笑，分对我也没啥用。

我就直接把楼主的copy过来了，其实他的已经很接近了。当然他的tvf代码要稍微改一下。如果逻辑重复的话，还可以写一个scalar function被tvf调用8次。这种写法和楼主的区别就是8次cross apply需要8次row-by-row operation，前一次的output是下一次的input，一次做一件事；我的写法就是一次做8件事，一个loop就搞定。这个看一下execution plan就明白了。


SELECT 'D' AS RECORDTYPECODE
, a.[Id]
,a.[FundsMovementItemTypeId]
,a.[FundsMovementSetId]
,a.[FeeTypeId]
,AccountName = iTVF.result1
,GLAccountName = iTVF.result
,LodgementReference =  iTVF.result2
,AccountNumber = iTVF.result3
,a.[IsSundryParties]
, TraceBSB = iTVF.result4
,TraceAccountName = iTVF.result5
,TraceAccountNumber = iTVF.result6
,a.[IsSundryPartiesTraceACC]
,a.[IsAttractGST]
,a.[IsCustomerAccount]
,a.[Amount]
,a.[OnDateTime]
,AccountBankCode = iTVF.result7
,a.[U1]
,U2 = iTVF.result8
,a.[U3]
,a.[U5]
,a.[genID]
FROM %DBNAME%.FundsMovementItem a
CROSS
APPLY   dbo.ReplaceSplVarcharsBICD_test4(a.AccountName，a.GLAccountName，a.LodgementReference...) iTVF

wonderdream

huaxianz 发表于 2013-4-18 21:59
没有parameter的tvf就是个view，换句话说tvf的出现就是因为parameterized view不存在。

cross apply我只 ...

"没有parameter的tvf就是个view，换句话说tvf的出现就是因为parameterized view不存在。"

Excellent. 谢谢指教。

有机会，多向你学习

老衲

For this case, it seems cross apply will not  improve performance as TVF still apply on each row.

A PERSISTED computed column could be the easiest way for this case. If you do not care data latency and want to better performance, a small ETL and DW could be an option or it could be better to clean the data in the application side instead of DB .  

huaxianz

wonderdream 发表于 2013-4-18 22:48
"没有parameter的tvf就是个view，换句话说tvf的出现就是因为parameterized view不存在。"

Excellent. 谢 ...

客气客气，其实我不如你，真的

tvf碰巧用的多些  

huaxianz

老衲 发表于 2013-4-19 11:03
For this case, it seems cross apply will not  improve performance as TVF still apply on each row.

...

大师所言极是。这里面corss apply + tvf在这里有点脱裤子放P之嫌。我后来其实是顺着楼主的思路走的。个人觉得把他后来搞到的那个big nested replace()放在一个scalar function里直接调用是最简单合理的。
PERSISTED computed column是个很好的思路，如果数据需要被读取次数 > 1。

jl162401

huaxianz 发表于 2013-4-18 21:59
没有parameter的tvf就是个view，换句话说tvf的出现就是因为parameterized view不存在。

cross apply我只 ...

能否看看你的方法 分分大大的有 30分够不够啊 呵呵

jl162401

说实在的 我真不看明白你怎么一次做8件事情

jl162401

huaxianz 发表于 2013-4-20 19:45
大师所言极是。这里面corss apply + tvf在这里有点脱裤子放P之嫌。我后来其实是顺着楼主的思路走的。个人 ...

数据会每天读一次 不能修改表结构（生产系统），数据每日更新 所以需要重新进行处理