How to Optimize/Speedup Code Execution C#, Windows.Net

I have following Code Block Which I tried to optimize in the Optimized section

DataSet dsLastWeighing = null;
DataSet ds = null;
DataSet dsWeight = null;
string strQuery = string.Empty;
string strWhere = string.Empty;
Database db = null;

#region Original Code Block
      try
      { 
       db = DatabaseFactory.CreateDatabase();

       strWhere = "WHERE SESSION_ID = '"+pSessionID+"'"; 
       strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("DeleteWeightRecent",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");
       db.ExecuteNonQuery(System.Data.CommandType.Text, strQuery);

       strWhere = "WHERE LAB_ID = 0";
       strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("InsertWeightRecent",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");
       db.ExecuteNonQuery(System.Data.CommandType.Text, strQuery);

       strWhere = strWhere = "WHERE SESSION_ID = '"+pSessionID+"'"; 
       strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("GetPatientID",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");
       ds = (DataSet) db.ExecuteDataSet(System.Data.CommandType.Text, strQuery);

       foreach(DataRow dr in ds.Tables[0].Rows)
       {
        if (db.ToString() == "Microsoft.Practices.EnterpriseLibrary.Data.SqlBase.SqlBaseDatabase")
        {
         strWhere = "WHERE LAB_ID=0 AND PAT_ID ="+ int.Parse(dr["PAT_ID"].ToString())+" AND WHEN IN(SELECT MAX(WHEN) FROM PATIENT_LAB WHERE LAB_ID=0 AND PAT_ID="+ int.Parse(dr["PAT_ID"].ToString())+")"; 
        }
        else if (db.ToString() == "Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase")
        {
         strWhere = "WHERE LAB_ID=0 AND PAT_ID ="+ int.Parse(dr["PAT_ID"].ToString())+" AND [WHEN] IN(SELECT MAX([WHEN]) FROM PATIENT_LAB WHERE LAB_ID=0 AND PAT_ID="+ int.Parse(dr["PAT_ID"].ToString())+")"; 
        }
        strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("GetWeight",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");

        strMain.append(strQuery+" ");
        dsWeight = (DataSet) db.ExecuteDataSet(System.Data.CommandType.Text, strQuery);


        foreach(DataRow drWeight in dsWeight.Tables[0].Rows)
        {       
         strWhere =  "WHERE PAT_ID = "+int.Parse(dr["PAT_ID"].ToString())+" AND SESSION_ID ='"+pSessionID+"'";
         strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("UpdateWeightRecent",db.ToString(),pFacilityID,pSessionID,strWhere,decimal.Parse(drWeight["LEVEL"].ToString()),DateTime.Parse(drWeight["WHEN"].ToString()).ToUniversalTime(),int.Parse(drWeight["IS_BAD"].ToString()),drWeight["NOTE"].ToString());
         db.ExecuteNonQuery(System.Data.CommandType.Text, strQuery); 
        }

       }

       strWhere = " ORDER BY W.IS_BAD DESC, P.LASTNAME ASC, P.FIRSTNAME ASC,P.MIDDLENAME ASC";
       strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("GetPatientLastWeight",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");
       dsLastWeighing = (DataSet) db.ExecuteDataSet(System.Data.CommandType.Text, strQuery);     
      }
      catch(Exception ex)
      {
       throw ex;
      }
      finally
      {
       db = null;
       ds= null;
       dsWeight= null;
      }
      return dsLastWeighing;
      #endregion




--Optimized Section--

    #region Optimized Code Block

      try
      { 
       StringBuilder strMain=new StringBuilder();
       db = DatabaseFactory.CreateDatabase();

       //StartTime=DateTime.Now.ToLongTimeString();
       strWhere = "WHERE SESSION_ID = '"+pSessionID+"'"; 
       strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("DeleteWeightRecent",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");
       //EndTime=DateTime.Now.ToLongTimeString();
       //db.ExecuteNonQuery(System.Data.CommandType.Text, strQuery);
       strMain.append(strQuery+" ");

       strWhere = "WHERE LAB_ID = 0";

       //StartTime=DateTime.Now.ToLongTimeString();
         strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("InsertWeightRecent",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");
       //EndTime=DateTime.Now.ToLongTimeString();

       //db.ExecuteNonQuery(System.Data.CommandType.Text, strQuery);
       strMain.append(strQuery+" ");

       strWhere = strWhere = "WHERE SESSION_ID = '"+pSessionID+"'"; 
       //StartTime=DateTime.Now.ToLongTimeString();
         strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("GetPatientID",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");
       //EndTime=DateTime.Now.ToLongTimeString();
       //ds = (DataSet) db.ExecuteDataSet(System.Data.CommandType.Text, strQuery);

       strMain.append(strQuery+" ");
       //StartTime=DateTime.Now.ToLongTimeString();
        ds = (DataSet) db.ExecuteDataSet(System.Data.CommandType.Text, strMain.ToString());
       //EndTime=DateTime.Now.ToLongTimeString();

       strMain=null;


       foreach(DataRow dr in ds.Tables[0].Rows)
       {
        //StartTime=DateTime.Now.ToLongTimeString();
        if (db.ToString() == "Microsoft.Practices.EnterpriseLibrary.Data.SqlBase.SqlBaseDatabase")
        {
         strWhere = "WHERE LAB_ID=0 AND PAT_ID ="+ int.Parse(dr["PAT_ID"].ToString())+" AND WHEN IN(SELECT MAX(WHEN) FROM PATIENT_LAB WHERE LAB_ID=0 AND PAT_ID="+ int.Parse(dr["PAT_ID"].ToString())+")"; 
        }
        else if (db.ToString() == "Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase")
        {
         strWhere = "WHERE LAB_ID=0 AND PAT_ID ="+ int.Parse(dr["PAT_ID"].ToString())+" AND [WHEN] IN(SELECT MAX([WHEN]) FROM PATIENT_LAB WHERE LAB_ID=0 AND PAT_ID="+ int.Parse(dr["PAT_ID"].ToString())+")"; 
        }
        strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("GetWeight",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");

        strMain.append(strQuery+" ");
        //EndTime=DateTime.Now.ToLongTimeString();
        //dsWeight = (DataSet) db.ExecuteDataSet(System.Data.CommandType.Text, strQuery);

        /*
        foreach(DataRow drWeight in dsWeight.Tables[0].Rows)
        {       
         strWhere =  "WHERE PAT_ID = "+int.Parse(dr["PAT_ID"].ToString())+" AND SESSION_ID ='"+pSessionID+"'";
         strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("UpdateWeightRecent",db.ToString(),pFacilityID,pSessionID,strWhere,decimal.Parse(drWeight["LEVEL"].ToString()),DateTime.Parse(drWeight["WHEN"].ToString()).ToUniversalTime(),int.Parse(drWeight["IS_BAD"].ToString()),drWeight["NOTE"].ToString());
         db.ExecuteNonQuery(System.Data.CommandType.Text, strQuery); 
        }
        */
       }


       dsWeight = (DataSet) db.ExecuteDataSet(System.Data.CommandType.Text, strMain.ToString());
       strMain=null;
       //StartTime=DateTime.Now.ToLongTimeString();
       for(int i=0;i<dsWeight.Tables.Count;i++)
       {
        foreach(DataRow drWeight in dsWeight.Tables[i].Rows)
        {       
         strWhere =  "WHERE PAT_ID = "+int.Parse(dr["PAT_ID"].ToString())+" AND SESSION_ID ='"+pSessionID+"'";
         strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("UpdateWeightRecent",db.ToString(),pFacilityID,pSessionID,strWhere,decimal.Parse(drWeight["LEVEL"].ToString()),DateTime.Parse(drWeight["WHEN"].ToString()).ToUniversalTime(),int.Parse(drWeight["IS_BAD"].ToString()),drWeight["NOTE"].ToString());
         strMain.append(strQuery+" ");
         //db.ExecuteNonQuery(System.Data.CommandType.Text, strQuery); 
        }
       }
       db.ExecuteNonQuery(System.Data.CommandType.Text, strMain.ToString()); 
       //EndTime=DateTime.Now.ToLongTimeString();

       //StartTime=DateTime.Now.ToLongTimeString();
       strWhere = " ORDER BY W.IS_BAD DESC, P.LASTNAME ASC, P.FIRSTNAME ASC,P.MIDDLENAME ASC";
       strQuery = SQ.BusinessLogic.SQLQueryFactory.GetPatientLastWeighing("GetPatientLastWeight",db.ToString(),pFacilityID,pSessionID,strWhere,0,DateTime.Now.ToUniversalTime(),0,"");
       dsLastWeighing = (DataSet) db.ExecuteDataSet(System.Data.CommandType.Text, strQuery); 
       //EndTime=DateTime.Now.ToLongTimeString();
      }
      catch(Exception ex)
      {
       throw ex;
      }
      finally
      {
       db = null;
       ds= null;
       dsWeight= null;
      }
      return dsLastWeighing;


      #endregion

Can This Further be optimized .. Just concentrate on minimizing the loops.. I am not getting any ideas further..Any Help would be appreciated

From stackoverflow

• If C#’s StringBuilder is anything like Java’s StringBuilder then a line like

  strMain.append(strQuery+" ");
  

  is a sure sign that you have not understood what StringBuilder is for.

  In Java that line would be compiled to

  strMain.append(new StringBuilder().append(strQuery).append(" ").toString());
  

  I’m pretty sure that that can not be called “optimized”.

  Jon Limjap : Yes Bombe, .NET StringBuilders work the same way as Java StringBuilders ;)

  Bombe : Ah, thanks for the information. :)

  Joel Mueller : No, actually that's not true. String concatenation is not compiled into StringBuilder calls in C#. The StringBuilder object itself is likely very similar to the Java version, but string concatenation does not use it implicitly.

• I would suggest:

  • Use parameterised SQL
  • Get rid of the catch block, or at least use "throw" instead of "throw ex" so you don't lose the information
  • Get rid of the finally block - it's not helping you
  • Declare variables when they're first needed, not all at the top of the method
  • Break the method up into more manageable sections

  When all those have been done, you'll be in a better place to tune the performance. At that point, profile the app and see whether the pain point is actually in the .NET code or in the interaction with the database. In my experience, database applications are usually improved by:

  • Improving the SQL queries
  • Reducing the round-trips
  • Optimising the database (applying indexes etc)
  Mehrdad Afshari : +1. Correctness and simplicity are more important than pure performance.

  Bombe : +1. What Mehrdad said.

• I don't know about the loops, but here are a few pointers

  • Don't use the strQuery+" " system in your stringbuilders. Append the " " too.
  • If you're looking for optimized SQL performance what're you doing with a dataset. Use a Datareader instead.
  • Listen to Jon Skeet.

  I think your code needs a re-work as to how it's modeled to get a good performance boost. Personally I avoid datasets when I can.

• Minor issues: strWhere is a string, and you're doing a lot of manipulations. You'll probably be better off using StringBuilder for this. You may also see a small benefit from initializing your StringBuilder with a suitable length.

  Jon Skeet : I strongly suspect that the in-memory string manipulation will pale into insignificance as soon as the first database query is involved though.

• Maybe you have optimized the creation of the SQL string, but I think this is peanuts compared to the time that it takes to communicate with the SQL server.

  You win a few milliseconds by optimizing your strings, but loose a lot by using a Dataset.

  I think you should focus on that part first. And not just the dataset thing, there is a lot more to gain if you optimize the SQL server. Maybe throw in a stored procedure, look at indexing etc.

  Also, this code is not safe at all for SQL injection attacks. You should use parameters.

• more code please !!! optimized !!!