时间戳

首页 > 解决方案 > SQL 查询运行太慢

sql - SQL 查询运行太慢

问题描述

我有这个命令

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

SQL 语句在哪里：

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)

问题是using (FbDataReader dr = cmd.ExecuteReader())运行大约需要 3-4 秒，如果运行它的次数很少，但需要运行大约 4k 次，因此需要 16k 秒（4 小时）

这两个表的工作原理是：

我有一个文档 ( DOKUMENT)，它是一排，它有多个项目 ( STAVKA) 连接到DOKUMENT(具有文档类型 ( VRDOK) 和文档编号 ( BRDOK))。

现在对于每个项目（STAVKA.ROBAID）我选择（STAVKA.TREN_STANJE）但DOCUMENT.FLAG其中包含STAVKA必须有flag < 2

例子：

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

因此，当我运行 SQL 语句时，它需要MIN(TREN_STANJE)从该文档的STAVKAWHERE ROBAID = @RID (3 in this case)BUT中进行选择（您可以在andFLAG中看到相同的列）必须是DOKUMENTSTAVKA< 2

我怎样才能加快速度？

表结构STAVKA为：

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")
);

的结构DOKUMENT是：

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")
);

标签： sqlfirebird

解决方案

这个查询是先读DOKUMENT表，再读STAVKA表。

如果过滤条件的选择性DOKUMENT.FLAG < 2很好，那么索引应该使其快速。一个好的选择性是当过滤的行小于表的 5%，理想情况下小于表的行的 0.5%。为了获得良好的选择性，我会确保创建了以下索引：

create index ix1_document on DOKUMENT (FLAG);

然后，它正在读表STAVKA。我会假设该表已经有一个索引VRDOK/ BRDOK，因为它可能是一个外键。然而，这还不足以让它更快。您需要在索引中包含MAGACINID、ROBAID和：TREN_STANJE

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

这个额外的索引将提高性能。

现在，如果您已经拥有这些索引，那么您在 SQL 优化级别上就无能为力了。

下一个选项是使用缓存。如果您正在运行查询 4000 次，也许您可​​以运行一次并将结果在接下来的 60 秒内保存在内存中。所有后续调用都会从内存中显示它，而不是再次运行查询......直到那 60 秒过去，并且您认为结果“陈旧”。

另一种选择是使用内存数据库选项，如果在 Firebird 上可用，但我不确定。

推荐阅读