Выполнение ХП с индексом по полю одной из таблиц, и без

Access Violation, некорректное выполнение запросов или вызовов API, ошибки утилит командной строки, в общем все, что вам мешает работать

Модераторы: kdv, dimitr

Ответить
Yaral
Сообщения: 18
Зарегистрирован: 12 фев 2006, 13:00

Выполнение ХП с индексом по полю одной из таблиц, и без

Сообщение Yaral » 25 июл 2006, 09:31

Когда я писал ХП у меня в таблицах были индексы только на ключевых полях, ХП выдовала правильные результаты, я проставил индексы, и ХП стала выдавать не правильные результаты. Вычеслил один индекс, с ним ХП не правильно работает. Данные выдает, но не правлиьно, вернее не все.
ХП строит набор данных древовидной структуры, так вот, с индексом она почемуто выдает только первые узлы в каждой ветке, а остальные игнорирует. А без индекса все правильно выдает.
В IB Expert в режиме трассировки даже с индексом возращает нормальный набор данных. А если просто выполнить тут же, опять не правильный.
Версия сервера FB 1.5.3.4870

dimitr
Разработчик Firebird
Сообщения: 888
Зарегистрирован: 26 окт 2004, 16:20

Сообщение dimitr » 25 июл 2006, 09:33

покажи запрос из процедуры (или все ее тело) и план

Yaral
Сообщения: 18
Зарегистрирован: 12 фев 2006, 13:00

Сообщение Yaral » 25 июл 2006, 09:53

Процедура:

Код: Выделить всё

CREATE PROCEDURE BUILD_CONTREE(
    IOBJ_ID INTEGER,
    ISTATE1 INTEGER,
    ISTATE2 INTEGER)
RETURNS (
    CONTREE_ID INTEGER,
    PARENT_ID INTEGER,
    OBJ_ID INTEGER,
    RTYPE_ID INTEGER,
    LNAME VARCHAR(250),
    CODE VARCHAR(5),
    ORDERINT INTEGER,
    STATE INTEGER)
AS
DECLARE VARIABLE VNOREC_ID INTEGER;
DECLARE VARIABLE VCONTREE_ID INTEGER;
DECLARE VARIABLE VRTYPE_ID INTEGER;
BEGIN
  VNOREC_ID=0;
  FOR SELECT CONTREE_ID, PARENT_ID, OBJ_ID, RTYPE_ID, LNAME, CODE, ORDERINT, STATE FROM CONTREE
  WHERE OBJ_ID=:IOBJ_ID AND (RTYPE_ID=1 OR RTYPE_ID=2 OR RTYPE_ID=3) AND STATE=1
  ORDER BY ORDERINT
  INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
  DO BEGIN
    SUSPEND;
    VCONTREE_ID = :CONTREE_ID;
    VRTYPE_ID   = :RTYPE_ID + 3;

    IF (NOT EXISTS (SELECT * FROM CONTREE
                    WHERE PARENT_ID=:CONTREE_ID AND (STATE=:ISTATE1 OR STATE=:ISTATE2)
                   )) THEN
      BEGIN
        VNOREC_ID = :VNOREC_ID - 1;
        FOR SELECT :VNOREC_ID, :VCONTREE_ID, :OBJ_ID, :VRTYPE_ID, '', '<...>', 1, :STATE FROM ZX
        INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
        DO SUSPEND;
      END

    FOR SELECT CONTREE_ID, PARENT_ID, OBJ_ID, RTYPE_ID, LNAME, CODE, ORDERINT, STATE FROM CONTREE
    WHERE PARENT_ID=:CONTREE_ID AND (STATE=:ISTATE1 OR STATE=:ISTATE2)
    ORDER BY ORDERINT
    INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
    DO BEGIN
      SUSPEND;
        IF (NOT EXISTS (SELECT * FROM CONTREE
                        WHERE PARENT_ID=:CONTREE_ID AND (STATE=:ISTATE1 OR STATE=:ISTATE2)
                       )) THEN
          BEGIN
            VCONTREE_ID = :CONTREE_ID;
            VNOREC_ID = :VNOREC_ID - 1;
            FOR SELECT :VNOREC_ID, :VCONTREE_ID, :OBJ_ID, 7, '', '<...>', 1, :STATE FROM ZX
            INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
            DO SUSPEND;
          END

      FOR SELECT CONTREE_ID, PARENT_ID, OBJ_ID, RTYPE_ID, LNAME, CODE, ORDERINT, STATE FROM CONTREE
      WHERE PARENT_ID=:CONTREE_ID AND (STATE=:ISTATE1 OR STATE=:ISTATE2)
      ORDER BY FMAIN DESC, ORDERINT
      INTO :CONTREE_ID, :PARENT_ID, :OBJ_ID, :RTYPE_ID, :LNAME, :CODE, :ORDERINT, :STATE
      DO SUSPEND;
    END

  END
END
Вызов:
SELECT * FROM BUILD_CONTREE(381, 1, 1)

Без индекса по полю ORDERINT:

План
PLAN (CONTREE INDEX (CONTREE_IDX1))(ZX NATURAL)(CONTREE INDEX (CONTREE_IDX1))(ZX NATURAL)SORT ((CONTREE INDEX (CONTREE_IDX1)))SORT ((CONTREE INDEX (CONTREE_IDX1)))SORT ((CONTREE INDEX (CONTREE_IDX2,CONTREE_IDX3,CONTREE_IDX3,CONTREE_IDX3)))

Адаптированный план
PLAN (CONTREE INDEX (CONTREE_IDX1))(ZX NATURAL)(CONTREE INDEX (CONTREE_IDX1))(ZX NATURAL)SORT ((CONTREE INDEX (CONTREE_IDX1)))SORT ((CONTREE INDEX (CONTREE_IDX1)))SORT ((CONTREE INDEX (CONTREE_IDX2,CONTREE_IDX3,CONTREE_IDX3,CONTREE_IDX3)))

------ Performance info ------
Prepare time = 0ms
Execute time = 100ms
Avg fetch time = 5,88 ms
Current memory = 1 424 752
Max memory = 27 333 076
Memory buffers = 2 048
Reads from disk to cache = 22
Writes from cache to disk = 2
Fetches from cache = 231

С индексом по полю ORDERINT:

План
PLAN (CONTREE INDEX (CONTREE_IDX1))(ZX NATURAL)(CONTREE INDEX (CONTREE_IDX1))(ZX NATURAL)SORT ((CONTREE INDEX (CONTREE_IDX1)))(CONTREE ORDER CONTREE_IDX5)(CONTREE ORDER CONTREE_IDX5)

Адаптированный план
PLAN (CONTREE INDEX (CONTREE_IDX1))(ZX NATURAL)(CONTREE INDEX (CONTREE_IDX1))(ZX NATURAL)SORT ((CONTREE INDEX (CONTREE_IDX1)))(CONTREE ORDER CONTREE_IDX5)(CONTREE ORDER CONTREE_IDX5)

------ Performance info ------
Prepare time = 0ms
Execute time = 50ms
Avg fetch time = 3,85 ms
Current memory = 2 204 040
Max memory = 27 333 076
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 2
Fetches from cache = 833

dimitr
Разработчик Firebird
Сообщения: 888
Зарегистрирован: 26 окт 2004, 16:20

Сообщение dimitr » 25 июл 2006, 10:23

сходу криминала не вижу. Можешь выложить где или выслать мне бекап базы на анализ? Интересно разобраться, кто тут виноват...

Merlin
Динозавр IB/FB
Сообщения: 1502
Зарегистрирован: 27 окт 2004, 11:44

Сообщение Merlin » 25 июл 2006, 12:25

Шас выяснится, что это IB6.0... Есть подозрение на If Exists, но этот глюк стабильно, насколько мне известно, не проявляется.

Yaral
Сообщения: 18
Зарегистрирован: 12 фев 2006, 13:00

Сообщение Yaral » 25 июл 2006, 12:31

В первом сообщении написано что это FB 1.5.3.4870
Бэкап не получиться выслать, он 50 метров. Могу выслать определение таблицы, и данные для одного или двух объектов

dimitr
Разработчик Firebird
Сообщения: 888
Зарегистрирован: 26 окт 2004, 16:20

Сообщение dimitr » 25 июл 2006, 12:31

он выше написал, что 1.5.3

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 25 июл 2006, 12:36

dimitr писал(а):Интересно разобраться, кто тут виноват...
Неполный фетч ?

dimitr
Разработчик Firebird
Сообщения: 888
Зарегистрирован: 26 окт 2004, 16:20

Сообщение dimitr » 25 июл 2006, 12:49

hvlad
вот только почему он там неполный? Из процедуры-то все должно фетчиться одинаково. Ладно, если бы еще в ней апдейты были, но тут я ничего опасного не наблюдаю...

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 25 июл 2006, 14:38

dimitr писал(а):hvlad
вот только почему он там неполный? Из процедуры-то все должно фетчиться одинаково. Ладно, если бы еще в ней апдейты были, но тут я ничего опасного не наблюдаю...
Другая версия - из-за другого плана (SORT vs ORDER) записи стали выдаваться в другом порядке и null'ы вышли на первое место (не в индексном поле, а например PARENT_ID)

В любом случае фразы типа
Yaral писал(а):В IB Expert в режиме трассировки даже с индексом возращает нормальный набор данных. А если просто выполнить тут же, опять не правильный
сам знаешь что означают :wink:

Ответить