How to optimize / speed up the execution of C #, Windows.Net code

Question

How to optimize / speed up the execution of C #, Windows.Net code

I have a 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

Could this be optimized? Just focus on minimizing cycles. I don't get any more ideas. Any help would be appreciated

0

c #

Sandhurst 07 jan. 09 at 11:25 am

source to share

5 answers

You may have optimized the creation of the SQL string, but I think it is peanuts compared to the time it takes to communicate with the SQL server.

You gain a few milliseconds optimizing your rows, but you lose a lot using the dataset.

I think you should focus on this part first. And not just for dataset, there is a lot more if you optimize SQL server. Maybe go into a stored procedure, look at indexing, etc.

Also, this code is not safe at all against SQL injection attacks. You have to use parameters.

+4

GvS 07 jan. '09 at 11:38

source to share

If C # s StringBuilder is something like Javas StringBuilder then a string like

strMain.append(strQuery+" ");

is a sure sign that you don't understand what the StringBuilder is for.

In Java this line will be compiled to

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

I am sure this cannot be called "optimized".

0

Bombe 07 jan. '09 at 11:31

source to share

I don't know about loops, but here are some pointers

Don't use the strQuery + "system in your inline constructors. Add" "too.
If you are looking for optimized SQL performance, what do you do with a dataset. Use Datareader instead.
Listen to Jon Skeet.

I think your code needs to be reworked as to how it is modeled to get a nice performance boost. Personally, I avoid datasets when I can.

0

Cyril gupta 07 jan. '09 at 11:32

source to share

Minor problems: strWhere

- this string

and you do a lot of manipulation. You are probably better off using StringBuilder

for this. You can also see a slight advantage when initializing StringBuilder

with a suitable length.

0

Brian Rasmussen 07 jan. '09 at 11:34

source to share

Jon Skeet · Accepted Answer · 2009-01-07T11:32:30+0000

I would suggest:

Use parameterized SQL
Get rid of the catch block, or at least use "throw" instead of "throw ex" so you don't lose information.
Get rid of the finally block - it won't help you
Declare variables when you need them first, not everything at the top of the method
Break the method into more manageable sections

When all this is done, you will be better at performance tuning. At this point, profile your application and see if the pain point is actually in .NET code or interacting with the database. In my experience, database applications are usually enhanced with:

Improving SQL queries
Reduction of round trips
Database optimization (using indexes, etc.)

How to optimize / speed up the execution of C #, Windows.Net code

More articles: