CODEWITHSUNDEEP
c#sqlperformancefirebird
Aleksa Ristic
Aleksa Ristic

Reputation: 2499

SQL (Firebird) query too slow

Until now I made my program without any demanding functions, but then I made function that takes a lot of time. It is start of the fifth month of the year, and every day my database gets populated with more than 400 documents (each document has 5-7 items on average (5-7 new lines to the table ROBA). So after some time populating, it takes more and more time to calculate everything and I need to speed it up.

For now it takes about 60 sec to finish up everything. So I want to know if there is any way to speed this up, what to use, what to look for. Here is function 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());
    }
}

Upvotes: 4

Views: 1095

Answers (1)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186823

Try avoiding to call query in a loop (i.e. performing the sql again and again):

  //FIRST SLOW QUERY

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

Instead, execute query just once, in one go:

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

Edit: you can increase performance further if you turn List_roba into a dictionary (or at least, create a temporary dictionary), e.g.

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

Upvotes: 4

Related Questions