I hope you can help me with this problem. I don't know what you will recommend, but I'm doing it this way.
The work that I am doing is comparing the records of a field of the table that is in SQLSERVER.
Example:
Table_Guide
Campo_nombre_imagen
-> This field returns the image name of a guide.
Fecha_guia
-> I use this field to send it as a parameter in my Store Procedure
.
Procedimiento Almacenado en SQLSERVER:
alter proc spSNMMan_Lista_Guia_x_mes_anno
@mes int,
@año int
as
select ltrim(g.GUIA_DET_NRO_REF) 'N° Guía'
from guia_detalle_nacional g WITH(NOLOCK)
where g.NWT_CON_IMG = 1 and
MONTH(guia_det_fch_emi)=@mes and
year(guia_det_fch_emi)=@año -------------> Yo se que esta tabla tiene más de 850000 registros.
In Visual Studio 2012 - C#
public DataTable Listar_Guias_x_parametros( int mes, int año)
{
DataTable dt = new DataTable();
CD_Conexion cnx = new CD_Conexion();
try
{
comando.Connection = conexion.AbrirConexion();
comando.CommandText = "spSNMMan_Lista_Guia_x_mes_anno";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@mes", mes);
comando.Parameters.AddWithValue("@año", año);
leer = comando.ExecuteReader();
tabla.Load(leer);
return tabla;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conexion.CerrarConexion();
}
}
And in my form I do this:
try
{
CN_Guias guias = new CN_Guias();
DateTime Hoy = DateTime.Today;
string fecha_actual = Hoy.ToString("dd/MM/yyyy");
string mes, año;
mes = txt_mes.Text;
año = txt_anno.Text;
string ruta = @"" + txt_ruta.Text + año + mes + "";
String[] dirs = System.IO.Directory.GetFiles(ruta);
for (int i = 0; i < dirs.Length; i++)
{
string nom_img = dirs[i];
string cadenaTexto = nom_img;
String[] resultado = cadenaTexto.Split('\\');
string ima_guia = resultado[resultado.Length - 1];
string cadenaTexto2 = ima_guia;
String[] resultado2 = cadenaTexto2.Split('.');
string ima_guia2 = resultado2[resultado2.Length - 2];
//*****************************************************************
//AQUI VA EL OTRO FOR
for (int o = 0; o < guias.Listar_Guias_x_parametros_prueba
(Convert.ToInt32(mes), Convert.ToInt32(año)).Rows.Count; o++)
//{
//for (int o = 0; o < guias.ListaPrueba().Rows.Count; o++)
{
//MessageBox.Show(Convert.ToString(o));
string x = guias.Listar_Guias_x_parametros_prueba
(Convert.ToInt32(mes), Convert.ToInt32(año)).Rows[o][1].ToString();
// guias.Listar_Guias_x_parametros_prueba
//(Convert.ToInt32(mes), Convert.ToInt32(año)).Rows[o][1].ToString();
}
//*****************************************************************
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
------------------------------------------------------------------------
I am getting a timeout error. But when I list another table with fewer records, it works.
I hope you can help me.
Thanks.
Having to filter by date means that the database engine always has to go through all the rows, there are some improvements that you can apply to the table so that the query is faster
If possible remove the time component (if you have it)
2018-10-01 10:23:31.123
change it to2018-10-01 00:00:00.000
. This will help in the next point.Add an index to the date column
By indexing the column, the engine will perform a search in a smaller space instead of the entire table, this in exchange for taking up more space for the table.
Make the query by range instead of extracting the parts of the date
In order to take advantage of the new index you have to change the way you do the query, compare as a datetime range instead of by month and year: