Reputation:
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
Upvotes: 0
Views: 3487
Reputation: 52518
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.
Upvotes: 4
Reputation: 116401
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.
Upvotes: 0
Reputation: 13723
I don't know about the loops, but here are a few pointers
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.
Upvotes: 0
Reputation: 1499770
I would suggest:
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:
Upvotes: 8
Reputation: 83847
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”.
Upvotes: 0