4

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.

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.

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.

I am using simple insert update commands in stored procedures in the Windows service and there are no transactions in those stored procedures.

Please advise any solution to avoid this issue.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ahmad
  • 41
  • 1
  • First thing - process big data at once is bad idea. Redesigning your system to import data in chunks is the first thing I would do. – Renatas M. Mar 14 '17 at 11:24
  • You should be consider using background tasks which process your big imports in background and do not interrupt the main thread of application. – Just code May 26 '17 at 09:58

6 Answers6

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ammar Ahmed
  • 126
  • 8
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.

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.

Best is to split the xml content and try using savepoints and commit all the transactions finally.

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?

Hari Hara Krishnan
  • 68
  • 8
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.

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

During that time, no other operations - not even SELECTs, are possible against that table ....

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
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

Yasirmx
  • 417
  • 3
  • 9
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.

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.

John Eisbrener
  • 642
  • 8
  • 17
-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.

Community
  • 1
  • 1
skvsree
  • 487
  • 1
  • 7
  • 19