Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Improve Performance using StringBuilder Class
All developers at some point in time have used string concatenation to build huge strings in a particular format. Most of us would normally do this using concatenation operators like ‘&̵
ITPro Today
June 28, 2004
7 Min Read
All developers at some point in time have used string concatenation to build huge strings in a particular format. Most of us would normally do this using concatenation operators like ‘&’ in VB.NET or ‘+’ in C#.
Given below is C# code demonstrating normal string concatenation. This is an example of an Oracle SQL Query being created.
string sHo = "HO" ; string sRO = "RO" ; string dAC = "dAC" ; string lvTemp = "temp" ; string RecKey = "key" ; string sFieldNam = " SELECT ERF_SECTION_NM,DAT_ACCT_DT,ERF_NBR, " + " ERF_LOB_NM,ERF_DESC,ERF_VALID_VALUE_CD, " + " ERD_FIELD_NM FROM " + " MXT_ERREF ER, MXT_ERRDAT ED, " + " MXT_DATSUSP DS, MXT_LOB LO " + " WHERE ER.ERF_NBR = ED.ERD_ERR_NBR " + " AND ER.ERF_SECTION_NM = ED.ERD_SECTION_NM " + " AND to_char(DS.DAT_ACCT_DT,'dd') = to_char(ED.ERD_ACCT_DT,'dd')" + " AND LO.LOB_REC_STRUCT_TXT = ER.ERF_SECTION_NM " + " AND LO.LOB_REC_STRUCT_TXT = ED.ERD_SECTION_NM " + " AND DS.DAT_LOB_NM = LO.LOB_NM AND " + " DS.DAT_ROITEM_CD = " + sRo + " AND DS.DAT_HOITEM_CD = " + sHo + " AND to_char(DS.DAT_ACCT_DT,'dd') = " + dAC + " AND ERF_SECTION_NM = " + "'" + lvTemp + "'" + " AND DS.DAT_KEY_NBR = " + RecKey + " order by ED.ERD_KEY_NBR "; |
Behind the scenes, whenever a string value changes, the CLR destroys the previous string instance and creates a new one, which results in memory allocation and de-allocation. This is all done by the CLR and the operations are not really known to the programmer.
Allocation and de-allocation of memory by using the Garbage collector are expensive operations for the CLR. Performance is really affected when strings get big and large blocks of memory are being and allocated and de-allocated in quick succession, as happens during heavy string concatenation.
In the SQL query above, the ‘+’ operator is used 23 times, which means the string ‘sFieldNam’ is destroyed and created 23 times.
The option is to use the StringBuilder Class. The StringBuilder class maintains it’s own buffer for string operations. This buffer length is checked each time a new string is added to the existing string, if found insufficient, it is increased by a pre-defined limit.
Given below is the code for SQL query using the StringBuilder Class.
string sHo = "HO" ; string sRO = "RO" ; string dAC = "dAC" ; string lvTemp = "temp" ; string RecKey = "key" ; System.Text.StringBuilder oSB = new System.Text.StringBuilder(" SELECT ERF_SECTION_NM,DAT_ACCT_DT,ERF_NBR, ") ; oSB.Append(" ERF_LOB_NM,ERF_DESC,ERF_VALID_VALUE_CD, "); oSB.Append(" ERD_FIELD_NM FROM "); oSB.Append(" MXT_ERREF ER, MXT_ERRDAT ED, "); oSB.Append(" MXT_DATSUSP DS, MXT_LOB LO "); oSB.Append(" WHERE ER.ERF_NBR = ED.ERD_ERR_NBR "); oSB.Append(" AND ER.ERF_SECTION_NM = ED.ERD_SECTION_NM "); oSB.Append(" AND to_char(DS.DAT_ACCT_DT,'dd') = to_char(ED.ERD_ACCT_DT,'dd')"); oSB.Append(" AND LO.LOB_REC_STRUCT_TXT = ER.ERF_SECTION_NM "); oSB.Append(" AND LO.LOB_REC_STRUCT_TXT = ED.ERD_SECTION_NM "); oSB.Append(" DS.DAT_ROITEM_CD = "); oSB.Append(sRO); oSB.Append(" AND DS.DAT_HOITEM_CD = "); oSB.Append(sHo); oSB.Append(" AND to_char(DS.DAT_ACCT_DT,'dd') = "); oSB.Append(dAC); oSB.Append(" AND ERF_SECTION_NM = "); oSB.Append("'"); oSB.Append(lvTemp); oSB.Append("'"); oSB.Append(" AND DS.DAT_KEY_NBR = " ); oSB.Append(RecKey); oSB.Append(" order by ED.ERD_KEY_NBR "); string sField = oSB.ToString(); |
Happy Coding !!!
About the Author
You May Also Like
.png?width=700&auto=webp&quality=80&disable=upscale)