issues with a large sql query

Question

I have to update a table containing millions of records. Now I am storing the id's of all the records to be updated in a List. The query is generated pragmatically as follows :

string queryPart="";
foreach (int id in transactionsToUpdate.ToList())
{
    queryPart+="TransactionID="+id;
    queryPart+=" OR ";
}

queryPart += "1=0";
string query = @"UPDATE dbo.OutgoingQueue SET Status='C' WHERE "+queryPart;

Currently even with 100,000 values in the list, there are two problems coming up. Firstly, the code above takes a long time to execute ( the query formation part ). And secondly, when I execute the query on DB it gives Timeout Expired exception. Is there is a better way to achieve what I want ?

UPDATE : The first problem of query taking a long time to form has been solved by using stringbuilder. But the second problem still remains. If I increase the timeout, then I get sql out of resource exception.

Answer 1

This is an ideal use-case for Table-Valued Parameters. See here: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Alternatively, you could also make a #temp table (or a staging table), fill it with SqlBulkCopy (see here), and then JOIN against it to do your UPDATE.

Answer 2

You could either find a method to pass 100,000 values into the database, albeit if you use parameters you run in to a limit very quickly.

UPDATE Additional wrapped in transaction

Alternatively this is what a prepared query is used for

using (var conn = <GETCONNETIONMETHOD>)
{
  conn.Open();
  using (var tran = conn.BeginTransaction()) 
  {
    using (var cmd = conn.CreateCommand(
        @"update dbo.outgoingqueue set status = 'C' where transactionID = @id"))
    {
       cmd.Transaction = conn.BeginTransaction();
       var param = cmd.Parameters.Add("@id", typeof(int));
       cmd.Prepare();
       foreach (int id in transactionsToUpdate.ToList())
       {
         param.Value = id;
         cmd.ExecuteNonQuery();
       }
       tran.Commit();
     }
  }
}

If you have enough permissions to perform a bulk copy then the best way would be

using (var conn = <GETCONNECTIONMETHOD>)
{
   var dt = new DataTable;
   dt.BeginLoadData();
   dt.Columns.Add("id");
   foreach (int id in transactionsToUpdate.ToList() {
     dt.Rows.Add(id);
   }
   dt.EndLoadData();

   using (var cmdSetup = conn.CreateCommand(@"create table #tempUpdate(int id)")) {
      cmdSetup.ExecuteNonQuery();
   }
   var bcp = new SqlBulkCopy(conn);
   bcp.DestinationTableName = "#tempUpdate";
   bcp.WriteToServer(dt);
   using (var cmdUpdate = conn.CreateCommand(
      @"update o set status = 'C' from dbo.outgoingQueue o " +
      @"inner join #tempUpdate t on o.transactionId = t.id"))
   {
      cmd.ExecuteNonQuery();
   }
}

Answer 3

you can do following things to solve your problem .

1. Speed Issue For this let me know what items are in transactionsToUpdate.ToList() this list ? If you fill up this list from database then i suggest to modify your update query so that you don't need to run for loop . it will increase performance of your application . you can do something like following query . no need to use looping everytime . it will run lot lot faster then you current code and i think you will not face problem of time out if you use this query .

   UPDATE dbo.OutgoingQueue 
   SET Status='C' 
   FROM dbo.OutgoingQueuE AS A
   INNER JOIN 
   (
   QUERY BY WHICH YOU FILL UP CURRENT LIST
   ) AS B ON A.TransactionID = B.ID
   

2. Time Out Issue you can set command time out when you execute the sql command.

   SqlCommand myCommand = new SqlCommand(); myCommand.CommandTimeout = 15;

Answer 4

Assuming the transactionsToUpdate is a List of int,

Get a comma separated list here:

string queryPart = String.Join(",", transactionsToUpdate.ToArray());

Then pass it in the query like:

string query = @"UPDATE dbo.OutgoingQueue SET Status='C' WHERE TransactionID IN(" + queryPart + ")";

OR you can create a stored procedure that accepts a comma separated list of values and pass the queryPart to the stored procedure.

UPDATE:

You can then do this in bulk operation through .Net e.g:

int count = 0;
int bulkCount = 1000;

while (count < transactionsToUpdate.Count)
{
    string queryPart = String.Join(",", transactionsToUpdate.ToArray().Skip(count).Take(bulkCount));
    string query = @"UPDATE dbo.OutgoingQueue SET Status='C' WHERE TransactionID IN(" + queryPart + ")";

    //execute the sql here by doing the ExecuteNonQuery call.

    count += bulkCount;
}

This query will take the first 1000 from the list, process them, then take another 1000 until all of them are processed.

Answer 5

The answer on the size of the update:

If you have small value of ids to be update then you can create batch statement and send it in single transaction.

update dbo.outgoingqueue out set status = 'C' where out.transactionID = %1;
update dbo.outgoingqueue out set status = 'C' where out.transactionID = %2;
update dbo.outgoingqueue out set status = 'C' where out.transactionID = %3;
update dbo.outgoingqueue out set status = 'C' where out.transactionID = %4;
update dbo.outgoingqueue out set status = 'C' where out.transactionID = %5;
update dbo.outgoingqueue out set status = 'C' where out.transactionID = %6;

If the ids are not so small to do it in single transaction you can create a temporary table and execute update query like this:

update dbo.outgoingqueue out set status = 'C' where 
 exists (select null from tmp_tab where tmp_tab.transactionID = out.transactionID);

If you plan to update all of the records, the best way is not to update at all.

You should create a new table with the new names using:

select <column list> into <table name> from <source>;

Then in select you set you set your new values and at the end you just rename the tables.

Answer 6

The tack I would take:

cmd.CommandText = "CREATE TABLE #Values( id  Int )";
cmd.ExecuteNonQuery();


foreach (int id in transactionsToUpdate.ToList())
{
    cmd.CommandText = "INSERT INTO #Values VALUES( " + id.ToString() + ");" 
    cmd.ExecuteNonQuery();
}

Now, rather than 100,000 IFs, you can test for the value by joining against this TEMP table, which is SQL's natural domain. If you'll be doing multiple tests against the same set of numbers, it might make sense to index the table after loading it.

Answer 7

One suggestion for your code

use StringBuilder instead of using String It speed up your process

StringBuilder queryPart = new StringBuilder("");
foreach (int id in transactionsToUpdate.ToList())
{
queryPart.Append("TransactionID=");
queryPart.Append(id);
queryPart.Append(" OR ");
}
queryPart.Append("1=0");
string query = @"UPDATE dbo.OutgoingQueue SET Status='C' WHERE "+queryPart.toString();

You shall always use StringBuileder if you are doing large manipulation

EDIT 1 You can check your execution performance using StopWatch class

This will show how quicker is StringBuilder Ever 100 to 1000 time faster than String