c# - SQL (Firebird) 查询太慢

Question

到目前为止，我编写的程序没有任何要求很高的功能，但后来我制作的功能需要花费很多时间。这是今年第五个月的开始，每天我的数据库都会填充 400 多个文档(每个文档平均有 5-7 个项目(表 ROBA 有 5-7 个新行)。所以在填充了一段时间后，计算所有内容需要越来越多的时间，我需要加快速度。

目前完成所有操作大约需要 60 秒。所以我想知道是否有任何方法可以加快速度，使用什么，寻找什么。这是我的功能:

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]);
     } 
   }
 }