Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2015
    Posts
    5

    Unanswered: How to Improve C# Code & DataBase Performance (Update Query)

    Hi All,

    Please find below code -

    string updateQuery = "UPDATE TENDORS SET TENDORS_VALUE = ";
    Cmd.CommandText = "SELECT TENDORS_VALUE,ID,TENDOR_DATE FROM TENDORS WHERE ID IN (" + "" + (IDValues) + "" + ")";
    DB2DataReader dr = Cmd.ExecuteReader();
    while (dr.Read())
    {
    tENDORS_VALUE = dr["TENDORS_VALUE"].ToString();
    id = dr["ID"].ToString();
    tDate = Convert.ToDateTime(dr["TENDOR_DATE"]);
    contextStr = GenerateHexaValue(id, tENDORS_VALUE);
    if (!string.IsNullOrEmpty(contextStr))
    {
    finalUpdateQuery += updateQuery + " '" + contextStr + "' " + " WHERE ID = '" + id + "' AND TENDOR_DATE = '" + tDate+ "'; ";
    }
    }
    if (!string.IsNullOrWhiteSpace(finalUpdateQuery))
    {
    DB2Command command = new DB2Command(finalUpdateQuery.Substring(0, finalUpdateQuery.Length - 1), connect);
    command.Transaction = dbTran;
    rowCount = command.ExecuteNonQuery();
    }

    Here finalUpdateQuery (stringBuilder) is -

    UPDATE TENDORS SET TENDORS_VALUE = '080' WHERE ID = '1245789633' AND TENDOR_DATE = '2012-01-02';
    UPDATE TENDORS SET TENDORS_VALUE = '080' WHERE ID = '1235853861' AND TENDOR_DATE = '2011-02-22';
    UPDATE TENDORS SET TENDORS_VALUE = '0A0' WHERE ID = '2565854283' AND TENDOR_DATE = '2013-11-19';
    UPDATE TENDORS SET TENDORS_VALUE = '0A0' WHERE ID = '0565853935' AND TENDOR_DATE = '2012-02-02';
    UPDATE TENDORS SET TENDORS_VALUE = '080' WHERE ID = '5565854148' AND TENDOR_DATE = '2013-07-02';
    UPDATE TENDORS SET TENDORS_VALUE = '480' WHERE ID = '4565850296' AND TENDOR_DATE = '2012-12-20';
    UPDATE TENDORS SET TENDORS_VALUE = '480' WHERE ID = '4565850507' AND TENDOR_DATE = '2012-10-12';
    UPDATE TENDORS SET TENDORS_VALUE = '480' WHERE ID = '4565850644' AND TENDOR_DATE = '2012-09-02';
    UPDATE TENDORS SET TENDORS_VALUE = '480' WHERE ID = '4565851488' AND TENDOR_DATE = '2012-08-02';
    UPDATE TENDORS SET TENDORS_VALUE = '080' WHERE ID = '4610849238' AND TENDOR_DATE = '2012-07-02';
    UPDATE TENDORS SET TENDORS_VALUE = '4A0' WHERE ID = '4610849238' AND TENDOR_DATE = '2012-06-02';
    UPDATE TENDORS SET TENDORS_VALUE = 'EB0' WHERE ID = '4610849238' AND TENDOR_DATE = '2012-05-02';
    UPDATE TENDORS SET TENDORS_VALUE = 'EA0' WHERE ID = '4610850154' AND TENDOR_DATE = '2012-04-02';
    UPDATE TENDORS SET TENDORS_VALUE = 'EA0' WHERE ID = '4610850154' AND TENDOR_DATE = '2012-03-02';
    UPDATE TENDORS SET TENDORS_VALUE = 'EA0' WHERE ID = '4610850154' AND TENDOR_DATE = '2012-12-02';
    UPDATE TENDORS SET TENDORS_VALUE = 'CA0' WHERE ID = '4610851768' AND TENDOR_DATE = '2012-08-02';
    UPDATE TENDORS SET TENDORS_VALUE = 'CA0' WHERE ID = '4610851768' AND TENDOR_DATE = '2012-07-02';
    UPDATE TENDORS SET TENDORS_VALUE = 'CA0' WHERE ID = '4610851768' AND TENDOR_DATE = '2012-06-02';
    UPDATE TENDORS SET TENDORS_VALUE = '8B0' WHERE ID = '4565853935' AND TENDOR_DATE = '2012-05-02';
    UPDATE TENDORS SET TENDORS_VALUE = '8B0' WHERE ID = '4565854146' AND TENDOR_DATE = '2009-04-02';
    UPDATE TENDORS SET TENDORS_VALUE = '8B0' WHERE ID = '4565854146' AND TENDOR_DATE = '2009-03-02';
    UPDATE TENDORS SET TENDORS_VALUE = '9B0' WHERE ID = '4565854283' AND TENDOR_DATE = '2010-02-02';
    UPDATE TENDORS SET TENDORS_VALUE = '9B0' WHERE ID = '4565854283' AND TENDOR_DATE = '2011-01-02';
    .
    .
    .
    .
    upto 1000 Update statements


    When we are executing above query (i.e. Executing 1000 Update Queries) it is taking much time. I want to boost performance. Which is best way to optimized it in terms of code and Time ?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2level and platform is always needed to be able to reply :
    db2 describe indexes for table tendors show detail
    much time ? minutes-days-years ? be more specific
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2015
    Posts
    5
    Hello,

    For 1000 Update queries it is taking 5-6 minutes which is too much for our application. I want optimized solution.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on the development language being used.

    but you would be better off using a parameterised query / prepared statement rather than sending essentially the same query 1000 times, send the prepared statement once, that allows the sql engine to parse it, work out the query plan, then call the prepared statement as many times as required.

    you may get an improvement by grouping together the same outcomes
    Code:
    UPDATE TENDORS SET TENDORS_VALUE = '080' 
    WHERE (ID = '1245789633' AND TENDOR_DATE = '2012-01-02')
    OR (ID = '1235853861' AND TENDOR_DATE = '2011-02-22')
    OR (ID = '5565854148' AND TENDOR_DATE = '2013-07-02'); 
    
    UPDATE TENDORS SET TENDORS_VALUE = '0A0'
    WHERE (ID = '2565854283' AND TENDOR_DATE = '2013-11-19')
    OR (ID = '0565853935' AND TENDOR_DATE = '2012-02-02')
    OR (ID = '4610849238' AND TENDOR_DATE = '2012-07-02'); 
    
    UPDATE TENDORS SET TENDORS_VALUE = '480'
    WHERE (ID = '4565850296' AND TENDOR_DATE = '2012-12-20')
    OR (ID = '4565850507' AND TENDOR_DATE = '2012-10-12') 
    OR (ID = '4565850644' AND TENDOR_DATE = '2012-09-02')
    OR (ID = '4565851488' AND TENDOR_DATE = '2012-08-02');
    OR you could use a stored procedure

    however as przytula_guy infers it may also require looking at indexing to see if by adding the tendor_date to the columns being indexed if that improves performance.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2015
    Posts
    5
    Hi,

    Thanks for reply.

    Again your solution we are checking the Date & ID every time. It becomes time consuming as earlier.
    In DB2, Can we have a class like Db2BulkCopy for insertion ? any other solution in optimized way ?

    Thanks.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    load is a db2 utility that can be used to bulk load data
    as I could understand : these are update statements and not insert statements
    you should be more specific with requirements - purpose - what to do?
    1000 updates 6min = 3/sec - looks ok for individual update statements
    load has a complete other approach : look in infocenter
    or have a dba to explain what you are trying to achieve
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    run some comparatives, get some facts before worrying about performance
    then
    see if a prepared statement will improve the process, that should be pretty trivial to implement given your current code
    see if bunching the update to the same value works
    see if a redesign of the schema would be a better way out of the problem, if there is a problem.

    work out where the time is being consumed
    work out why the time is being consumed, is it:-
    a hardware issue (need more memory?, faster processor)
    a configuration issue (does the db2 server need tweaking, and or the allocation of resources on the server itself)
    a network issue
    and so on
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2015
    Posts
    5
    Quote Originally Posted by przytula_guy View Post
    load is a db2 utility that can be used to bulk load data
    as I could understand : these are update statements and not insert statements
    you should be more specific with requirements - purpose - what to do?
    1000 updates 6min = 3/sec - looks ok for individual update statements
    load has a complete other approach : look in infocenter
    or have a dba to explain what you are trying to achieve


    Comments = > we want to update a column based on requirement. Here 1000 update queries we need to execute to update the records which is taking much time. We want to optimized the performance. Suggest me best solution.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Sud,
    You have been asked several questions(which you have not answered). You have been given several suggestions on next steps. Yet, you continue to ask us for best solution. If you want more than what is already here, I would suggest you contact one of us regarding our hourly rates to help you resolve your issue.
    Dave

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated in my post : I do not participate in "Spoon feeding"
    try to understand.. try to learn.. try to read ...
    all the doc is available and for FREE, just open it..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Sep 2015
    Posts
    5

    719

    Quote Originally Posted by przytula_guy View Post
    as indicated in my post : I do not participate in "Spoon feeding"
    try to understand.. try to learn.. try to read ...
    all the doc is available and for FREE, just open it..
    Hello Experts,

    I have implemented the suggestions you provided and still they were time taking.
    I just asked whether we have Built-In (like DB2BulkCopy) classes or not, not to feed me by spoon.

    If you don't know the answers, you can clearly reply us sorry we don't have answers for you query instead of blaming the users.[/B]

    Regards,
    Sudarshan

  12. #12
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    nobody has a list with all answers : we just try to get info from submitter

    which suggestion have you implemented ?
    time taking ? be precise by indicating rows /sec ...
    have a look at load command
    as you need to invoke it from an application, I point you the page for use of load with admin_cmd
    http://www-01.ibm.com/support/knowle...0-0-12&lang=en
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  13. #13
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Has your DBA been monitoring the bufferpool hit ratios and I/O rates during your job run? Has any performance analysis been done? Have you run the sql through db2advis and/or db2expln?
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •