Forums Últimos mensajes - Powered by IBM
 
Tags Palabras claves

rendimiento a la hora de insertar registros en sql server desde c#

21/09/2007 - 10:04 por Sergio Palacín | Informe spam
Ayuda Hacer una pregunta
Hola.

Mi problema es el siguiente:

Tengo que recuperar registros de una base de datos paradox (con 100.000
registros), tratar los datos e insertarlos en tres tablas diferentes de
SQLServer.

Todo esto lo puedo hacer, pero me cuesta una dos horas hacerlo, lo cual es
totalmente inaceptable.

Primero abría y cerraba una conexión para cada insert, así que simplemente
abrí una conexión y realicé todos insert desde ella.
Como el rendimiento seguía siendo inaceptable, utilice preparecommand, que
creía que ayudaría mucho al rendimiento, pero no ha sido así.

¿Alguien tiene alguna idea? Adjunto el código empleado debajo.

private void importarClientes(string conParadox, string conSQL)

{

CambioTablaEventArgs cambioTablaArgs = new CambioTablaEventArgs();

cambioTablaArgs.nombreTabla = "CLIEMAQ";

cambioTablaArgs.filas = 97761; // Este valor lo he sacado haciendo un select
count(*) de la base de datos. Lo meto directamente porque

// el OLEDB casca con tantos registros

cambioTabla(this, cambioTablaArgs);

StringBuilder sb = new StringBuilder();

DataAccess.ExecuteNonQuery(conSQL, CommandType.Text, "SET IDENTITY_INSERT
[dbo].[MAQUINAS] ON");

string sql = "SELECT
codigo_cliente,Nombre,Direccion,Localidad,Cp,Telefono,Nif,Modelo,NumeroDeSerie,FechaInicioGarantia,"
+

"FechaContrato,NumeroContrato,Distribuidor,NumeroDeFactura,Telefono2,CODIGO_MARCA,fechaInicioGarantia,fechaDeBajaDeContrato
FROM CLIEMAQ";

OleDbDataReader dr = DataAccessDB2.ExecuteReader(conParadox,
CommandType.Text, sql);

SqlConnection con = new SqlConnection(conSQL);

con.Open();

//********************************PREPARAMOS EL COMANDO INSERTAR
CLIENTE***********************

SqlCommand insertCommandCliente = new SqlCommand();

insertCommandCliente.Connection = con;

insertCommandCliente.CommandType = CommandType.Text;

insertCommandCliente.CommandText = "INSERT INTO CLIENTES
(nombre,telefono,dni,movil) " +

"VALUES (@nombre,@telefono,@dni,@movil)";

insertCommandCliente.Parameters.Add("@nombre", SqlDbType.VarChar, 100);

insertCommandCliente.Parameters.Add("@telefono", SqlDbType.VarChar, 9);

insertCommandCliente.Parameters.Add("@dni", SqlDbType.VarChar, 9);

insertCommandCliente.Parameters.Add("@movil", SqlDbType.VarChar, 9);

insertCommandCliente.Prepare();

//************************************************************************************************

//********************************PREPARAMOS EL COMANDO OBTENER
ID_CLIENTE***********************

SqlCommand selectCommandCliente = new SqlCommand();

selectCommandCliente.Connection = con;

selectCommandCliente.CommandType = CommandType.Text;

selectCommandCliente.CommandText = "select @@identity";

selectCommandCliente.Prepare();

//************************************************************************************************

//********************************PREPARAMOS EL COMANDO COMPROBAR
CLIENTE***********************


SqlCommand comprobarCommandCliente = new SqlCommand();

comprobarCommandCliente.Connection = con;

comprobarCommandCliente.CommandType = CommandType.Text;

comprobarCommandCliente.CommandText = "SELECT ID_CLIENTE FROM CLIENTES WHERE
NOMBRE = @nombre";

comprobarCommandCliente.Parameters.Add("@nombre", SqlDbType.VarChar, 100);

comprobarCommandCliente.Prepare();

//************************************************************************************************





//********************************PREPARAMOS EL COMANDO INSERTAR
DIRECCION***********************

SqlCommand insertCommandDireccion = new SqlCommand();

insertCommandDireccion.Connection = con;

insertCommandDireccion.CommandType = CommandType.Text;

insertCommandDireccion.CommandText = "INSERT INTO
DIRECCIONES(id_cliente,direccion,localidad,codigoPostal) " +

"VALUES (@id_cliente,@direccion,@localidad,@codigoPostal)";

insertCommandDireccion.Parameters.Add("@id_cliente", SqlDbType.Int, 4);

insertCommandDireccion.Parameters.Add("@direccion", SqlDbType.VarChar, 500);

insertCommandDireccion.Parameters.Add("@localidad", SqlDbType.VarChar, 100);

insertCommandDireccion.Parameters.Add("@codigopostal", SqlDbType.Int, 4);

insertCommandDireccion.Prepare();

//************************************************************************************************

//********************************PREPARAMOS EL COMANDO OBTENER
ID_DIRECCION***********************

SqlCommand selectCommandDireccion = new SqlCommand();

selectCommandDireccion.Connection = con;

selectCommandDireccion.CommandType = CommandType.Text;

selectCommandDireccion.CommandText = " select @@identity";

selectCommandDireccion.Prepare();

//************************************************************************************************

//********************************PREPARAMOS EL COMANDO COMPROBAR
DIREccion***********************

SqlCommand comprobarCommandDireccion = new SqlCommand();

comprobarCommandDireccion.Connection = con;

comprobarCommandDireccion.CommandType = CommandType.Text;

comprobarCommandDireccion.CommandText = "SELECT ID_DIRECCION FROM
DIRECCIONES WHERE DIRECCION = @direccion AND LOCALIDAD = @localidad";

comprobarCommandDireccion.Parameters.Add("@direccion", SqlDbType.VarChar,
500);

comprobarCommandDireccion.Parameters.Add("@localidad", SqlDbType.VarChar,
100);

comprobarCommandDireccion.Prepare();

//************************************************************************************************



//********************************PREPARAMOS EL COMANDO INSERTAR
MAQUINA***********************

SqlCommand insertCommandMaquina = new SqlCommand();

insertCommandMaquina.Connection = con;

insertCommandMaquina.CommandType = CommandType.Text;

insertCommandMaquina.CommandText = "INSERT INTO
MAQUINAS(id_cliente,id_direccion,id_marca,modelo,serie,fechaGarantia," +

"fechaContrato,numContrato,distribuidor,numFactura,fechaBajaContrato,id_maquina)
VALUES (@id_cliente,@id_direccion,@id_marca,@modelo," +

"@serie,@fechaGarantia,@fechaContrato,@numContrato,@distribuidor,@numFactura,@fechaBajaContrato,@id_maquina)";

insertCommandMaquina.Parameters.Add("@id_cliente", SqlDbType.Int, 4);

insertCommandMaquina.Parameters.Add("@id_direccion", SqlDbType.Int, 4);

insertCommandMaquina.Parameters.Add("@id_marca", SqlDbType.Int, 4);

insertCommandMaquina.Parameters.Add("@modelo", SqlDbType.VarChar, 200);

insertCommandMaquina.Parameters.Add("@serie", SqlDbType.VarChar, 200);

insertCommandMaquina.Parameters.Add("@fechaGarantia", SqlDbType.DateTime,
8);

insertCommandMaquina.Parameters.Add("@fechaContrato", SqlDbType.DateTime,
8);

insertCommandMaquina.Parameters.Add("@numContrato", SqlDbType.VarChar, 10);

insertCommandMaquina.Parameters.Add("@distribuidor", SqlDbType.VarChar,
100);

insertCommandMaquina.Parameters.Add("@numFactura", SqlDbType.VarChar, 10);

insertCommandMaquina.Parameters.Add("@fechaBajaContrato",
SqlDbType.DateTime, 8);

insertCommandMaquina.Parameters.Add("@id_maquina", SqlDbType.Int, 4);

insertCommandMaquina.Prepare();


//************************************************************************************************

int idDireccion;

int idCliente;

bool first = true;

while (dr.Read())

{

try

{

registrosTabla++;

idCliente = comprobarCliente(Convert.ToString(dr["nombre"]),
comprobarCommandCliente);

if (idCliente == 0)

{

if (dr["nif"] is DBNull)

{

insertCommandCliente.Parameters["@dni"].Value = DBNull.Value;

}

else

{

string dni = System.Convert.ToString(dr["nif"]);

dni = dni.Replace("-", "");

dni = dni.Replace(" ", "");

if (dni.Length > 9)

{

sb.Append("Error en el campo dni para el codigo_cliente = " +
Convert.ToString(dr["codigo_cliente"]) +

" dni: " + dni + "");

dni = dni.Substring(dni.Length - 9, 9);

}

insertCommandCliente.Parameters["@dni"].Value = dni;

}

if (dr["nombre"] is DBNull)

{

insertCommandCliente.Parameters["@nombre"].Value = DBNull.Value;

}

else

{

insertCommandCliente.Parameters["@nombre"].Value =
System.Convert.ToString(dr["nombre"]);

}

if (dr["telefono"] is DBNull)

{

insertCommandCliente.Parameters["@telefono"].Value = DBNull.Value;

}

else

{

string telefono = System.Convert.ToString(dr["telefono"]);

telefono = telefono.Replace("-", "");

if (telefono.Length > 9)

{

sb.Append("Error en el campo teléfono para el codigo_cliente = " +
Convert.ToString(dr["codigo_cliente"]) +

" teléfono: " + telefono + "");

telefono = telefono.Substring(telefono.Length - 9, 9);

}

insertCommandCliente.Parameters["@telefono"].Value = telefono;

}

if (dr["telefono2"] is DBNull)

{

insertCommandCliente.Parameters["@movil"].Value = DBNull.Value;

}

else

{

string telefono = System.Convert.ToString(dr["telefono2"]);

telefono = telefono.Replace("-", "");

if (telefono.Length > 9)

{

sb.Append("Error en el campo móvil para el codigo_cliente = " +
Convert.ToString(dr["codigo_cliente"]) +

" móvil: " + telefono + "");

telefono = telefono.Substring(telefono.Length - 9, 9);

}

insertCommandCliente.Parameters["@movil"].Value = telefono;

}

insertCommandCliente.ExecuteNonQuery();

idCliente = Convert.ToInt32(selectCommandCliente.ExecuteScalar());

}



idDireccion = comprobarDireccion(Convert.ToString(dr["direccion"]),
Convert.ToString(dr["localidad"]), comprobarCommandDireccion); ;

if (idDireccion == 0)

{

insertCommandDireccion.Parameters[0].Value = idCliente;

if (dr["direccion"] is DBNull)

{

insertCommandDireccion.Parameters[1].Value = DBNull.Value;

}

else

{

insertCommandDireccion.Parameters[1].Value =
System.Convert.ToString(dr["direccion"]);

}

if (dr["localidad"] is DBNull)

{

insertCommandDireccion.Parameters[2].Value = DBNull.Value;

}

else

{

insertCommandDireccion.Parameters[2].Value =
System.Convert.ToString(dr["localidad"]);

}

if (dr["cp"] is DBNull)

{

insertCommandDireccion.Parameters[3].Value = DBNull.Value;

}

else

{

insertCommandDireccion.Parameters[3].Value =
System.Convert.ToInt32(dr["cp"]);

}



insertCommandDireccion.ExecuteNonQuery();



idDireccion = Convert.ToInt32(selectCommandDireccion.ExecuteScalar());

}

insertCommandMaquina.Parameters["@id_cliente"].Value = idCliente;

insertCommandMaquina.Parameters["@id_direccion"].Value = idDireccion;

if (dr["CODIGO_MARCA"] is DBNull)

{

insertCommandMaquina.Parameters["@id_marca"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@id_marca"].Value =
System.Convert.ToInt32(dr["CODIGO_MARCA"]);

}

if (dr["modelo"] is DBNull)

{

insertCommandMaquina.Parameters["@modelo"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@modelo"].Value =
System.Convert.ToString(dr["modelo"]);

}



if (dr["NumeroDeSerie"] is DBNull)

{

insertCommandMaquina.Parameters["@serie"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@serie"].Value =
System.Convert.ToString(dr["NumeroDeSerie"]);

}

if (dr["fechaInicioGarantia"] is DBNull)

{

insertCommandMaquina.Parameters["@fechaGarantia"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@fechaGarantia"].Value =
System.Convert.ToDateTime(dr["fechaInicioGarantia"]);

}

if (dr["fechaContrato"] is DBNull)

{

insertCommandMaquina.Parameters["@fechaContrato"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@fechaContrato"].Value =
System.Convert.ToDateTime(dr["fechaContrato"]);

}

if (dr["NumeroContrato"] is DBNull)

{

insertCommandMaquina.Parameters["@numContrato"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@numContrato"].Value =
System.Convert.ToString(dr["NumeroContrato"]);

}



if (dr["distribuidor"] is DBNull)

{

insertCommandMaquina.Parameters["@distribuidor"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@distribuidor"].Value =
System.Convert.ToString(dr["distribuidor"]);

}



if (dr["NumeroDeFactura"] is DBNull)

{

insertCommandMaquina.Parameters["@numFactura"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@numFactura"].Value =
System.Convert.ToString(dr["NumeroDeFactura"]);

}



if (dr["fechaDeBajaDeContrato"] is DBNull)

{

insertCommandMaquina.Parameters["@fechaBajaContrato"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@fechaBajaContrato"].Value =
System.Convert.ToDateTime(dr["fechaDeBajaDeContrato"]);

}

if (dr["codigo_cliente"] is DBNull)

{

insertCommandMaquina.Parameters["@id_maquina"].Value = DBNull.Value;

}

else

{

insertCommandMaquina.Parameters["@id_maquina"].Value =
System.Convert.ToInt32(dr["codigo_cliente"]);

}

insertCommandMaquina.ExecuteNonQuery();

first = false;

}

catch (Exception ex2)

{

sb.Append("Error al importar el registro de clientes de codigo_cliente: "

+ Convert.ToString(dr["codigo_cliente"]) + " mensaje: " + ex2.Message);

}

CambioRegistroEventArgs args = new CambioRegistroEventArgs();

args.fila = registrosTabla;

cambioRegistro(this, args);

}

con.Close();

if (sb.ToString() != "")

{

if (!File.Exists(pathLog))

{

File.Create(pathLog);

}

StreamReader sr = new StreamReader(pathLog);

string s = sr.ReadToEnd();

sb = sb.Insert(0, s);

sr.Close();

StreamWriter sw = File.CreateText(pathLog);

sw.Write(sb.ToString());

sw.Flush();

sw.Close();

}

DataAccess.ExecuteNonQuery(conSQL, CommandType.Text, "SET IDENTITY_INSERT
[dbo].[MAQUINAS] OFF");

registrosTabla = 0;

}
email Siga el debateRespuesta 5 respuestasRespuesta Tengo una respuesta

Preguntas similare

Leer las respuestas

#1 Alberto Poblacion
21/09/2007 - 10:26 | Informe spam
"Sergio Palacín" wrote in message
news:ucDwqZC$
Mi problema es el siguiente:
[...] insertarlos en tres tablas diferentes de SQLServer.
[...] rendimiento seguía siendo inaceptable
[...]
¿Alguien tiene alguna idea? Adjunto el código empleado debajo.



Así a simple vista no encuentro ningún inconveniente en el código. Para
comprobar si el problema está en el código cliente, o en el rendimiento de
las inserciones en el propio Sql Server, puedes usar el Sql Profiler. Activa
el profiler (se llama "analizador" en la version española), configurando una
traza que te capture las sentencias que le envías junto con sus estadísticas
de tiempo de ejecución y acceso a páginas de datos. Ejecuta tu programa, y
luego ve a la captura y examina las sentencias capturadas (a ver si ves algo
raro), asi como las estadísticas correspondientes. Si el tiempo consumido
por la ejecución de las sentencias es casi igual al tiempo que tarda tu
programa en ejecutarse, entonces no hay nada más que puedas hacer desde el
lado cliente para mejorarlo, y tendrás que centrarte en optimizar la
configuración del servidor.
Respuesta Responder a este mensaje
#2 Sergio Palacín
21/09/2007 - 11:00 | Informe spam
Muchas gracias por contestar. He monitorizado los procesos como me dices, y
me sale que los inserts tienen una duración de unos 100 ms. ¿Esto no es muy
elevado?

Además, había oido comentarios que decían que para agilizar las
inserciones, se indicaba a la base de datos el modo de hacerlas,ésta
compilaba el plan de actuación y las inserciones posteriormente eran casi
instantáneas. ¿alguien sabe algo de esto o es ciencia-ficción?

Gracias otra vez.


"Alberto Poblacion"
escribió en el mensaje news:uYSdjkC$
"Sergio Palacín" wrote in message
news:ucDwqZC$
Mi problema es el siguiente:
[...] insertarlos en tres tablas diferentes de SQLServer.
[...] rendimiento seguía siendo inaceptable
[...]
¿Alguien tiene alguna idea? Adjunto el código empleado debajo.



Así a simple vista no encuentro ningún inconveniente en el código. Para
comprobar si el problema está en el código cliente, o en el rendimiento de
las inserciones en el propio Sql Server, puedes usar el Sql Profiler.
Activa el profiler (se llama "analizador" en la version española),
configurando una traza que te capture las sentencias que le envías junto
con sus estadísticas de tiempo de ejecución y acceso a páginas de datos.
Ejecuta tu programa, y luego ve a la captura y examina las sentencias
capturadas (a ver si ves algo raro), asi como las estadísticas
correspondientes. Si el tiempo consumido por la ejecución de las
sentencias es casi igual al tiempo que tarda tu programa en ejecutarse,
entonces no hay nada más que puedas hacer desde el lado cliente para
mejorarlo, y tendrás que centrarte en optimizar la configuración del
servidor.


Respuesta Responder a este mensaje
#3 Alberto Poblacion
21/09/2007 - 12:45 | Informe spam
"Sergio Palacín" wrote in message
news:%23jy2E5C$
Muchas gracias por contestar. He monitorizado los procesos como me dices,
y me sale que los inserts tienen una duración de unos 100 ms. ¿Esto no es
muy elevado?



Pues 100 ms multiplicado por 100.000 registros son 10.000 segundos, es
decir, cerca de tres horas. Es un tiempo muy elevado para una simple
inserción. Tendrías que ver si se puede optimizar en el lado de Sql Server.

Además, había oido comentarios que decían que para agilizar las
inserciones, se indicaba a la base de datos el modo de hacerlas,ésta
compilaba el plan de actuación y las inserciones posteriormente eran casi
instantáneas. ¿alguien sabe algo de esto o es ciencia-ficción?



¿Qué versión de Sql Server usas? Si es un 2000 o 2005, el plan de
actuación se guarda en cache, y las sucesivas sentencias que siguen el mismo
modelo que la primera usan el mismo plan, sin recalcularse. Así que no
tienes que hacer nada en especial.

Comprueba si existen muchos índices creados sobre las tablas en las que
estás haciendo la inserción masiva de datos. Cada vez que añades un registro
tiene que añadirse a todos los índices. Una forma de acelerar la inserción
es eliminar todos los índices, insertar los registros, y luego volver a
crear los índices. Si esto no es aceptable (por ejemplo, debido a que se
están realizando consultas sobre esas tablas simultaneamente a la inserción,
o a que las tablas tienen muchos más registros que los insertados y se tarda
demasiado en reconstruir los índices), entonces conviene optimizar las
inserciones en los índices poniendo un valor pequeño en el FILL_FACTOR y
activando el PAD_INDEX, para que no sean demasiado frecuentes las divisiones
de páginas. También es bueno mover los índices a otros discos separados, en
caso de que el cuello de botella esté en la grabación de índices en disco.
Otra optimización que se puede hacer es mover el .ldf (el log de
transacciones) a un disco separado dedicado exclusivamente a ese fin, para
que la cabeza de grabación no tenga que moverse de sitio mientras se
"loguean" las inserciones.
Más trucos: repartir la tabla de datos y sus índices entre varios
discos, usando tablas particionadas si tienes Sql 2005, o Vistas
particionadas si es una versión anterior.
Antes de tocar nada, conviene examinar los contadores de Sql Server en
el Performance Monitor de Windows, y comprobar si el cuello de botella está
efectivamente en el acceso a disco (si durante el proceso tienes una media
de más de 2 peticiones en cola por cada eje), o si la memoria para los
buffers de páginas del sql server es demasiado pequeña (un valor pequeño en
los "cache hits" - si todo va bien este valor debería estar cerca del 100%).
Si ocurre esto último, es preferible ampliar la memoria del servidor.

En cualquier caso, para optimizar el servidor Sql, es mejor preguntar en
los grupos de Sql, más que en el de C#.
Respuesta Responder a este mensaje
#4 LegioFox
21/09/2007 - 12:54 | Informe spam
Hola Sergio :

Yo tuve el mismo problema desde una aplicación de VisualFoxpro, y la
solucion que encontre fue utilizar
SQLXMLBulkLoad. Te cuento la estrategia, primero genero un XML con los
registros que quiero insertar y luego uso el SQLXMLBulkLoad. Te puedo decir
que para incorporar 56.000 registros lo hace en 20 segundos.
No son registros con muchas columnas. Creo que es un sistema bastante rápido
para migrar datos.

Espero que te sea de ayuda.

Un saludo
Respuesta Responder a este mensaje
#5 Sergio Palacín
21/09/2007 - 13:07 | Informe spam
Muchas gracias a los dos.

El problema es que yo no puedo tocar nada del servidor, pues se comparte
para mi aplicación y para muchas otras y no tengo permisos para tocar nada.
Así que voy a intentar lo que me propone LegioFox.

Gracias y ya os contaré.


"Sergio Palacín" escribió en el mensaje
news:ucDwqZC$
Hola.

Mi problema es el siguiente:

Tengo que recuperar registros de una base de datos paradox (con 100.000
registros), tratar los datos e insertarlos en tres tablas diferentes de
SQLServer.

Todo esto lo puedo hacer, pero me cuesta una dos horas hacerlo, lo cual es
totalmente inaceptable.

Primero abría y cerraba una conexión para cada insert, así que simplemente
abrí una conexión y realicé todos insert desde ella.
Como el rendimiento seguía siendo inaceptable, utilice preparecommand, que
creía que ayudaría mucho al rendimiento, pero no ha sido así.

¿Alguien tiene alguna idea?
email Siga el debate Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaRespuesta Tengo una respuesta
Search Busqueda sugerida