Optimize a list of SQLServer in C #

As a complement to the answer of @Leandro Tuttini, you can modify the query to not apply functions on the left side of the where. 20 seconds is a long time to return 900,000 records and can be optimized. When you apply a function to a column, it becomes NOT SARGEABLE, which means that you can not take advantage of the indices that you have, since you need to go through all the rows to be able to apply the function in the indicated column. So check out these optimization tips .

To eliminate the functions in your query, I see that you need the guides of a specific month and year. That is to say, if we need the guides of the last month, the range of dates would contemplate from 01/10/2018 to 10/31/2018.

Then, we could leave the query in the following way:

create proc spSNMMan_SP_Listar_Guias_xMes_xAño 
@fechaInicio date,
@fechaFin date
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
guia_det_fch_emi >=@fechaInicio and
guia_det_fch_emi <=@fechaFin

In this way, we avoid applying a function to our conditions in the where and with an index in the field guia_det_fch_emi the performance should be remarkable. Now, where do we get the start date and the end of the month date?

I will use the code proposed by Leandro, we will define both dates and send them as parameters:

string fechaInicio = "1/"+mes.ToString()+"/"+año.ToString();
DateTime fechaPrimerDia = Convert.ToDateTime(fechaInicio);
DateTime fechaUltimoDia = fechaPrimerDia.AddMonths(1).AddDays(-1);
DataTable dtImagenes = guias.Listar_Guias_xMes_xAño(fechaPrimerDia,fechaUltimoDia);

And of course, the method Listar_Guias_xMes_xYear must be modified so that it receives dates instead of integers.

The problem is that in each loop iteration you are invoking the procedure

string nom_imagen_db = guias.Listar_Guias_xMes_xAño
                (Convert.ToInt32(mes), Convert.ToInt32(año)).Rows[o][0].ToString()

to take a single record, that is a bad idea and you make them several times for both columns

You could use the foreach to iterate each item in the list and not just its index

void Listar() {
    try
    {
        CN_Guias guias = new CN_Guias();

        string mes = txt_mes.Text;
        string 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);
            return;
        }
        if (txt_mes.Text == "")
        {
            MessageBox.Show("Ingrese el Mes por favor.!!!", "Ingresar Datos", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return;
        }
        if (txt_anno.Text == "")
        {
            MessageBox.Show("Ingrese el Año por favor.!!!", "Ingresar Datos", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return;
        }


        DataTable dtImagenes = guias.Listar_Guias_xMes_xAño(Convert.ToInt32(mes), Convert.ToInt32(año));

        pgb_cargando.Visible = true;
        pgb_cargando.Maximum = cantidad_imagen_db;
        pgb_cargando.Step = 1;
        pgb_cargando.Value = 0;

        btn_listar.Enabled = false;

        foreach (DataRow row in dtImagenes.Rows)
        {
            string nom_imagen_db = row["N° de Guía"].ToString().TrimEnd(' ');

            var ruta_imagen = Path.Combine(txt_ruta.Text, nom_imagen_db + ".tif");
            if (!File.Exists(ruta_imagen))
            {
                string f_guia = row["Fecha_Guia"].ToString();
                objguias.InsertarGuiasValidadas(nom_imagen_db, 
                                                Convert.ToDateTime(f_guia), 
                                                DateTime.Now, 0);
            }
        }

        MessageBox.Show("Se realizo la validación correctamente");
        btn_listar.Enabled = true;

        Limpiar();

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

Adapt the code so that you can see how it is solved using

foreach (DataRow row in dtImagenes.Rows){ ..

You can also access the fields by their name, although I would advise you to define something standard, for example, change "Guide No." to "NroGuia" and "Date_Guide" only to "DateGuide"