SQL Server中CLR存储过程的性能
本文关键字：性能 存储过程 CLR Server SQL | 更新日期: 2023-09-27 18:29:49
我有一个SQL CLR存储过程，它解析TLVBER字符串，每次向DB发送命令时都会调用此CLR过程，共有6个命令。
这6个命令表示单个事务。
在每个接收TLVBER字符串的命令中，我将字符串解析为带有值的标记，然后将其插入或更新到相应的列/表中。这个标签存在于数据库中很重要，我通过查询来验证它。
为了知道标签在哪个列/表中被插入或更新，我在DB中有一个表，我在其中存储id_tag、表和列。
问题是：
我不知道该做什么更好，因为我已经在CLR中对整个过程进行了编码，我进行了许多查询和插入&更新。
例如，有一个问题是，我必须制作一个临时表，我读到这是不推荐的，所以这就是我用CLR解决这个问题的原因，因为你可以使用ArrayList；总的来说，从C#编写代码比从T-SQL编写代码更容易。
另一方面，我想在一个表中插入所有标签引用那些带有id的标签，因为如果我将所有标签存储在一个单独的标签中表中，我将存储发送到数据库的所有不同COMMAND的标记。
处理时间是标准的，因为每分钟将有数千个事务。
在CLR中，我必须从数据库中查询数据（这些查询会影响CLR的时间吗？）。
下一个问题是在TLVBER的解析结束时，因为我需要连接解析结果，并比较解析的每个标签（每个标签都用ArrayList迭代），并查询插入或更新它的表/列。我认为构建查询可能会更好，将其作为表返回到T-SQL，这样T-SQL中的执行速度会比CLR中更快。
无论如何，我必须对DB进行多次查询才能获得信息，那么有没有办法将表发送到CLR SP？；为了避免从CLR进行查询，最好在T-SQL中查询必要的数据，并将其发送到CLR，这样就可以处理数据，而不会与DB进行任何交互（没有产生延迟的连接，只处理数据）。
下面的代码向您展示了我如何从T-SQL调用CLR，然后我向您展示循环，在其中我进行查询和插入&更新
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();
}
}
忽略DBO.LOGDB中的插入，这些插入用于调试，其他插入查询&插入和更新是关键过程。
问题是，我如何在CLR中通过传递表和解析数据来执行数据处理，并且只构建查询并将其发送到T-SQL，从而执行它？，或者不存在调用查询的延迟问题&插入&更新。
考虑一下这个CLR过程每分钟会被调用很多次。
例如，有一个问题是，我必须制作一个临时表，我读到这是不推荐的，所以这就是我使用CLR 解决这个问题的原因
你从哪里读到不应该创建临时表？你为什么相信这些信息？是否提供了证据？临时桌子应该可以。如果行不多，您甚至可以尝试使用表变量。
从我对代码的了解来看（尽管很难说，因为这段代码的大部分似乎都在调试；调试的东西应该是一个存储过程，而不是动态SQL和移动到一个单独的方法中，所以在这段代码中每次都只有一行），这里确实没有太多工作可以保证使用SQLCLR。最适用的操作似乎是将字符串初始拆分为多个部分，是SQLCLR可以非常有效地执行的操作。看起来每个循环都使用了原始字符串中的8个字符，所以编写一个简单的SQLCLR TVF时要考虑以下内容：
- 如果输入字符串永远不会超过4000个字符，请确保通过
[SqlFacet(MaxSize = 4000)] SqlString TLV使用NVARCHAR(4000) - 创建一个由3个字符串组成的结构，对于每组8个字符，返回分成这3个字段的4、2和2
- 使用
yield return theObject;流式输出结果 - 确保在
SqlFunction()属性中指定IsDeterministic = true; - 确保您不进行任何数据访问（但不需要标记
DataAccessKind.None，因为这是默认设置） - 确保将程序集标记为
WITH PERMISSION_SET = SAFE
最后3项可能允许SQLCLR TVF参与并行计划（我知道它适用于标量函数，对TVF不太确定）。
关于C#代码和一般过程的一些注意事项：
- 为什么一直打
TLV.ToString().ToCharArray()？为什么不在开始时创建一个char[]并将TLV.ToString().ToCharArray()分配给它呢？那你就不需要重复那么多次了 SqlDataReader是一个一次性对象，但您只能关闭它们- 如果此代码经常运行，您可能需要重新考虑调试，因为它执行许多的单行DML语句，这是大量额外的事务，会减慢此过程
- 您可能希望对其进行重组，使其更加基于集合，而不是对
@TLV字符串的每个卡盘执行太多操作 - 您是否真的将十六进制值存储为字符串（例如
SeleccionaTag.CommandText = "SELECT id_tag FROM dbo.tags where dbo.tags.id_tag = '" + Stag + "'";）？这似乎有点不对 - 如果确实要将表/数组传递给SQLCLR存储过程，可以将分隔字符串传递给pars，也可以将值存储在本地临时表中并传递这些临时表的表名