微软ACE驾驶员改变浮点precision我在程序的其他部分 [英] The Microsoft ACE driver changes the floating point precision in the rest of my program
问题描述
我有一个问题，似乎有些计算结果改变已经使用的微软ACE驾驶员打开一个Excel US preadsheet。
在code初级讲座重现问题。
前两个调用 DoCalculation 产生相同的结果。然后，我调用函数打开preadSheet 打开和关闭使用ACE驱动的Excel 2003小号preadsheet。你不会想到打开preadSheet 有在最后调用 DoCalculation 任何影响，但事实证明，结果实际上改变。这是该程序生成的输出：
1,59142713593566
1,59142713593566
1,59142713593495
请注意在最后3位小数的差异。这似乎不是一个很大的区别，但在我们的生产code中的计算很复杂，所产生的差异是相当大的。
这没有什么区别，如果我用ACE驾驶员的JET驱动程序。如果我更改从类型双为十进制错误消失。但是，这并不在我们的生产code选项。
我在Windows 7 64位运行，组件被编译为.NET 4.5 x86的。使用64位的ACE驾驶员是不是一种选择，因为我们在运行32位Office。
有谁知道为什么发生这种情况，我怎么能解决这个问题？
下面code重现我的问题：
静态无效的主要（字串[] args）
{
DoCalculation（）;
DoCalculation（）;
打开preadSheet（）;
DoCalculation（）;
}
静态无效DoCalculation（）
{
//将两个随机选择的数10.000倍。
VAR D1 = 1.0003123132;
VAR D3 = 0.999734234;
双解析度= 1;
的for（int i = 0; I＆LT; 10000;我++）
{
RES * = D1 * D3;
}
Console.WriteLine（RES）;
}
公共静态无效打开preadSheet（）
{
变种CN =新的OleDbConnection（@供应商= Microsoft.ACE.OLEDB.12.0;数据源= C：\ TEMP \ workbook1.xls;扩展属性= Excel的8.0）;
VAR CMD =新的OleDbCommand（选择[列1] FROM [工作表Sheet1 $]，CN）;
cn.Open（）;
使用（CN）
{
使用（OleDbDataReader读卡器= cmd.ExecuteReader（））
{
// 没做什么
}
}
}
这是技术上是可行的，非托管的code可摆弄FPU控制字，并改变它的计算方式。著名的麻烦制造者是用Borland工具编译的DLL，其运行时支持code取消屏蔽的异常可能会崩溃管理code。和DirectX，它是已知的与FPU控制字修修补补得到与计算的双的执行为的浮动的加快图形运算。
的具体种类FPU控制字的变化，要在此处进行会出现是舍入模式，使用时，它需要写入的内部寄存器值的80位precision到一个64位存储器位置FPU的。它有4个选项，以使该转换：四舍五入，取整，截断和舍入到偶数（银行家的舍入）。非常小的差异，但你作出努力，迅速积累他们。如果你的数字模型是不稳定的，那么你肯定会看到最终结果的差异。这并不能使它或多或少准确的，只是不同而已。
管理code是pretty的手无寸铁code做这个，你不能直接访问FPU控制字。它需要编写汇编code。你有一招可用，高度无证但是pretty的效果。 CLR会的重置的，只要它处理一个异常FPU。所以，你可以这样做：
公共静态无效ResetMathProcessor（）
{
如果（IntPtr.Size = 4！）回报; //无需在64位code，它使用SSE
尝试 {
抛出新的异常（请忽略，重设FPU）;
}
赶上（例外前）{}
}
千万要小心，这是昂贵的，以便用尽可能少。它是当你调试code，所以你可能要禁用此在Debug版本的一大皮塔。
我应该提到另一种选择，你可以的PInvoke的_f preSET（）函数MSVCRT.DLL。然而有风险的，如果你使用它的方法也执行浮点运算内，抖动优化器不知道这个功能抽搐的地板垫。你需要彻底的测试发布版本：
[System.Runtime.InteropServices.DllImport（MSVCRT.DLL）
公共静态外部无效_f preSET（）;
和千万记住，这样做的不可以让你的计算结果更准确的任何方式。只是不同而已。就像运行您的code发布版本没有调试器会产生比调试版本不同的结果。发布版本code将执行四舍五入较少的这种，因为抖动优化用力保持FPU内的中间结果在80位precision。从生产调试版本，但一个实际上更准确的一个不同的结果。给予或采取。这80位中间格式是英特尔的数十亿美元的错误，在SSE2指令集不重复。
I am having a problem where it seems that the results of some calculations change after having used the Microsoft ACE driver to open an Excel spreadsheet.
The code belows reproduces the problem.
The first two calls to DoCalculation yield the same results. Then I call the function OpenSpreadSheet which opens and closes an Excel 2003 spreadsheet using the ACE driver. You would not expect OpenSpreadSheet to have any effect on the last call to DoCalculation but it turns out that the result actually changes. This is the output that the program generates:
1,59142713593566
1,59142713593566
1,59142713593495
Note the differences on the last 3 decimals. This does not seem like a big difference but in our production code the calculations are complex and the resulting differences are quite large.
It makes no difference if I use the JET driver instead of the ACE driver. If I change the types from double to decimal the error goes away. But this is not an option in our production code.
I am running on a Windows 7 64 bit and the assemblies are compiled for .NET 4.5 x86. Using the 64 bit ACE driver is not an option as we are running 32 bit Office.
Does anybody know why this is happening and how I can fix it?
The following code reproduces my problem:
static void Main(string[] args)
{
DoCalculation();
DoCalculation();
OpenSpreadSheet();
DoCalculation();
}
static void DoCalculation()
{
// Multiply two randomly chosen number 10.000 times.
var d1 = 1.0003123132;
var d3 = 0.999734234;
double res = 1;
for (int i = 0; i < 10000; i++)
{
res *= d1 * d3;
}
Console.WriteLine(res);
}
public static void OpenSpreadSheet()
{
var cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;data source=c:\temp\workbook1.xls;Extended Properties=Excel 8.0");
var cmd = new OleDbCommand("SELECT [Column1] FROM [Sheet1$]", cn);
cn.Open();
using (cn)
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
// Do nothing
}
}
}
This is technically possible, unmanaged code may be tinkering with the FPU control word and change the way it calculates. Well-known trouble makers are DLLs compiled with Borland tools, their runtime support code unmasks exceptions that can crash managed code. And DirectX, it is known for tinkering with the FPU control word to get calculations with double to be performed as float to speed up graphics math.
The specific kind of FPU control word change that appears to be made here is the rounding mode, used by the FPU when it needs to write an internal register value with 80-bit precision to a 64-bit memory location. It has 4 options to make that conversion: round up, round down, truncate and round-to-even (banker's rounding). Very small differences but you do make an effort to accumulate them rapidly. And if your numerical model is unstable then you certainly will see a difference in the end result. That doesn't make it more or less accurate, just different.
Managed code is pretty defenseless against code that does this, you cannot directly access the FPU control word. It requires writing assembly code. You've got one trick available, highly undocumented but pretty effective. The CLR will reset the FPU whenever it handles an exception. So you could do this:
public static void ResetMathProcessor()
{
if (IntPtr.Size != 4) return; // No need in 64-bit code, it uses SSE
try {
throw new Exception("Please ignore, resetting the FPU");
}
catch (Exception ex) {}
}
Do beware that this is expensive so use as infrequently as possible. And it is a major pita when you debug code so you might want to disable this in the Debug build.
I should mention an alternative, you can pinvoke the _fpreset() function in msvcrt.dll. It is however risky if you use it inside of a method that also performs floating point math, the jitter optimizer doesn't know that this function jerks the floor mat. You'll need to thoroughly test the Release build:
[System.Runtime.InteropServices.DllImport("msvcrt.dll")]
public static extern void _fpreset();
And do keep in mind that this does not make your calculation results more accurate in any way. Just different. Just like running the Release build of your code without a debugger will produce different results than the Debug build. The Release build code will perform this kind of rounding less frequently since the jitter optimizer makes an effort to keep intermediate results inside the FPU at 80-bit precision. Producing a different result from the Debug build but one that actually is more accurate. Give or take. This 80-bit intermediate format was Intel's billion dollar mistake, not repeated in the SSE2 instruction set.
这篇关于微软ACE驾驶员改变浮点precision我在程序的其他部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
