gpt4 book ai didi

c# - SQL Server 的 CLR 存储过程的性能

转载 作者:行者123 更新时间:2023-11-30 23:31:10 26 4
gpt4 key购买 nike

我有一个解析 TLVBER 字符串的 SQL CLR 存储过程,每次向数据库发送命令时都会调用此 CLR 过程,共有 6 个命令。

6 个命令代表一个事务。

在每个接收 TLVBER 字符串的命令中,我将字符串解析为标签及其值,然后将其插入或更新到相应的列/表中。这个标签存在于数据库中很重要,我通过查询验证它。

要知道在哪个列/表中插入或更新了标签,我在数据库中有一个表,我在其中存储了 id_tag、表和列。

问题是:

我不知道什么是更好的选择,因为我已经在 CLR 中编写了整个过程,我做了很多查询、插入和更新。

例如,一个问题是我必须制作一个临时表,我读到不推荐这样做,所以这就是我用 CLR 解决这个问题的原因,因为你可以使用 ArrayList;总体而言,使用 C# 编写代码比使用 T-SQL 更容易。

另一方面,我想将所有标签插入一个表中,并且使用 id 引用这些标签,因为如果我将所有标签存储在一个单独的标签中表,我将存储发送到数据库的所有不同命令的标签。

这对处理时间至关重要,因为每分钟将有数千笔交易。

在 CLR 中,我必须从数据库查询数据(这些查询会影响 CLR 的处理时间吗?)。

下一个问题是在 TLVBER 的解析结束时,因为我需要加入解析的结果并比较解析的每个标签(每个标签都用 ArrayList 迭代它)并查询表/列在哪里插入或更新它。我认为构建查询可能会更好,然后将其作为表返回给 T-SQL,这样在 T-SQL 中的执行速度会比在 CLR 中更快。

无论如何,我必须对数据库进行多次查询才能获取信息,那么有没有办法将表发送到 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 过程很多次。

最佳答案

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

您在哪里读到不应创建临时表?你为什么相信这些信息?提供证据了吗?临时表应该没问题。如果行数不多,您甚至可以尝试使用表变量。

从我所看到的代码来看(虽然很难说,因为看起来大部分代码都在调试;调试的东西应该是一个存储过程而不是动态 SQL移到一个单独的方法中,因此在此代码中每次都只是一行),这里实际上没有做太多事情来保证使用 SQLCLR。最适用的操作似乎是将字符串初始拆分为多个部分, SQLCLR 可以相当高效地执行此操作。看起来每个循环都使用原始字符串中的 8 个字符,因此请记住以下几点并编写一个简单的 SQLCLR TVF:

  1. 如果输入字符串永远不会超过 4000 个字符,请确保通过 [SqlFacet(MaxSize = 4000)] SqlString TLV 使用 NVARCHAR(4000)
  2. 创建一个包含 3 个字符串的结构,对于每组 8 个字符,返回分解为这 3 个字段的 4、2 和 2。
  3. 使用yield return theObject;流出结果
  4. 确保在 SqlFunction() 属性中指定 IsDeterministic = true;
  5. 确保您不进行任何数据访问(但无需标记 DataAccessKind.None,因为这是默认设置)。
  6. 确保将程序集标记为 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,或者将值存储在本地临时表中并传递这些临时表的表名。<

关于c# - SQL Server 的 CLR 存储过程的性能,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34700687/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com