CODEWITHSUNDEEP
c#sqlwinformst-sql
k1f1
k1f1

Reputation: 433

Any way to speed up this UPDATE? C#, SQL, T-SQL

SQL:

CREATE FUNCTION dbo.fnRandomForeNames ()
RETURNS VARCHAR(50) 
AS
BEGIN
RETURN  (
            SELECT TOP 1 [FirstName]
            FROM [tmp_ForeNames] 
            ORDER BY (SELECT new_id from GetNewID)
        )
END
GO

Similar functions for dbo.fnRandomSurNames() etc.

UPDATE Table1
SET firstname = dbo.fnRandomForeNames(),
    lastname = dbo.fnRandomSurNames(),
    address1 = dbo.fnRandomAddress1(),
    address2 = dbo.fnRandomAddress2(),
    address3 = dbo.fnRandomAddress3(),
    birthdate = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '1990-01-01')

My C# Code: 

    private void RunThis(string connString, StreamReader sr)
    {
        sr.BaseStream.Position = 0;
        string sqlQuery = sr.ReadToEnd();
        using (SqlConnection connection = new SqlConnection(connString))
        {
            Server server = new Server(new ServerConnection(connection));
            server.ConnectionContext.StatementTimeout = 4200;
            server.ConnectionContext.ExecuteNonQuery(sqlQuery);
        }
        sr.Close();
    }

........ 

 RunThis(e.Argument.ToString(), _updateClaim);

Where e.Argument.ToString() is the connection string.

The CREATE FUNCTION scripts are run earlier, take very little time to run. Also, names are stored in tmp databases, these are entered in C# via arrays. These also take very little time to run.

Table1 contains approx 140,000 rows and takes approx. 14 mins to complete.

I have also tried using parameterised SQL queries, skipping the tmp tables and SQL functions and instead creating the SQL query and executing it from the code, such as the following:

UPDATE Table1
SET lastname = '{0}',
    firstname = '{1}',
    birthdate = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '1990-01-01'),
    address1 = '{2}',
    address2 = '{3}',
    address3 = '{4}'
    WHERE u_id = '{6}'

And some C#: 

 using (SqlConnection connection = new SqlConnection(connString))
        {
            connection.Open();
            for (int i = 0; i < arraySize; ++i)
            {
                string updateString = string.Format(updateString2, GetRandomSurname(), GetRandomForeName(), GetRandomAddress1(), GetRandomAddress2(), GetRandomAddress3(), "", ids[i]);
                SqlCommand cmd = new SqlCommand(updateString, connection);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
        }

The latter method also taking upwards of 14 minutes.

Any ideas on how to cut down the time it takes to update the table?

Thanks.

Upvotes: 1

Views: 462

Answers (2)

Taryn
Taryn

Reputation: 247650

My suggestion would be to looks at create a single table valued function to return a table with those random values in it, instead of making multiple calls to multiple functions. Something like this:

create function dbo.fnRandomStuff
(
  @FirstName bit, 
  @LastName bit, 
  @address1 bit,
  @address2 bit,
  @address3 bit
)
RETURNS @RandomStuff TABLE 
(
    FirstName varchar(50) NULL, 
    LastName varchar(50) NULL, 
    address1 varchar(50) NULL, 
    address2 varchar(50) NULL, 
    address3 varchar(50) NULL
)
AS
BEGIN
  -- your code here to get the random stuff based on the 
  -- parameters passed

END

Then since you are returning a table, you can join on the table and it will minimize the multiple calls that you are making to your functions.

Upvotes: 1

Andr&#225;s Ott&#243;
Andr&#225;s Ott&#243;

Reputation: 7695

The first update is slow, because of the functions. They are simply running too many times.

In the second case the checksum could take too many time.

Just a hint may you can try it:

What do you think about a solution where you put an IDENTITY column on each temp table, simply giving them id-s and then just have to use joins in the sql, and only throwing some Random values in c# like foreign keys to the temp tables. With the rows you can inserts the new id based records first, and then run only one big update on the main table. First is may an overkill but I think it could make a better result. In the past we used something like this for prefill some testdatabase.

P.S.: I used android phone sorry because of typos and readability:)

Upvotes: 1

Related Questions