my application has the following procedure.
我的申请有以下程序。
a database of products (20,000 rows) exists.
存在产品数据库(20,000行)。
our client has 'import' feature where he imports an excel file.
我们的客户端具有导入excel文件的“导入”功能。
this is implemented by deleting all products table rows, then doing the import - which is a long thing since we programmatically performing calculations on the data.
这是通过删除所有产品表行,然后执行导入来实现的 - 这是一件很长的事情,因为我们以编程方式对数据执行计算。
the obvious problem is if the 'import' action fails (IO stuff), they now have none/partial/curropt data in the products table.
显而易见的问题是,如果'import'操作失败(IO东西),他们现在在products表中没有/ partial / curropt数据。
We wish that if the 'import' operation fails, the original data remains.
我们希望如果“导入”操作失败,原始数据仍然存在。
this is ASP.NET application, written in C#, using SQL Server 2005 and using XSD which we created through the VS2005 design tools.
这是用C#编写的ASP.NET应用程序,使用SQL Server 2005并使用我们通过VS2005设计工具创建的XSD。
4 个解决方案
#1
- Start transaction
- delete data from table
- Insert new data
- if no problem happens, commit changes
从表中删除数据
插入新数据
如果没有问题发生,提交更改
#2
I would import the data onto a table with the same structure as your products table, and then replace the data on your products table once you're happy the import has been successful.
我会将数据导入到与product表结构相同的表中,然后在导入成功后感到高兴,然后替换product表上的数据。
This will mean that users can carry on using the system while the import is underway, as well as minimizing the down time, while you update the products table.
这意味着用户可以在导入过程中继续使用系统,并在更新产品表时最大限度地减少停机时间。
#3
Using a transaction would be the obvious choice here.
在这里使用交易将是明显的选择。
I guess the first thing I would ask is do you really need to clear the entire table? Is there a timestamp or something you could use to limit the amount of data that needs to be refreshed? Could you re-work the logic to use updates instead of all the deletes and inserts? That way your transactions will be smaller.
我想我要问的第一件事是你真的需要清理整个桌子吗?是否有时间戳或某些东西可用于限制需要刷新的数据量?您是否可以重新使用逻辑来使用更新而不是所有删除和插入?这样你的交易就会变小。
-Dan
#4
I would go with the transaction approach as outlined above. But the only problem I can see is that you might end up locking the whole table for the entire period the import process is taking place. you might need to think about it. The seperate table approach can be one of the solutions.
我会采用上面概述的交易方法。但我能看到的唯一问题是,您可能最终会在导入过程的整个期间锁定整个表。你可能需要考虑一下。单独的表格方法可以是解决方案之一。
#1
- Start transaction
- delete data from table
- Insert new data
- if no problem happens, commit changes
从表中删除数据
插入新数据
如果没有问题发生,提交更改
#2
I would import the data onto a table with the same structure as your products table, and then replace the data on your products table once you're happy the import has been successful.
我会将数据导入到与product表结构相同的表中,然后在导入成功后感到高兴,然后替换product表上的数据。
This will mean that users can carry on using the system while the import is underway, as well as minimizing the down time, while you update the products table.
这意味着用户可以在导入过程中继续使用系统,并在更新产品表时最大限度地减少停机时间。
#3
Using a transaction would be the obvious choice here.
在这里使用交易将是明显的选择。
I guess the first thing I would ask is do you really need to clear the entire table? Is there a timestamp or something you could use to limit the amount of data that needs to be refreshed? Could you re-work the logic to use updates instead of all the deletes and inserts? That way your transactions will be smaller.
我想我要问的第一件事是你真的需要清理整个桌子吗?是否有时间戳或某些东西可用于限制需要刷新的数据量?您是否可以重新使用逻辑来使用更新而不是所有删除和插入?这样你的交易就会变小。
-Dan
#4
I would go with the transaction approach as outlined above. But the only problem I can see is that you might end up locking the whole table for the entire period the import process is taking place. you might need to think about it. The seperate table approach can be one of the solutions.
我会采用上面概述的交易方法。但我能看到的唯一问题是,您可能最终会在导入过程的整个期间锁定整个表。你可能需要考虑一下。单独的表格方法可以是解决方案之一。