ADO.NET SQL Server Performance cuello de botella

Tengo una connection sql que tengo que acceder a la database de 500 a 10.000 veces por segundo. Después de aproximadamente 250 por segundo, las cosas comienzan a desacelerarse y luego la aplicación se queda tan atrás que se bloquea.

Estaba pensando en poner la database en un dictionary. Necesito el performance más rápido que puedo get. Actualmente el ado.net tarda de 1 a 2 milisegundos aproximadamente, pero sucede algo que causa un cuello de botella.

¿Hay algo de malo en la siguiente syntax para las 10k consultas por segundo? es un dictionary que va a funcionar? estamos hablando de 12 millones de loggings y necesito poder searchlo dentro de 1 a 5 milisegundos. También tengo otra colección en la database que tiene 50 millones de loggings, así que no estoy seguro de cómo almacenarla. cualquier sugerencia será genial.

El SQL db tiene 128 gb de memory y 80 procesadores y la aplicación está en el mismo server en el server Sql 2012

using (SqlConnection sqlconn = new SqlConnection(sqlConnection.SqlConnectionString())) { using (SqlCommand sqlcmd = new SqlCommand("", sqlconn)) { sqlcmd.CommandType = System.Data.CommandType.StonetworkingProcedure; sqlcmd.Parameters.Clear(); sqlcmd.CommandTimeout = 1; sqlconn.Open(); using (SqlDataReader sqlDR = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) public static string SqlConnectionString() { return string.Format("Data Source={0},{1};Initial Catalog={2};User ID={3};Password={4};Application Name={5};Asynchronous Processing=true;MultipleActiveResultSets=true;Max Pool Size=524;Pooling=true;", DataIP, port, Database, username, password, IntanceID); } 

el código debajo del lector de datos es

 r.CustomerInfo = new CustomerVariable(); r.GatewayRoute = new List<RoutingGateway>(); while (sqlDR.Read() == true) { if (sqlDR["RateTableID"] != null) r.CustomerInfo.RateTable = sqlDR["RateTableID"].ToString(); if (sqlDR["EndUserCost"] != null) r.CustomerInfo.IngressCost = sqlDR["EndUserCost"].ToString(); if (sqlDR["Jurisdiction"] != null) r.CustomerInfo.Jurisdiction = sqlDR["Jurisdiction"].ToString(); if (sqlDR["MinTime"] != null) r.CustomerInfo.MinTime = sqlDR["MinTime"].ToString(); if (sqlDR["interval"] != null) r.CustomerInfo.interval = sqlDR["interval"].ToString(); if (sqlDR["code"] != null) r.CustomerInfo.code = sqlDR["code"].ToString(); if (sqlDR["BillBy"] != null) r.CustomerInfo.BillBy = sqlDR["BillBy"].ToString(); if (sqlDR["RoundBill"] != null) r.CustomerInfo.RoundBill = sqlDR["RoundBill"].ToString(); } sqlDR.NextResult(); 

  1. No cierre y vuelva a abrir la connection, puede mantenerla abierta entre las requestes. Incluso si tiene activada la agrupación de conexiones, existe cierta sobrecarga, que incluye una breve sección crítica para evitar problemas de concurrency al capturar una connection desde la agrupación. También puedo evitar eso.

  2. Asegúrese de que su procedimiento almacenado tiene SET NOCOUNT ON para networkingucir la cantidad de conversaciones.

  3. Asegúrese de utilizar el nivel mínimo de aislamiento de transactions con el que puede salirse con la suya, por ejemplo, lecturas sucias, también conocidas como NOLOCK. Puede establecer esto en el extremo del cliente en el nivel de connection o dentro del procedimiento almacenado, con el que se sienta más cómodo.

  4. Perfile estas transactions para asegurarse de que el cuello de botella esté en el cliente. Podría estar en el server de bases de datos o en la networking.

  5. Si se trata de una aplicación multiprocess (por ejemplo, en la web), compruebe la configuration de su set de conexiones y asegúrese de que sea lo suficientemente grande. Hay un contador PerfMon para esto.

  6. Acceda a sus campos por ordinal utilizando getters fuertemente tipados, por ejemplo, GetString (0) o GetInt32 (3).

  7. Ajusta el aspecto de tu procedimiento almacenado y tus índices. Podría escribir un libro sobre esto.

  8. Reindexe sus tablas durante periodos de inactividad, y llene las páginas de índice si esta es una tabla bastante estática.

  9. Si el propósito del procedimiento almacenado es recuperar una sola fila, intente agregar TOP 1 a la consulta para que se detenga después de que se encuentre la primera fila. Además, considere usar parameters de salida en lugar de un set de resultados, lo que implica un poco less de sobrecarga.

  10. Un dictionary podría funcionar potencialmente, pero depende de la naturaleza de los datos, cómo lo está buscando y qué ancho tienen las filas. Si actualiza su pregunta con más información, editaré mi respuesta.

Si va a acceder al DataReader en un bucle, debe encontrar los índices fuera del bucle y luego usarlos dentro del bucle. También podría ser mejor usar los accesorios fuertemente tipados.

Bueno, si ya ha medido que el command ADO tarda solo un par de milisegundos, la otra posible causa de retraso es la cadena. Formato para build la connection

Intentaría eliminar la cadena. Formato que se llama para cada

 using(SqlConnection cn = new SqlConnection(sqlConnection.SqlConnectionString())) 

En cambio, suponiendo que SqlConnectionString está en una class separada, podrías escribir

 private static string conString = string.Empty; public static string SqlConnectionString() { if(conString == "") conString = string.Format("............"); return conString; } 

Por supuesto, un punto de reference podría descartar esto, pero estoy bastante seguro de que las operaciones de cadenas de ese tipo son costosas

Ver sus comentarios debajo de otra cosa que es muy importante agregar es la statement correcta de sus parameters. En lugar de usar AddWithValue (conveniente, pero con efectos secundarios complicados) declare sus parameters con el tamaño correcto

 using (SqlCommand sqlcmd = new SqlCommand("", sqlconn)) { sqlcmd.CommandType = System.Data.CommandType.StonetworkingProcedure; sqlcmd.CommandText = mySql.GetLCR(); SqlParameter p1 = new SqlParameter("@GatewayID", SqlDbType.NVarChar, 20).Value = GatewayID; SqlParameter p2 = new SqlParameter("@DialNumber", SqlDbType.NVarChar, 20).Value = dialnumber; sqlCmd.Parameters.AddRange(new SqlParameter[] {p1, p2}); sqlcmd.CommandTimeout = 1; sqlconn.Open(); ..... } 

AddWithValue no se recomienda cuando necesita exprimir cada milisegundos de performance. Este artículo muy útil explica por qué pasar una cadena con AddWithValue destruye los trabajos realizados por el optimizador de Sql Server. (En resumen, el optimizador calcula y almacena un plan de consulta para su command y, si recibe otro command idéntico, reutiliza el plan de consulta calculado. Pero si pasa una cadena con addwithvalue, el tamaño del parámetro se calcula cada vez que se base en la longitud real de la secuencia pasada. El optimizador no puede reutilizar el plan de consulta y vuelve a calcular y almacena uno nuevo)

"Necesito el performance más rápido que puedo get".

Si aún no lo ha hecho, revise los requisitos de su negocio y cómo su aplicación interactúa con su almacén de datos. Si ya has hecho esto, ignora esta publicación.


Ha sido mi experiencia que:

  1. El hecho de que incluso esté ejecutando una consulta SQL en una database significa que tiene un gasto: las consultas cuestan time / CPU / memory.
  2. Las consultas son aún más costosas si incluyen operaciones de escritura.

La forma más fácil de ahorrar dinero, ¡no es gastarlo! Así que busca forms de:

  1. evitar consultar la database en primer lugar
  2. garantizar que las consultas se ejecuten lo más rápido posible

ESTRATEGIAS

  • Asegúrese de estar utilizando los índices de la database correctamente.
  • Evite las consultas SQL que dan como resultado un escaneo completo de la tabla.
  • Use la agrupación de conexiones.
  • Si está insertando datos en la database, use cargas masivas.
  • Use el almacenamiento en caching cuando sea apropiado. Las opciones incluyen:
    • los resultados de almacenamiento en caching en la memory (es decir, RAM)
    • los resultados de almacenamiento en caching en el disco
    • Presentar los resultados antes de time y leerlos en lugar de ejecutar una nueva consulta
    • en lugar de extraer datos sin formatting con cada consulta, considere generar datos de resumen que podrían consultarse en su lugar.
  • Particiona tus datos. Esto puede ocurrir en varios niveles:
    • la mayoría de las bases de datos empresariales admiten estrategias de partición
    • al revisar su model de negocio, puede particionar sus datos en varias bases de datos (es decir, operaciones de lectura / escritura contra un DB, operaciones de escritura contra otro DB).
  • Revise el layout de su aplicación y luego mida los times de respuesta para confirmar que el cuello de la botella está de hecho donde usted cree que está.

CACHING TECHNOLOGIES

  • Asp.net – almacenamiento en caching
  • memcached
  • networkingis
  • etc.

DESCARGO DE RESPONSABILIDAD: No soy un administrador de database (DBA).

No creo que el problema sea el string.format

El resultado es:

108 ms para el formatting

1416 ms para el abierto

5176 ms para la ejecución

y todo es 6891 ms

ejecuta esto, ¡testing MUY simple!

 namespace ConsoleApplication1 { class Program { private static string DataIP; private static string Database; private static string IntanceID; static void Main(string[] args) { DataIP = @"FREDOU-PC\SQLEXPRESS"; Database = "testing"; IntanceID = "123"; int count = 0; System.Diagnostics.Stopwatch swWholeThing = System.Diagnostics.Stopwatch.StartNew(); System.Diagnostics.Stopwatch swFormat = new System.Diagnostics.Stopwatch(); System.Diagnostics.Stopwatch swOpen = new System.Diagnostics.Stopwatch(); System.Diagnostics.Stopwatch swExecute = new System.Diagnostics.Stopwatch(); for (int i = 0; i < 100000; ++i) { using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(SqlConnectionString(ref swFormat))) { using (System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand("dbo.counttable1", sqlconn)) { sqlcmd.CommandType = System.Data.CommandType.StonetworkingProcedure; sqlcmd.Parameters.Clear(); swOpen.Start(); sqlconn.Open(); swOpen.Stop(); swExecute.Start(); using (System.Data.SqlClient.SqlDataReader sqlDR = sqlcmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { if (sqlDR.Read()) count += sqlDR.GetInt32(0); } swExecute.Stop(); } } } swWholeThing.Stop(); System.Console.WriteLine("swFormat: " + swFormat.ElapsedMilliseconds); System.Console.WriteLine("swOpen: " + swOpen.ElapsedMilliseconds); System.Console.WriteLine("swExecute: " + swExecute.ElapsedMilliseconds); System.Console.WriteLine("swWholeThing: " + swWholeThing.ElapsedMilliseconds + " " + count); System.Console.ReadKey(); } public static string SqlConnectionString(ref System.Diagnostics.Stopwatch swFormat) { swFormat.Start(); var str = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;Application Name={2};Asynchronous Processing=true;MultipleActiveResultSets=true;Max Pool Size=524;Pooling=true;", DataIP, Database, IntanceID); swFormat.Stop(); return str; } } } 

dbo.counttable1 procedimiento almacenado:

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create PROCEDURE dbo.counttable1 AS BEGIN SET NOCOUNT ON; SELECT count(*) as cnt from dbo.Table_1 END GO 

dbo.table_1

 USE [testing] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Table_1]( [id] [int] NOT NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO 

contenido:

 insert into dbo.Table_1 (id) values (1) insert into dbo.Table_1 (id) values (2) insert into dbo.Table_1 (id) values (3) insert into dbo.Table_1 (id) values (4) insert into dbo.Table_1 (id) values (5) insert into dbo.Table_1 (id) values (6) insert into dbo.Table_1 (id) values (7) insert into dbo.Table_1 (id) values (8) insert into dbo.Table_1 (id) values (9) insert into dbo.Table_1 (id) values (10) 

Si maneja millones de loggings y accede a la database entre 500 y 10.000 veces por segundo. Recomendaré crear un file de controller (API) para la recuperación de datos y podrá encontrar herramientas de testing de carga para probar el performance de la API.

Al usar el performance de Memcache se puede boost, a continuación se muestra el paso para implementar el Memcache

  1. Debe crear un service de window que recuperará datos de la database y almacenará en Memcache en formatting JSON como (par de valor key).

  2. Para el website, cree un file de controller como una API que recuperará datos de Memcache y mostrará el resultado.

Lo he implementado en uno de mis proyectos, recupera miles de datos en milisegundos