I have a SQL CLR stored procedure that parses a TLVBER String, this CLR procedure is called each time a command is sent to the DB, there are 6 commands.
The 6 commands represents a single transaction.
In each command I receive a TLVBER string, I parse the string into tags, with their value, then I insert it or update it in its corresponding column/table. Is important that this tag exist on the DB, I validate it via a query.
To know in which column/table the tag is inserted or updated it, I have a table in the DB, where I store the id_tag, the table and the column.
Here is the problem:
I don't know what is better option to do, because I have coded the entire process in the CLR, I made many queries and inserts & updates.
One single problem for example is that I have to make a temporary table, and I read that is not recommend it, so that's why I solve this with CLR, because you can use ArrayList's; overall it´s easier to code from C# than T-SQL.
By the other hand I´ve thought to insert all the tags in a single table, and reference those tags with an id, because if I store all the tags in one single table, I will store the tags of all different COMMANDS sent to the database.
It's critical the processing time, because there will be thousands of transactions per minute.
In the CLR I have to query data from the DB (could the queries compromise the time processing from CLR?).
The next problem is at the end of the parse of the TLVBER, because I need to join the results of the parse and compare each tag (each tag is iterate it with an ArrayList) parsed and query the table/column where is inserted or update it. I've thought that it could be better to build the queries, an return it as a table to T-SQL so the execution would be faster in T-SQL than in the CLR.
Anyways I have to do many queries to the DB to get information, so is there a way to send tables to a CLR SP?; to avoid making queries from the CLR, instead would be better to query the necessary data in T-SQL, and send it to the CLR so this process the data and doesn't make any interaction with the DB (no connections that generate delays, only processing data).
Well the code below show you how I invoke the CLR from T-SQL, then I show you the loop, where I make the queries and inserts&updates
EXEC SP_CLR_PARSEATLVBER @TLV= '00102001010010010100100201007F010101',
@error=@ERRORCLR OUTPUT,
@CMD='OPN'
while (cursor <= total)
{
Stag = "";
// -- * ******************EXTRAE TAG*******************
tag = TLV.ToString().ToCharArray(cursor, 4);
foreach (char c in tag)
Stag = Stag + c;
Stag = "0x" + Stag;
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" '" + "TAG:" + Stag + "'," +
" '128'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
SeleccionaTag.CommandText = "SELECT id_tag FROM dbo.tags where dbo.tags.id_tag = '" + Stag + "'";
SeleccionaTag.Connection = conn;
reader = SeleccionaTag.ExecuteReader();
int vacio = 0;
String id_tag = "";
while (reader.Read())
{
vacio++;
id_tag = reader.GetSqlString(0).ToString();
}
reader.Close();
if (vacio == 0)
{
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'ID_TAG NO SE ENCUENTRA'," +
" '137'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
}
{
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" '" + "Vacio:" + id_tag + "'," +
" '137'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
}
//-- /////////////////////////////////////
// --*******************AVANCE EN LA CADENA*******************
cursor = cursor + 4;
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'Cursor despues de extraer tag(" + Stag + "): " + cursor.ToString() + "'," +
" '137'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
// -- /////////////////////////////////////
// --*******************EXTRAE TAMAÑO EN BYTES DEL TAG EXTRAIDO *******************
length = TLV.ToString().ToCharArray(cursor, 2);
Slength = "";
foreach (char c in length)
Slength = Slength + c;
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'TAMAÑO DE TAG:" + Slength + "'," +
" '209'," +
" SYSDATETIME()" +
" )";
Int32.TryParse( Slength, NumberStyles.HexNumber,
new CultureInfo("en-US"), out varlength);
InsertaLog.ExecuteNonQuery();
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'TAMAÑO DE TAG(INT):" + varlength.ToString() + "'," +
" '218'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
Slength = "";
if ((varlength) > 127)
{
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
"'TAG SUPERA LONGITUD'," +
" '227'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
varlength = 0;
// --*******************AVANCE EN LA CADENA*******************
cursor = cursor + 2;
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'Cursor despues de extraer tamaño de TAG(" + tag + "):" +cursor+ "'," +
" '237'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
// -- /////////////////////////////////////
TotalBytes = varlength - 128;
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'Total de Bytes Asignados de la longitud del TAG(" + tag + "):" + TotalBytes+"',"+
" '246'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
length = TLV.ToString().ToCharArray(cursor, 2);
foreach (char c in length)
Slength = Slength + c;
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'TAMAÑO DE TAG:" + Slength + "'," +
" '257'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
Int32.TryParse(Slength, NumberStyles.HexNumber,
new CultureInfo("en-US"), out varlength);
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'TAMAÑO DE TAG(INT):" + varlength.ToString() + "'," +
" '266'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
varlength = varlength * 2;
//--*******************AVANCE EN LA CADENA*******************
cursor = cursor + (TotalBytes * 2);
// -- /////////////////////////////////////
}
else
{
length = TLV.ToString().ToCharArray(cursor, 2);
Slength = "";
foreach (char c in length)
Slength = Slength + c;
Int32.TryParse(Slength, NumberStyles.HexNumber,
new CultureInfo("en-US"), out varlength);
//--*******************AVANCE EN LA CADENA*******************
cursor = cursor + 2;
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'Slength:" + Slength + "'," +
" '286'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'TAMAÑO DE TAG(INT):" + varlength.ToString() + "'," +
" '294'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
// -- /////////////////////////////////////
}
//-- /////////////////////////////////////
//-- * ******************EXTRAE VALOR DE TAG EXTRAIDO *******************
value = TLV.ToString().ToCharArray(cursor, varlength);
foreach (char c in value)
Svalue = Svalue + c;
SeleccionaTipoDato.CommandText = "SELECT dbo.TAGS.tipodato" +
" FROM dbo.TAGS" +
" WHERE dbo.TAGS.id_tag = '" + tag + "'";
SeleccionaTipoDato.Connection = conn;
reader = SeleccionaTipoDato.ExecuteReader();
while (reader.Read())
{
tipoDato = reader.GetSqlString(0).ToString();
}
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'TipoDato:" + tipoDato.ToString() + "'," +
" '294'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
reader.Close();
if (tipoDato.Equals("ASCII"))
{
Convertidor C = new Convertidor();
valorParseadoASCII=C.ValueToASCII(Svalue);
}
cursor = cursor + varlength;
//-- /////////////////////////////////////
//-- ******************* Tabla para agregar tags con su respectivo valor *******************
Tupla.Add(Stag);
Tupla.Add(Svalue);
if(tag.Equals("0x2005"))
TID = Svalue;
if(tag.Equals("0x1003"))
MID = Svalue;
if (tag.Equals("0xE001"))
xid = Convert.ToInt64(Svalue);
CMD.Add(Tupla);
InsertaLog.CommandText = "INSERT INTO DBO.LOGDB values " +
" ('PARSEATLVBER', " +
" 'Tupla Agregada: (" + Tupla[0]+","+Tupla[1] + ")'," +
" '320'," +
" SYSDATETIME()" +
" )";
InsertaLog.ExecuteNonQuery();
Tupla.Clear();
Svalue = "";
Slength = "";
Stag = "";
//-- /////////////////////////////////////
}
// INSERCION DE CAMPO LLAVE
if (cmd.Equals("OPN"))
{
SeleccionaNuevaLLave.CommandText = "SELECT NEXT VALUE FOR dbo.llave";
reader = SeleccionaNuevaLLave.ExecuteReader();
while (reader.Read())
SecuenciaLlave = Convert.ToInt64(reader.GetSqlString(0));
reader = SeleccionaNuevaLLave.ExecuteReader();
SeleccionaNuevaTransId.CommandText = "SELECT NEXT VALUE FOR dbo.transid";
while (reader.Read())
SecuenciaTransid = Convert.ToInt64(reader.GetSqlString(0));
InsertaNuevaTransaccion.CommandText = "INSERT INTO dbo.transaccion"+
" values (xid,trans_id,mid,tid)" +
" ("+SecuenciaLlave + "," +
SecuenciaTransid + "," +
MID + "," +
TID +
" )";
InsertaNuevaTransaccion.CommandText = "INSERT INTO DBO.AMT (xid) values (" + SecuenciaLlave +")"; InsertaNuevaTransaccion.ExecuteNonQuery();
InsertaNuevaTransaccion.CommandText = "INSERT INTO DBO.CRD (xid) values (" + SecuenciaLlave + ")"; InsertaNuevaTransaccion.ExecuteNonQuery();
InsertaNuevaTransaccion.CommandText = "INSERT INTO DBO.DMO (xid) values (" + SecuenciaLlave + ")"; InsertaNuevaTransaccion.ExecuteNonQuery();
InsertaNuevaTransaccion.CommandText = "INSERT INTO DBO.HST (xid) values (" + SecuenciaLlave + ")"; InsertaNuevaTransaccion.ExecuteNonQuery();
InsertaNuevaTransaccion.CommandText = "INSERT INTO DBO.MRC (xid) values (" + SecuenciaLlave + ")"; InsertaNuevaTransaccion.ExecuteNonQuery();
InsertaNuevaTransaccion.CommandText = "INSERT INTO DBO.PRO (xid) values (" + SecuenciaLlave + ")"; InsertaNuevaTransaccion.ExecuteNonQuery();
InsertaNuevaTransaccion.CommandText = "INSERT INTO DBO.RCV (xid) values (" + SecuenciaLlave + ")"; InsertaNuevaTransaccion.ExecuteNonQuery();
InsertaNuevaTransaccion.CommandText = "INSERT INTO DBO.SND (xid) values (" + SecuenciaLlave + ")"; InsertaNuevaTransaccion.ExecuteNonQuery();
}
else
{
SecuenciaLlave = xid;
}
int j = 0;
String tabla,columna;
foreach (ArrayList A in CMD)
{
SeleccionaTipoDato.CommandText = "SELECT dbo.TAGS.tabla,"+
" dbo.TAGS.columna" +
" FROM dbo.TAGS" +
" WHERE dbo.TAGS.id_tag = '" + A[0].ToString() + "'";
reader = SeleccionaTipoDato.ExecuteReader();
while (reader.Read())
{
tabla = reader.GetSqlString(0).ToString();
columna = reader.GetSqlString(1).ToString();
ActualizaTabla.CommandText = "UPDATE "
+tabla+
" SET "
+columna+"='"+A[1]+"'"+
" where "+tabla+".xid="+SecuenciaLlave;
ActualizaTabla.ExecuteNonQuery();
}
}
Ignore the inserts in the DBO.LOGDB, those are for debugging purpose, the other queries & inserts and updates are the critical process.
The question is how can I perform the processing data in the CLR, via passing it tables, and parse data, and only build queries and send it to T-SQL, so this execute it?, or there are no problems on delay of calling queries & inserts & updates.
Consider that this CLR procedure will be called many, many times per minute.
foreach (char c in tag) Stag = Stag + c;is the worst thing you can do. Why not useStag = new String(tag);' (ie constructor of string taking a char[] as parameter)? Same issue with buildingSLength` further down the procedure. The way you are building the strings now is just trashing your memory by creating a lot of strings in a loop.