TIMEOUT when searching data from SQL Server when executing update commands

Question

I have a windows service developed in C #. This service imports data from XML files into a SQL Server database in different tables. These XML files are large. A specific 500 MB file takes more than 1 hour to import into tables with millions of rows.

I have a web application developed in ASP.NET MVC, the above database is at the end of this application. This application shows different reports by filtering related records from tables in this database. This application also processes records.

When I run an application to interact with the database while importing the XML file by the Windows service, my web application freezes and a timeout error occurs after a long time.

I use simple insert update commands in stored procedures in a Windows service, and there are no transactions in these stored procedures.

Please report any solution to avoid this problem.

Answer 1

You can increase the time of commands, for example,

cmd.CommandTimeout = 3600; // in seconds

You also need to examine the stored procedure, why it gives a timeout. Your request breaks down, where

Answer 2

I can offer a few things from my experience.

First of all, pushing all 500 MB data in one transaction is not recommended.

Then create the necessary savepoints and finally commit it if all transactions are successful.

. , sql-. .

xml .

, . , .

, .

, . , ?

Answer 3

SQL Server INSERT UPDATE - , . , SQL Server .

SQL Server , . . , 5000 , SQL Server 5000 , ( ).

- SELECT s, ....

, , , 5000 , .

Answer 4

. :

<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>  

= 500

Answer 5

SQL Server Enterprise Edition, Table Partitioning. , , , , . , . , , , .

, , , , : , .

Answer 6

(nolock). SO Nolock. ORM, EntityFramework, .