SQL query (Firebird) is too slow

Question

So far, I have been building my program without any demanding functions, but then I made a function that takes a lot of time. This is the beginning of the fifth month of the year, and every day my database is filled with more than 400 documents (each document has an average of 5-7 elements (5-7 new lines in the table ROBA). Therefore, after some time, it takes more time to check in calculate everything and I need to speed it up.

At the moment, it takes about 60 seconds to finish everything. So I want to know if there is a way to speed it up, what to use, what to look for. Here is the function that I have:

private void ucitajStanje()
{
    DataTable dt1 = new DataTable();
    try
    {
        List<List_Int_Decimal> List_roba = new List<List_Int_Decimal>();

        using (FbConnection con = new FbConnection(connectionString_PrirucniMagacin))
        {
            con.Open();
            using (FbCommand cmd = new FbCommand("SELECT ROBAID, KOLICINA FROM STAVKA WHERE VRDOK = 0 AND BRDOK = 1", con))
            {
                FbDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    if (!(dr[0] is DBNull))
                    {
                        List_roba.Add(new List_Int_Decimal { ROBAID = Convert.ToInt16(dr[0]), kolicina = Convert.ToDecimal(dr[1]) });
                    }
                }
            }
            con.Close();
        }

        using (FbConnection con = new FbConnection(connectionString_Baza))
        {
            con.Open();

            //Selektuje stanje u magacinu iz komercijalnog
            using (FbDataAdapter da = new FbDataAdapter("SELECT ROBA.ROBAID, ROBA.KATBR, ROBA.NAZIV, ROBAUMAGACINU.STANJE AS STANJE_KOMERCIJALNO FROM ROBAUMAGACINU INNER JOIN ROBA ON ROBAUMAGACINU.ROBAID = ROBA.ROBAID WHERE MAGACINID = 12 AND VRSTA = 1", con))
            {
                da.Fill(dt1);
            }

            //FIRST SLOW QUERY


            //izracunava stanje kartice robe bez pocetnog stanja iz komercijalnog gde se dodaje na stanje
            using (FbCommand cmd = new FbCommand("SELECT ROBAID, SUM(KOLICINA) FROM STAVKA WHERE VRDOK = 16 AND ROBAID = @Robaid AND MAGACINID = 12 OR VRDOK = 18 AND ROBAID = @Robaid AND MAGACINID = 12 OR VRDOK = 22 AND ROBAID = @Robaid AND MAGACINID = 12 GROUP BY ROBAID", con))
                {
                    cmd.Parameters.Add("@Robaid", FbDbType.Integer);
                    foreach (var robaid in List_roba)
                {
                    cmd.Parameters["@Robaid"].Value = robaid.ROBAID;

                    FbDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if (!(dr[0] is DBNull))
                        {
                            int trenutnaRobaId = Convert.ToInt16(dr[0]);

                            var roba = List_roba.Where(r => r.ROBAID == trenutnaRobaId).FirstOrDefault();
                            if (roba != null)
                            { roba.kolicina = roba.kolicina + Convert.ToDecimal(dr[1]); }
                        }
                    }
                    dr.Close();
                }                    
            }

            //SECOND SLOW QUERY

            //izracunava stanje kartice robe bez pocetnog stanja iz komercijalnog gde se oduzima sa stanja
            using (FbCommand cmd = new FbCommand("SELECT ROBAID, SUM(KOLICINA) FROM STAVKA WHERE VRDOK = 15 AND ROBAID = @Robaid AND MAGACINID = 12 OR VRDOK = 17 AND ROBAID = @Robaid AND MAGACINID = 12 OR VRDOK = 19 AND ROBAID = @Robaid AND MAGACINID = 12 OR VRDOK = 34 AND ROBAID = @Robaid AND MAGACINID = 12 GROUP BY ROBAID", con))
            {
                cmd.Parameters.Add("@Robaid", FbDbType.Integer);
                foreach (var robaid in List_roba)
                {
                    cmd.Parameters["@Robaid"].Value = robaid.ROBAID;

                    FbDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if (!(dr[0] is DBNull))
                        {
                            int trenutnaRobaId = Convert.ToInt16(dr[0]);
                            var roba = List_roba.Where(r => r.ROBAID == trenutnaRobaId).FirstOrDefault();
                            if (roba != null)
                            { roba.kolicina = roba.kolicina - Convert.ToDecimal(dr[1]); }
                        }
                    }
                    dr.Close();
                }                    
            }
            con.Close();
        }
        DataTable dt2 = StaticFunctions.ToDataTable(List_roba);

        var dt = new[] { dt1, dt2 };
        DataTable mergedDT = StaticFunctions.MergeAll(dt, "ROBAID");

        dataGridView1.DataSource = mergedDT;

        dataGridView1.Columns["ROBAID"].Visible = false;
        dataGridView1.Columns["KATBR"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
        dataGridView1.Columns["NAZIV"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
        dataGridView1.Columns["STANJE_KOMERCIJALNO"].Visible = false;
        dataGridView1.Columns["robaid"].Visible = false;
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Answer 1

(.. sql ):

  //FIRST SLOW QUERY

  // Loop! 
  foreach (var robaid in List_roba) {
    ...
    // Antipattern: Many a time query calling 
    FbDataReader dr = cmd.ExecuteReader();
    ...
  }

:

 //FIRST SLOW QUERY (Hope, much faster now)

 //DONE: Keep SQL readable
 string sql = 
    @"SELECT ROBAID, 
             SUM(KOLICINA) 
        FROM STAVKA 
       WHERE MAGACINID = 12 AND
             VRDOK IN (16, 18, 22)
    GROUP BY ROBAID";

 using (FbCommand cmd = new FbCommand(sql, con)) {
   using (FbDataReader dr = cmd.ExecuteReader()) {
     while (dr.Read()) {
       if (dr.IsDBNull(0)) // <- Is it really possible for Id to be null?
         continue;

       int trenutnaRobaId = Convert.ToInt32(dr[0]);

       //TODO: you may want to turn List_roba into Dictionary_roba:
       // if (Dictionary_roba.TryGeValue(trenutnaRobaId, out roba)) 
       //   roba.kolicina = roba.kolicina + Convert.ToDecimal(dr[1]);
       var roba = List_roba
         .Where(r => r.ROBAID == trenutnaRobaId)
         .FirstOrDefault();

       if (roba != null)  
         roba.kolicina = roba.kolicina + Convert.ToDecimal(dr[1]);
     } 
   }
 }

: , List_roba (, , ),

 // In general case, if ROBAID can have duplicates
 var Dictionary_roba = List_roba
   .GroupBy(item => item.ROBAID)
   .ToDictionary(chunk => chunk.Key, 
                 chunk => chunk.First());

 // If ROBAID is unique:
 //var Dictionary_roba = List_roba
 //  .ToDictionary(item => item.ROBAID, item => item); 

 ...

 using (FbCommand cmd = new FbCommand(sql, con)) {
   using (FbDataReader dr = cmd.ExecuteReader()) {
     while (dr.Read()) {
       if (dr.IsDBNull(0)) // <- Is it really possible for Id to be null?
         continue;

       int trenutnaRobaId = Convert.ToInt32(dr[0]);

       // C# 7.0 Syntax - out var; 
       // if you don't have C# 7.0 you have to declare "roba" variable  
       if (Dictionary_roba.TryGeValue(trenutnaRobaId, out var roba))
         roba.kolicina = roba.kolicina + Convert.ToDecimal(dr[1]);
     } 
   }
 }