转换“null” varchar 转十进制
我需要在 sql-server-2005 中创建一些 xml 结构（借用 C 短语）。为此，我将所有值更改为 varchar。当我想使用这些值时，问题就出现了，我必须将它们转换为十进制。
因此，我的 xml 代码如下所示：
set @result = @result + <VAL>' + coalesce(cast(@val as varchar(20)), '-.11111') + '</VAL>'
这样，如果 VAL 为 null，我将返回一个特殊的小数，并且可以检查该小数。这样做的缺点是，当我使用该值时，我无法在另一端使用合并，我必须检查它转换后的值是否等于 0。 像这样：
case when cast(InvestmentReturn.fn_getSTRUCT(...args...).value('results[1]/VAL[1]', 'varchar(40)')as decimal(10,5)) = -.11111
由于现在性能不可接受，我认为提高性能的一种方法可能是使用合并而不是使用嵌套的 case 语句并检查值与我的特殊“null”等效项是否相等。
有什么想法吗？
另外，我看到 selectcast('null' asdecimal(10,5)) 给了我：
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I have a requirement to create some xml structs (to borrow a C-phrase) in sql-server-2005. In order to do this, I change all my values to varchar. The problem arises when I want to make USE of these values, i have to convert them to decimal.
So, my xml code looks like this:
set @result = @result + <VAL>' + coalesce(cast(@val as varchar(20)), '-.11111') + '</VAL>'
this way, if VAL is null, I return a special decimal and I can check for that decimal. The drawback of doing this, is that I can't use coalesce on the other end when I use the value, I have to check if it converted value is equal to 0.
like this:
case when cast(InvestmentReturn.fn_getSTRUCT(...args...).value('results[1]/VAL[1]', 'varchar(40)')as decimal(10,5)) = -.11111
Since performance is unacceptable right now, I thought one way to improve performance might be to use coalesce instead of using a nested case statement and checking the value for equality with my special 'null' equivalent.
Any thoughts?
also, i see that select cast('null' as decimal(10,5)) gives me:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
性能问题可能由多种因素引起。
第一个是在 sql 2005 中使用 XML。我不知道您正在使用的 xml 数据的大小，但是当我 5 年前尝试这个时，如果您跨越了一定的大小障碍（我认为它是 32k，可能是 64k ）然后处理性能就断崖式下降。 1 个额外字节将导致查询从 500 毫秒延长到 60 秒。那时我们不得不放弃让 SQL Server 自己处理 XML 数据。在 C# 中进行处理要快得多。
第二个是调用 select 语句内的函数。如果该函数必须对多行进行操作，那么性能就会下降。我经常用 GETDATE() 来说明这一点。如果将变量设置为 GETDATE() 的返回值，然后在选择查询中使用该变量，它将比在查询本身中调用 GETDATE() 快一个数量级。您提供的小代码示例可能是一个杀手，因为它正在调用一个函数。
对于您眼前的问题，这可能不是一个很好的答案，但我真的相信您从 SQL Server 中取出任何 XML 处理代码并使用您选择的任何其他语言来执行它会更好。
Performance issues can be caused by a number of factors.
The first one is using XML in sql 2005. I don't know the size of the xml data you are using but when I tried this 5 years ago if you crossed a certain size barrier (I think it was 32k, might have been 64k) then processing performance fell off the cliff. 1 extra byte would cause a query to go from 500ms to 60 seconds. We had to abandon letting SQL server deal with XML data itself at that point. It was MUCH faster to do that processing in C#.
The second one is making calls to functions inside a select statement. If that function has to operate on multiple rows, then performance goes down. One example I always use to illustrate this is GETDATE(). If you set a variable to the return of GETDATE() and then use that variable in a select query it will run an order of magnitude faster than calling GETDATE() in the query itself. The little code example you provided could be a killer just because it's calling a function.
This may not be a good answer to your immediate problem, but I really believe you would be much better served yanking any XML processing code out of SQL server and doing it in ANY OTHER language of your choice.