I have to list approx 900,000 records from a database, and then do a tour in C# with a FOR, but since there are too many records, I get the timeout error. Is there any way to speed this up? This is my SP in SQL: The result is 870000 records.
create proc spSNMMan_SP_Listar_Guias_xMes_xAño
@mes int,
@año int
as
select (g.GUIA_DET_NRO_REF)'N° de Guía',
guia_det_fch_emi Fecha_Guia
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
This is my FOR in C#:
void Listar() {
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;
if (txt_ruta.Text == "")
{
//MessageBox.Show("Ingresar Ruta por favor.!!!");
MessageBox.Show("Ingresar Ruta por favor.!!!", "Ingresar Datos", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (txt_mes.Text == "")
{
MessageBox.Show("Ingrese el Mes por favor.!!!", "Ingresar Datos", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (txt_anno.Text == "")
{
MessageBox.Show("Ingrese el Año por favor.!!!", "Ingresar Datos", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
int cantidad_imagen_db = guias.Listar_Guias_xMes_xAño
(Convert.ToInt32(mes), Convert.ToInt32(año)).Rows.Count;
pgb_cargando.Visible = true;
pgb_cargando.Maximum = cantidad_imagen_db;
pgb_cargando.Step = 1;
pgb_cargando.Value = 0;
//INICIO FOR
btn_listar.Enabled = false;
for (int o = 0; o < cantidad_imagen_db; o++) //Recorre la cantidad de Imagenes de la DB
{
string nom_imagen_db = guias.Listar_Guias_xMes_xAño
(Convert.ToInt32(mes), Convert.ToInt32(año)).Rows[o][0].ToString().TrimEnd(' ');
var ruta_imagen = Path.Combine(txt_ruta.Text, nom_imagen_db + ".tif");
if (!File.Exists(ruta_imagen))
{
string f_guia = guias.Listar_Guias_xMes_xAño(Convert.ToInt32(mes),
Convert.ToInt32(año)).Rows[o][1].ToString();
objguias.InsertarGuiasValidadas(nom_imagen_db,
Convert.ToDateTime(f_guia), Convert.ToDateTime(fecha_actual), 0);}
}
MessageBox.Show("Se realizo la validación correctamente");
btn_listar.Enabled = true;
Limpiar();
//FIN FOR
//btn_validar.Enabled = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
If anyone has any ideas that could help me, I would appreciate sharing your knowledge.
Thanks.
The problem is that in each loop iteration you are calling the procedure
to take a single record, that's a lousy idea and you do it multiple times for both columns
You could use
foreach
it to iterate over each item in the list and not just its index.I adapted the code so you can see how it is solved using
You can also access the fields by their name, although I would advise you to define something more standard, for example change "Guide Number" for "GuideNumber" and "Guide_Date" only for "Guide Date"
As a complement to @Leandro Tuttini's answer, you can modify the query to not apply functions from the left side of the where. 20 seconds is a long time to return 900000 records and may be tunable. When you apply a function to a column, it becomes NOT SARGEABLE, which means that you cannot take advantage of the existing indexes, since you need to go through all the rows to be able to apply the function to the indicated column. Then review these optimization tips .
To remove the functions in your query, I see that you need the guides for a specific month and year. That is, if we need last month's guides, the date range would be from 10/01/2018 to 10/31/2018.
So, we could leave the query as follows:
This way we avoid applying a function to our where conditions and with an index on the field
guia_det_fch_emi
the performance should be remarkable. Now, where do we get the start date and the end date of the month?I am going to use the code that Leandro proposed, we will define both dates and send them as parameters:
And of course, the Listar_Guias_xMes_xYear method must be modified so that it receives dates instead of integers.