Optimize Long running update query

Question

I have written following method with three sql queries in order to update a single column in a single table.since there are nearly 10000 records to be updated it takes more than 20 mins to complete.is there any other better way to do this updation.some thin like set based update...

private void UpdateLatest()
    {
        string connstr = "Data Source=CHAMARA-PC;Initial Catalog=PHPA_Production_fromEWP;Integrated Security=True";
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            conn.Open();
            SqlTransaction myTransaction = conn.BeginTransaction();
            try
            {
                DataTable dt = new DataTable();
                DataTable DTT = new DataTable();

                string command = "select dh.DocumentNumber,max(dr.RevisionDate) as latestdate from " +
                  "tblDocumentHeader dh inner join tblDocumentRevision dr on dh.DocumentHeaderID=dr.DocumentHeaderID " +
                      "group by dh.DocumentNumber ";


                using (SqlCommand cmd = new SqlCommand(command, conn, myTransaction))
                {
                    using (SqlDataAdapter adapt = new SqlDataAdapter())
                    {
                        adapt.SelectCommand = cmd;
                        adapt.Fill(dt);

                    }
                }
                label1.Text = dt.Rows.Count.ToString();
                foreach (DataRow item in dt.Rows)
                {
                    try
                    {
                        int res = 0;
                        string query2 = "select dr.DocumentRevisionID from " +
                  "tblDocumentHeader dh inner join tblDocumentRevision dr on dh.DocumentHeaderID=dr.DocumentHeaderID " +
                  " where dh.DocumentNumber='" + item["DocumentNumber"].ToString().Trim() + "' and dr.RevisionDate='" + item["latestdate"].ToString().Trim() + "'";

                        using (SqlCommand cmd = new SqlCommand(query2, conn, myTransaction))
                        {
                            using (SqlDataAdapter adapt = new SqlDataAdapter())
                            {
                                adapt.SelectCommand = cmd;
                                adapt.Fill(DTT);

                            }
                        }

                        foreach (DataRow Ritem in DTT.Rows)
                        {
                            string updatequery = "update tblDocumentRevision set LatestRev='latest' where DocumentRevisionID='" + Ritem["DocumentRevisionID"].ToString().Trim() + "'";

                            using (SqlCommand cmd = new SqlCommand(updatequery, conn, myTransaction))
                            {
                                cmd.ExecuteNonQuery();

                                res++;

                            }
                        }
                        listBox1.Items.Add(item["DocumentNumber"].ToString() + " " + "updated");

                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                myTransaction.Commit();
                MessageBox.Show("successfully updated");
            }
            catch (Exception ex)
            {
                myTransaction.Rollback();
                MessageBox.Show(ex.Message);

            }
        }
    }

Answer 1

I don't have your database at hand to test this, but basically, your code boils down to this:

DECLARE @Temp TABLE (DocNumber INT, LatestDate DATETIME)

INSERT INTO @Temp(DocNumber, LatestDate)
    SELECT 
        dh.DocumentNumber, MAX(dr.RevisionDate) 
    FROM 
        dbo.tblDocumentHeader dh 
    INNER JOIN 
        dbo.tblDocumentRevision dr ON dh.DocumentHeaderID = dr.DocumentHeaderID
    GROUP BY 
        dh.DocumentNumber

UPDATE 
    dbo.tblDocumentRevision 
SET 
    LatestRev = 'latest' 
FROM
    dbo.tblDocumentRevision dr
INNER JOIN
    dbo.tblDocumentHeader dh ON dh.DocumentHeaderID = dr.DocumentHeaderID 
INNER JOIN
    @Temp t ON dh.DocumentNumber = t.DocNumber AND dr.RevisionDate = t.LatestDate

This can be very easily wrapped in a stored procedure, it does not use any dead slow cursors, and it does not cause any SQL injection possibilities.

Answer 2

since there are nearly 10000 records to be updated it takes more than 20 mins to complete

Learn SQL.

Ok, first - 10.000 update statements = 20 minutes means your server is a laptop with a slow disc, not a database server. This is WAY too long. The way you do is horrific slow, but at the end... 20 minutes is way too slow. If this is a real database - get a server for it. You do less than 10 updates per second, even a normal desktop can handle about 75.

Now:

You do select then update. Why? Why not do

update tblDocumentRevision set LatestRev='latest' where DocumentRevisionID IN (x,y,z)

and fire one off for every 100 or so documents? That brutally cuts down on the number of statements you issue.

On top if that is too sow you should fire them off async - prepare statement 2 while statement 1 executes, or even fire them off a number of worker threads in parallel.

And finally, SQL basics: is there an index on DocumentRevisionID? And third there is no need to use an adapter and a dta table here. This is sloq, clunky and in general avoids proper programming practices.