SQL Query runs too slow

Question

I have this command

using (FbCommand cmd = new FbCommand(@"SELECT MIN(STAVKA.TREN_STANJE) FROM STAVKA RIGHT OUTER JOIN DOKUMENT ON STAVKA.VRDOK = DOKUMENT.VRDOK AND STAVKA.BRDOK = DOKUMENT.BRDOK WHERE(STAVKA.MAGACINID = @MID) AND(STAVKA.ROBAID = @RID) AND(DOKUMENT.FLAG < 2)", con))
{
    cmd.Parameters.AddWithValue("@MID", magacinId);
    cmd.Parameters.Add("@RID", FbDbType.Integer);

    foreach(Int_Double stavka in stavkePocetnogStanjaSaKolicinama)
    {
        cmd.Parameters["@RID"].Value = stavka._int;

        using (FbDataReader dr = cmd.ExecuteReader())
        {

            if (dr.Read())
            {
                double v = (dr[0] is DBNull) ? 0 : Convert.ToDouble(dr[0]);
                double t = stavka._double;
                if (smanjuj && !povecavaj)
                {
                    if (v >= 0)
                        stavka._double -= v;
                }
                else if (smanjuj && povecavaj)
                    stavka._double -= v;
                else if (!smanjuj && povecavaj)
                {
                    if (v < 0)
                        stavka._double -= v;
                }
                else
                {
                    MessageBox.Show("Doslo je do greske! - SvediPocetnoStanjeNaMinimum");
                    return;
                }
            }
        }
    }
}

Where the SQL statement is:

SELECT MIN(STAVKA.TREN_STANJE) 
  FROM STAVKA 
  RIGHT OUTER JOIN DOKUMENT 
    ON STAVKA.VRDOK = DOKUMENT.VRDOK AND STAVKA.BRDOK = DOKUMENT.BRDOK 
  WHERE (STAVKA.MAGACINID = @MID) 
    AND (STAVKA.ROBAID = @RID) 
    AND (DOKUMENT.FLAG < 2)

Problem is that using (FbDataReader dr = cmd.ExecuteReader()) takes about 3-4 seconds to run which would be okay if there are few times to run it but there is around 4k times needed to run so that is 16k seconds (4hrs)

How does these two table works is:

I have one document (DOKUMENT) which is one row and it has multiple items (STAVKA) which are connected to DOKUMENT (have document type (VRDOK) and document number (BRDOK)).

Now for each item (STAVKA.ROBAID) i select (STAVKA.TREN_STANJE) but DOCUMENT.FLAG which contains that STAVKA must have flag < 2

Example:

STAVKA TABLE
STAVKAID     | VRDOK    | BRDOK    | TREN_STANJE    | ROBAID  |
1            | 1        | 10       | 6              | 3       |
2            | 1        | 14       | 12             | 3       |
3            | 1        | 18       | 3              | 3       |
4            | 1        | 21       | 8              | 3       |
5            | 1        | 23       | 7              | 3       |


DOKUMENT TABLE
VRDOK    | BRDOK    | FLAG
1        | 10       | 1
1        | 14       | 3
1        | 18       | 1
1        | 21       | 1
1        | 23       | 4

So when i run SQL statement it need to select MIN(TREN_STANJE) from STAVKA WHERE ROBAID = @RID (3 in this case) BUT FLAG of that document (as you can see same columns in DOKUMENT and STAVKA) must be < 2

How could i speed this up?

Structure of STAVKA table is:

CREATE TABLE "STAVKA" 
(
  "STAVKAID"     INTEGER NOT NULL,
  "VRDOK"    SMALLINT NOT NULL,
  "BRDOK"    INTEGER NOT NULL,
  "MAGACINID"    SMALLINT NOT NULL,
  "ROBAID"   INTEGER NOT NULL,
  "VRSTA"    SMALLINT,
  "NAZIV"    VARCHAR(50),
  "NABCENSAPOR"  NUMERIC(15,4),
  "FAKTURNACENA"     NUMERIC(15,4),
  "NABCENABT"    DOUBLE PRECISION,
  "TROSKOVI"     NUMERIC(15,4),
  "NABAVNACENA"  NUMERIC(15,4) NOT NULL,
  "PRODCENABP"   NUMERIC(15,4) NOT NULL,
  "KOREKCIJA"    DOUBLE PRECISION,
  "PRODAJNACENA"     NUMERIC(15,2) NOT NULL,
  "DEVIZNACENA"  NUMERIC(15,4) NOT NULL,
  "DEVPRODCENA"  NUMERIC(15,4),
  "KOLICINA"     NUMERIC(15,3) NOT NULL,
  "NIVKOL"   NUMERIC(15,3) NOT NULL,
  "TARIFAID"     VARCHAR(3),
  "IMAPOREZ"     SMALLINT,
  "POREZ"    NUMERIC(15,2) NOT NULL,
  "RABAT"    NUMERIC(15,2) NOT NULL,
  "MARZA"    NUMERIC(15,2) NOT NULL,
  "TAKSA"    NUMERIC(15,4),
  "AKCIZA"   NUMERIC(15,2),
  "PROSNAB"  NUMERIC(15,4) NOT NULL,
  "PRECENA"  NUMERIC(15,4) NOT NULL,
  "PRENAB"   NUMERIC(15,4) NOT NULL,
  "PROSPROD"     NUMERIC(15,4) NOT NULL,
  "MTID"     VARCHAR(10),
  "PT"   CHAR(1) NOT NULL,
  "ZVEZDICA"     VARCHAR(6),
  "TREN_STANJE"  NUMERIC(15,3),
  "POREZ_ULAZ"   NUMERIC(15,2) NOT NULL,
  "SDATUM"   DATE,
  "DEVNABCENA"   NUMERIC(15,4),
  "POREZ_IZ"     NUMERIC(15,2) NOT NULL,
  "X4"   NUMERIC(15,3),
  "Y4"   NUMERIC(15,3),
  "Z4"   NUMERIC(15,3),
  "CENAPOAJM"    NUMERIC(15,2),
  "KGID"     INTEGER,
CONSTRAINT "STAVKAPRIMARYKEY" PRIMARY KEY ("STAVKAID")
);

Structure of DOKUMENT is:

CREATE TABLE "DOKUMENT" 
(
  "VRDOK"    SMALLINT NOT NULL,
  "BRDOK"    INTEGER NOT NULL,
  "INTBROJ"  VARCHAR(15),
  "KODDOK"   SMALLINT NOT NULL,
  "FLAG"     SMALLINT,
  "DATUM"    DATE NOT NULL,
  "LINKED"   VARCHAR(10),
  "MAGACINID"    SMALLINT NOT NULL,
  "PPID"     INTEGER,
  "FAKTDOBIZV"   VARCHAR(15),
  "PLACEN"   SMALLINT NOT NULL,
  "DATROKA"  DATE,
  "NUID"     SMALLINT,
  "NRID"     SMALLINT,
  "VALUTA"   VARCHAR(3) NOT NULL,
  "KURS"     NUMERIC(15,4) NOT NULL,
  "ZAPID"    SMALLINT NOT NULL,
  "UPLACENO"     NUMERIC(15,2) NOT NULL,
  "TROSKOVI"     NUMERIC(15,2) NOT NULL,
  "DUGUJE"   NUMERIC(15,2) NOT NULL,
  "POTRAZUJE"    NUMERIC(15,2) NOT NULL,
  "POPUST"   NUMERIC(15,2) NOT NULL,
  "RAZLIKA"  NUMERIC(15,2),
  "DODPOREZ"     NUMERIC(15,2),
  "POREZ"    NUMERIC(15,2),
  "PRODVREDBP"   NUMERIC(15,2) NOT NULL,
  "KUPAC"    VARCHAR(50),
  "OPISUPL"  VARCHAR(30),
  "VRDOKIN"  SMALLINT,
  "BRDOKIN"  INTEGER,
  "VRDOKOUT"     SMALLINT,
  "BRDOKOUT"     INTEGER,
  "MAGID"    SMALLINT,
  "POPUST1DANA"  INTEGER,
  "POPUST1PROCENAT"  NUMERIC(15,2),
  "POPUST2DANA"  INTEGER,
  "POPUST2PROCENAT"  NUMERIC(15,2),
  "POPUST3DANA"  INTEGER,
  "POZNABROJ"    CHAR(2),
  "POPUST3PROCENAT"  NUMERIC(15,2),
  "KONTRBROJ"    CHAR(2),
  "MTID"     VARCHAR(10),
  "REFID"    SMALLINT,
  "STATUS"   SMALLINT,
  "PPO"  SMALLINT,
  "PRENETI_POREZ"    NUMERIC(15,2),
  "AKVRDOK"  SMALLINT,
  "AKBRDOK"  INTEGER,
  "ALIASIZ"  SMALLINT,
  "ALIASU"   SMALLINT,
  "PREVOZROBE"   SMALLINT,
  "DATUM_PDV"    DATE,
  "NDID"     SMALLINT,
  "NABVREDNOST"  NUMERIC(15,2),
  "SAT_START"    VARCHAR(8),
  "SAT_END"  VARCHAR(8),
  "KNJIZNAOZ"    NUMERIC(15,2),
  "POVRATNICE"   NUMERIC(15,2),
  "SINHRO"   SMALLINT,
  "STORNO"   NUMERIC(15,2),
  "SMENAID"  SMALLINT NOT NULL,
  "POR_ODB"  NUMERIC(15,2),
CONSTRAINT "DOKUMENTPRIMARYKEY" PRIMARY KEY ("VRDOK", "BRDOK")
);

You don't even have any parameters in that SQL string. Is this really the exact code you are using? — Crowcoder, Jun 08 '18 at 10:56
Without knowing the exact table structure (e.g. what data types are the columns, how many rows are in each table) we can merely *guess* why the query is slow. — Thomas Flinkow, Jun 08 '18 at 10:58
Additionally, `smanjuj` and `povecavaj` are difficult to understand variable names, and if we don't know what they mean we cannot semantically understand your code and help optimize it. — Thomas Flinkow, Jun 08 '18 at 11:00
Based on what it looks like you are doing, I might try creating a temp table of `stavka._int` and do a single query by joining on it instead of a query execution for each. — Crowcoder, Jun 08 '18 at 11:03
What @Crowcoder mentioned is exactly the problem right now. It *looks like* you are doing something, but I suspect a so-called [XY problem](https://en.wikipedia.org/wiki/XY_problem) and we need quite some more information to know what you are actually trying to achieve. That's where the slow performance of the query likely comes from - a somewhat wrong approach to the problem. We only see the approach, but we need to see the problem to be able to sufficiently help you. — Thomas Flinkow, Jun 08 '18 at 11:05
If you run the query directly against the database (using https://www.firebirdsql.org/en/third-party-tools/) how long does it take? — mjwills, Jun 08 '18 at 11:07
(short note: database access layer classes should probably not show message boxes) — Thomas Flinkow, Jun 08 '18 at 11:08
@Crowcoder For testing purpose i changed parameters into manually written but i have changed it back on how it should been — Aleksa Ristic, Jun 08 '18 at 11:25
@ThomasFlinkow `smanjuj` and `povecavaj` are bools but they do not make any problem since when i debug line with `using(FbDataReader.....` takes 3 sec not `if/else` with those variables — Aleksa Ristic, Jun 08 '18 at 11:27
@mjwills I have tried that with manually inserting parameters so it runs only once and it takes 3-4 sec — Aleksa Ristic, Jun 08 '18 at 11:28
If it takes 3-4 seconds to run directly against the DB (without C#) then this isn't a C# question. It is purely a Firebird question. — mjwills, Jun 08 '18 at 11:28
`RIGHT OUTER JOIN DOKUMENT ... WHERE ... AND (DOKUMENT.FLAG < 2)` - you do not need RIGHT JOIN here since you explicitly remove NULL values, hence use usual JOIN (aka INNER JOIN) — Arioch 'The, Jun 08 '18 at 14:18
You should create a single query instead of N+1, that is executing 1 query and then executing N queries based on the results. This is essentially a duplicate of your earlier question https://stackoverflow.com/questions/50363866/building-sql-with-foreach — Mark Rotteveel, Jun 09 '18 at 07:08
BTW, who is native English speaker? Why inner and outer joins are called inner and outer ? — Arioch 'The, Jun 09 '18 at 09:13
@Arioch'The I suggest you ask a question tagged with SQL for that. — Mark Rotteveel, Jun 09 '18 at 11:47

score 1 · Answer 1 · answered Jun 08 '18 at 14:19

This query is first reading the DOKUMENT table, and then reading the STAVKA table.

If the selectivity of the filter criteria DOKUMENT.FLAG < 2 is good, then an index should make it fast. A good selectivity is when the filtered rows are less than 5% of the table, ideally less than 0.5% of the rows of the table. For a good selectivity I would make sure I have the following index created:

create index ix1_document on DOKUMENT (FLAG);

Then, it's reading the table STAVKA. I would assume that table already has an index by VRDOK/BRDOK, since it's probably a foreign key. However, this is not enough to make it faster. You need to include MAGACINID, ROBAID, and TREN_STANJE in the index:

create index ix1_stavka (VRDOK, BRDOK, MAGACINID, ROBAID, TREN_STANJE);

This extra index will increase performance.

Now, if you already have these indexes, then there isn't much more you can do at the SQL optimization level.

The next option is to use caching. If you are running the query 4000 times, maybe you could run it once and keep the results in memory for the next 60 seconds. All subsequent calls will show it from memory instead of running the query again... until those 60 seconds pass, and you consider the result "stale".

Another option is to use in-memory database option, if available on Firebird, but I'm not sure about this.

SQL Query runs too slow

1 Answers1