I have a Windows service developed in C#. This service imports data from XML files into a SQL Server database, into different tables. These XML files are large. A particular file of 500 MB size takes more than 1 hour to import into tables with millions of rows.
我有一个用C＃开发的Windows服务。此服务将XML文件中的数据导入SQL Server数据库，导入到不同的表中。这些XML文件很大。 500 MB大小的特定文件需要超过1小时才能导入包含数百万行的表。
I have a web application developed in ASP.NET MVC, the above database is at back end of this application. This application shows different reports by filtering related records from tables of this database. This application also manipulates the records as well.
我有一个在ASP.NET MVC中开发的Web应用程序，上面的数据库是在这个应用程序的后端。此应用程序通过过滤此数据库的表中的相关记录来显示不同的报告。此应用程序也操纵记录。
When I run the application to interact with database during the XML file import by the Windows service, then my web application hangs and after a long time a timeout error occurs.
当我在Windows服务导入XML文件期间运行应用程序与数据库交互时，我的Web应用程序挂起，并在很长一段时间后发生超时错误。
I am using simple insert update commands in stored procedures in the Windows service and there are no transactions in those stored procedures.
我在Windows服务的存储过程中使用简单的插入更新命令，并且这些存储过程中没有事务。
Please advise any solution to avoid this issue.
请告知任何解决方案以避免此问题。
6 个解决方案
#1
1
I can suggest few things from my experience.
根据我的经验，我可以提出一些建议。
First of all pushing the entire data of 500MB in a single transaction is not advisable.
首先，在单个事务中推送500MB的整个数据是不可取的。
Then make necessary savepoints and finally commit it if all the transactions are succeed.
然后制作必要的保存点，如果所有事务都成功，最后提交它。
Because holding a connection object for a long time is not advisable. If you still need you can try increasing the sql server time out. But this is a bad approach.
因为建议长时间保持连接对象是不可取的。如果您仍然需要，可以尝试增加sql server超时。但这是一个糟糕的方法。
Best is to split the xml content and try using savepoints and commit all the transactions finally.
最好是拆分xml内容并尝试使用保存点并最终提交所有事务。
Don't think you are just doing only a simple insert or update command. If you have indexes on your column it will take long time for these many records.
不要以为你只是做一个简单的插入或更新命令。如果您的列上有索引，则这些记录需要很长时间。
Make sure you have the right indexes and only the necessary indexes.
确保您拥有正确的索引并且只有必要的索引。
If you have any more questions reply here. And what is the methodology you use in you current code?
如果您有任何疑问，请在此处回复。您在当前代码中使用的方法是什么？
#2
1
You can increase command time like this
你可以像这样增加命令时间
cmd.CommandTimeout = 3600; // in seconds
You also have to look into stored procedure why it is giving timeout your query is breaking some where
您还必须查看存储过程为什么它会超时您的查询会破坏某些位置
#3
0
Any SQL Server INSERT or UPDATE command is run in a transaction - whether you specify one or not. If you don't specify anything yourself, SQL Server will use an implicit transaction to ensure data integrity of your statement.
任何SQL Server INSERT或UPDATE命令都在事务中运行 - 无论您是否指定一个。如果您自己未指定任何内容，SQL Server将使用隐式事务来确保语句的数据完整性。
By default, SQL Server uses row-level locks, e.g. it only locks rows being inserted or updated. However, if that transaction would affect more than 5000 rows, then SQL Server will do a lock escalation and replace the 5000 individual row-level locks with a full table exclusive lock until the operation has completed and the transaction is committed (or rolled back).
默认情况下，SQL Server使用行级锁，例如它只锁定正在插入或更新的行。但是，如果该事务将影响超过5000行，则SQL Server将执行锁升级并使用完整表独占锁替换5000个单独的行级锁，直到操作完成并且提交（或回滚）事务为止。
During that time, no other operations - not even SELECTs, are possible against that table ....
在此期间，没有其他操作 - 甚至SELECT也不可能对该表....
The only solution is to either run those imports at a time no other operations are running against the database, or to split these insert up into smaller chunks of less than 5000 rows that can be committed separately.
唯一的解决方案是在没有其他操作针对数据库运行的情况下运行这些导入，或者将这些插入拆分为可以单独提交的少于5000行的较小块。
#4
0
You can increase maximum number of connections in your config file. Here is my config file:
您可以增加配置文件中的最大连接数。这是我的配置文件：
<connectionStrings>
<add name="local" connectionString="user id=sa;password=test+;Data Source= (local);Max Pool Size=500;Pooling=true;Database=user" providerName="System.Data.SqlClient" />
</connectionStrings>
I have set Max Pool Size=500
我设置了Max Pool Size = 500
#5
0
If you're running SQL Server Enterprise Edition, you should be taking advantage of Table Partitioning. What you can do, specifically with data loads, is load your data into staging tables and then switch a partition into your final table. This will eliminate any table-level locks on the final table during the load process so your reports can run at the same time the loads are occurring. The byproduct here though is the data being loaded will not be viewable to the reports until the load process completes and the partition is switched in.
如果您正在运行SQL Server Enterprise Edition，则应该利用表分区。您可以做什么，特别是数据加载，将数据加载到临时表，然后将分区切换到最终表。这将消除加载过程中最终表上的任何表级锁定，因此您的报告可以在加载发生的同时运行。这里的副产品是加载的数据在加载过程完成并切换分区之前将无法查看报告。
You will want to design this properly as it will depend on a variety of factors that I'm not going to get into the nuance of in this post, but there are a number of blogs about this that you can find: here's one for reference to get you going.
你需要正确设计这个，因为它取决于我不会在这篇文章中引入细微差别的各种因素，但是你可以找到一些关于这个的博客：这里有一个供参考让你去
#6
-1
You should try with(nolock) option. This SO provides details on with Nolock. If you are using ORM like EntityFramework then we need to take closer look of code.
你应该尝试使用（nolock）选项。这个SO提供了有关Nolock的详细信息。如果您正在使用像EntityFramework这样的ORM，那么我们需要仔细查看代码。
#1
1
I can suggest few things from my experience.
根据我的经验，我可以提出一些建议。
First of all pushing the entire data of 500MB in a single transaction is not advisable.
首先，在单个事务中推送500MB的整个数据是不可取的。
Then make necessary savepoints and finally commit it if all the transactions are succeed.
然后制作必要的保存点，如果所有事务都成功，最后提交它。
Because holding a connection object for a long time is not advisable. If you still need you can try increasing the sql server time out. But this is a bad approach.
因为建议长时间保持连接对象是不可取的。如果您仍然需要，可以尝试增加sql server超时。但这是一个糟糕的方法。
Best is to split the xml content and try using savepoints and commit all the transactions finally.
最好是拆分xml内容并尝试使用保存点并最终提交所有事务。
Don't think you are just doing only a simple insert or update command. If you have indexes on your column it will take long time for these many records.
不要以为你只是做一个简单的插入或更新命令。如果您的列上有索引，则这些记录需要很长时间。
Make sure you have the right indexes and only the necessary indexes.
确保您拥有正确的索引并且只有必要的索引。
If you have any more questions reply here. And what is the methodology you use in you current code?
如果您有任何疑问，请在此处回复。您在当前代码中使用的方法是什么？
#2
1
You can increase command time like this
你可以像这样增加命令时间
cmd.CommandTimeout = 3600; // in seconds
You also have to look into stored procedure why it is giving timeout your query is breaking some where
您还必须查看存储过程为什么它会超时您的查询会破坏某些位置
#3
0
Any SQL Server INSERT or UPDATE command is run in a transaction - whether you specify one or not. If you don't specify anything yourself, SQL Server will use an implicit transaction to ensure data integrity of your statement.
任何SQL Server INSERT或UPDATE命令都在事务中运行 - 无论您是否指定一个。如果您自己未指定任何内容，SQL Server将使用隐式事务来确保语句的数据完整性。
By default, SQL Server uses row-level locks, e.g. it only locks rows being inserted or updated. However, if that transaction would affect more than 5000 rows, then SQL Server will do a lock escalation and replace the 5000 individual row-level locks with a full table exclusive lock until the operation has completed and the transaction is committed (or rolled back).
默认情况下，SQL Server使用行级锁，例如它只锁定正在插入或更新的行。但是，如果该事务将影响超过5000行，则SQL Server将执行锁升级并使用完整表独占锁替换5000个单独的行级锁，直到操作完成并且提交（或回滚）事务为止。
During that time, no other operations - not even SELECTs, are possible against that table ....
在此期间，没有其他操作 - 甚至SELECT也不可能对该表....
The only solution is to either run those imports at a time no other operations are running against the database, or to split these insert up into smaller chunks of less than 5000 rows that can be committed separately.
唯一的解决方案是在没有其他操作针对数据库运行的情况下运行这些导入，或者将这些插入拆分为可以单独提交的少于5000行的较小块。
#4
0
You can increase maximum number of connections in your config file. Here is my config file:
您可以增加配置文件中的最大连接数。这是我的配置文件：
<connectionStrings>
<add name="local" connectionString="user id=sa;password=test+;Data Source= (local);Max Pool Size=500;Pooling=true;Database=user" providerName="System.Data.SqlClient" />
</connectionStrings>
I have set Max Pool Size=500
我设置了Max Pool Size = 500
#5
0
If you're running SQL Server Enterprise Edition, you should be taking advantage of Table Partitioning. What you can do, specifically with data loads, is load your data into staging tables and then switch a partition into your final table. This will eliminate any table-level locks on the final table during the load process so your reports can run at the same time the loads are occurring. The byproduct here though is the data being loaded will not be viewable to the reports until the load process completes and the partition is switched in.
如果您正在运行SQL Server Enterprise Edition，则应该利用表分区。您可以做什么，特别是数据加载，将数据加载到临时表，然后将分区切换到最终表。这将消除加载过程中最终表上的任何表级锁定，因此您的报告可以在加载发生的同时运行。这里的副产品是加载的数据在加载过程完成并切换分区之前将无法查看报告。
You will want to design this properly as it will depend on a variety of factors that I'm not going to get into the nuance of in this post, but there are a number of blogs about this that you can find: here's one for reference to get you going.
你需要正确设计这个，因为它取决于我不会在这篇文章中引入细微差别的各种因素，但是你可以找到一些关于这个的博客：这里有一个供参考让你去
#6
-1
You should try with(nolock) option. This SO provides details on with Nolock. If you are using ORM like EntityFramework then we need to take closer look of code.
你应该尝试使用（nolock）选项。这个SO提供了有关Nolock的详细信息。如果您正在使用像EntityFramework这样的ORM，那么我们需要仔细查看代码。