Помогите составить запрос / Firebird, InterBase

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Помогите составить запрос

32 сообщений из 32, показаны все 2 страниц

все

Помогите составить запрос

#39358894

Marcello

Гость

Приветствую!

Есть таблица покупателей

Код: plsql

1.
2.
3.
4.
5.

CREATE TABLE CARD_HOLDERS (
    CH_ID             BIGINT NOT NULL, // PK
    CH_SURNAME        VARCHAR(32) NOT NULL
);
ALTER TABLE CARD_HOLDERS ADD CONSTRAINT PK_CARD_HOLDERS PRIMARY KEY (CH_ID);

Есть таблица документов

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.

CREATE TABLE DOCUMENT (
    DC_ID                     BIGINT NOT NULL, // PK
    DC_DATE                   DATE NOT NULL, // дата документа
    DC_TYPE                   BIGINT NOT NULL, // тип документа
    DC_ID_WHOSE_CD            BIGINT // покупатель (может быть пустым)
);
ALTER TABLE DOCUMENT ADD CONSTRAINT PK_DOCUMENT PRIMARY KEY (DC_ID);
ALTER TABLE DOCUMENT ADD CONSTRAINT FK_DOCUMENT_5 FOREIGN KEY (DC_ID_WHOSE_CD) REFERENCES CARD_HOLDERS (CH_ID);

Запрос, возвращающий все документы 75 типа (продажа), у которых заполнен покупатель, выглядит так:

Код: plsql

1.
2.
3.
4.

select dc.dc_id, dc.dc_date, ch.ch_surname
from document dc
left join card_holders ch on ch.ch_id = dc.dc_id_whose_cd
where dc.dc_type = 75 and coalesce(dc.dc_id_whose_cd, 0) <> 0

Теперь нужно к каждому полученному документу прицепить дату предыдущей продажи. Делаю так:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

select dc.dc_id, dc.dc_date, ch.ch_surname,
( select first 1 dcx.dc_date
  from card_holders ch
  left join document dcx on dcx.dc_id_whose_cd = ch.ch_id and dcx.dc_type = 75
  where dcx.dc_type = 75 and ch.ch_id = dc.dc_id_whose_cd
  and dcx.dc_date <= dc.dc_date and dcx.dc_id <> dc.dc_id
  order by dcx.dc_date desc ) as prev_date
from document dc
left join card_holders ch on ch.ch_id = dc.dc_id_whose_cd
where dc.dc_type = 75 and coalesce(dc.dc_id_whose_cd, 0) <> 0

Запрос выполняется очень долго. Не могу придумать, как его ускорить.

Для информации:
в таблице CARD_HOLDERS - 167090 строк,
в таблице DOCUMENT - 1073186 строк,
из них документов 75 типа - 444907 строк,
из них с заполненным DC_ID_WHOSE_CD - 214675 строк.

...

Рейтинг:

0 / 0

01.12.2016, 20:08

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358921

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

Убери из подзапроса таблицу картовладельцев.
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

01.12.2016, 21:00

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358923

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

нафига ты left join тут накрутил?

...

Рейтинг:

0 / 0

01.12.2016, 21:10

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358925

Marcello

Гость

Dimitry Sibiryakov,

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

select dc.dc_id, dc.dc_date, ch.ch_surname,
( select first 1 dcx.dc_date
  from document dcx
  where dcx.dc_type = 75 and dcx.dc_id_whose_cd = dc.dc_id_whose_cd
  and dcx.dc_date <= dc.dc_date and dcx.dc_id <> dc.dc_id
  order by dcx.dc_date desc ) as prev_date
from document dc
left join card_holders ch on ch.ch_id = dc.dc_id_whose_cd
where dc.dc_type = 75 and coalesce(dc.dc_id_whose_cd, 0) <> 0

Существенного прироста скорости не наблюдается.

...

Рейтинг:

0 / 0

01.12.2016, 21:11

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358926

Marcello

Гость

Для заполнения cxPivotGrid все строки нужны сразу.

FETCH ALL по первому запросу возвращает 214765 строк

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

Plan
PLAN JOIN (DC INDEX (FK_DOCUMENT_9), CH INDEX (PK_CARD_HOLDERS))

------ Performance info ------
Prepare time = 31ms
Execute time = 2s 390ms
Avg fetch time = 0,01 ms
Current memory = 35 608 088
Max memory = 35 688 896
Memory buffers = 2 048
Reads from disk to cache = 14 277
Writes from cache to disk = 0
Fetches from cache = 1 963 513

Второй запрос уходит куда-то в себя. План запроса такой:

Код: sql

1.
2.
3.

Plan
PLAN SORT ((DCX INDEX (FK_DOCUMENT_5, DOCUMENT_IDX2)))
PLAN JOIN (DC INDEX (FK_DOCUMENT_9), CH INDEX (PK_CARD_HOLDERS))

...

Рейтинг:

0 / 0

01.12.2016, 21:17

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358931

hvlad

Участник

Сообщения: 13 125

Рейтинг: 0 / 0

MarcelloТеперь нужно к каждому полученному документу прицепить дату предыдущей продажиИндекс по дате где ?

...

Рейтинг:

0 / 0

01.12.2016, 21:37

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358932

Marcello

Гость

hvladИндекс по дате где ?
Он есть

Код: sql

CREATE INDEX DOCUMENT_IDX2 ON DOCUMENT (DC_DATE);

...

Рейтинг:

0 / 0

01.12.2016, 21:41

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358937

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

поскольку версию сервера ты не сказал предлагаю решение для 3.0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

SELECT
    dc.dc_id,
    dc.dc_date,
    ch.ch_surname,
    LAG(dc.dc_id_whose_cd) OVER(PARTITION BY dc.dc_id_whose_cd ORDER BY dc.dc_date) AS prev_date
FROM
    document dc
    LEFT JOIN card_holders ch ON ch.ch_id = dc.dc_id_whose_cd
WHERE
      dc.dc_type = 75
  AND COALESCE(dc.dc_id_whose_cd, 0) <> 0

...

Рейтинг:

0 / 0

01.12.2016, 21:49

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358939

Marcello

Гость

Симонов Денис,

2.5, к сожалению

...

Рейтинг:

0 / 0

01.12.2016, 21:53

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358940

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

и кстати

MarcelloDC_ID_WHOSE_CD BIGINT // покупатель (может быть пустым)

можно преобразовать в

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

SELECT
    dc.dc_id,
    dc.dc_date,
    ch.ch_surname,
    LAG(dc.dc_id_whose_cd) OVER(PARTITION BY dc.dc_id_whose_cd ORDER BY dc.dc_date) AS prev_date
FROM
    document dc
    JOIN card_holders ch ON ch.ch_id = dc.dc_id_whose_cd
WHERE
      dc.dc_type = 75

...

Рейтинг:

0 / 0

01.12.2016, 21:54

| Ответить | Цитировать | Написать

Помогите составить запрос

#39358945

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

MarcelloОн есть
А назачем он возрастающий, если ты ищешь на убывание? ССЗБ?..
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

01.12.2016, 22:01

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359093

Marcello

Гость

Dimitry SibiryakovА назачем он возрастающий, если ты ищешь на убывание? ССЗБ?..

Это тут при чем? Ну, развернул я условие запроса наоборот, скорости это не добавило.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

select dc.dc_id, dc.dc_date, ch.ch_surname,
( select first 1 dcx.dc_date
  from document dcx
  where dcx.dc_type = 75 and dcx.dc_id_whose_cd = dc.dc_id_whose_cd
  and dcx.dc_date >= dc.dc_date and dcx.dc_id <> dc.dc_id
  order by dcx.dc_date asc ) as prev_date
from document dc
left join card_holders ch on ch.ch_id = dc.dc_id_whose_cd
where dc.dc_type = 75 and coalesce(dc.dc_id_whose_cd, 0) <> 0

...

Рейтинг:

0 / 0

02.12.2016, 09:30

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359096

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

дак ты смысл запроса поменял

...

Рейтинг:

0 / 0

02.12.2016, 09:32

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359103

Marcello

Гость

Симонов Денис,

у Сибирякова было предположение, что индекс не работает из-за его "возрастания". Вместо создания убывающего индекса поменял условие, индекс не "включился".

...

Рейтинг:

0 / 0

02.12.2016, 09:36

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359107

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

попробуй вот это

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

WITH T
AS (SELECT
        DC.DC_ID AS DC_ID,
        DC.DC_ID_WHOSE_CD AS DC_ID_WHOSE_CD,
        DC.DC_DATE AS DC_DATE,
        (SELECT
             MAX(DCX.DC_DATE)
         FROM
             DOCUMENT DCX
         WHERE DCX.DC_TYPE = DC.DC_TYPE
           AND DCX.DC_ID_WHOSE_CD = DC.DC_ID_WHOSE_CD
           AND DCX.DC_DATE <= DC.DC_DATE
           AND DCX.DC_ID <> DC.DC_ID) AS PREV_DATE
    FROM
        DOCUMENT DC
    WHERE DC.DC_TYPE = 75
      AND DC.DC_ID_WHOSE_CD IS NOT NULL)
SELECT
    T.DC_ID,
    T.DC_DATE,
    CH.CH_SURNAME,
    T.PREV_DATE
FROM
    T
    JOIN CARD_HOLDERS CH ON CH.CH_ID = T.DC_ID_WHOSE_CD

...

Рейтинг:

0 / 0

02.12.2016, 09:41

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359109

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

если не поможет попробуй выполнить отдельно запрос который в находится CTE и сообщи его статистику выполнения.

...

Рейтинг:

0 / 0

02.12.2016, 09:44

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359163

Marcello

Гость

Симонов Дениспопробуй выполнить отдельно запрос который в находится CTE
Сорри, не понял

...

Рейтинг:

0 / 0

02.12.2016, 11:07

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359164

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

внутри WITH. CTE - Common Table Expression

Ты мой запрос пробовал запускать?

...

Рейтинг:

0 / 0

02.12.2016, 11:09

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359183

Marcello

Гость

Симонов Денис,

да, виснет наглухо... Попробую CTE

...

Рейтинг:

0 / 0

02.12.2016, 11:26

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359291

Marcello

Гость

Симонов Денис,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

Plan
PLAN (DCX INDEX (FK_DOCUMENT_5, FK_DOCUMENT_9))
PLAN (DC INDEX (FK_DOCUMENT_9))

------ Performance info ------
Prepare time = 16ms
Execute time = 59m 16s 74ms
Avg fetch time = 16,36 ms
Current memory = 35 813 192
Max memory = 35 947 464
Memory buffers = 2 048
Reads from disk to cache = 747 095
Writes from cache to disk = 0
Fetches from cache = 58 095 743

Performance Analysis

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

Query Time
------------------------------------------------
Prepare       : 16,00 ms
Execute       : 3 556 074,00 ms
Avg fetch time: 16,36 ms

Memory
------------------------------------------------
Current: 35 798 192
Max    : 35 947 464
Buffers: 2 048

Operations
------------------------------------------------
Read   : 747 095
Writes : 0
Fetches: 58 095 743
Marks  : 0


Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|DOCUMENT                       |         0 |   2204845 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

На всякий случай статистика БД:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              9857075
        Page size               16384
        ODS version             11.2
        Oldest transaction      9170097
        Oldest active           9647498
        Oldest snapshot         9647498
        Next transaction        9698691
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      158348
        Implementation ID       26
        Shadow count            0
        Page buffers            2048
        Next header page        0
        Database dialect        3
        Creation date           Apr 17, 2016 6:09:15
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        END

...

Рейтинг:

0 / 0

02.12.2016, 13:23

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359307

hvlad

Участник

Сообщения: 13 125

Рейтинг: 0 / 0

Marcello,

Это с DESC индексом по дате ?

...

Рейтинг:

0 / 0

02.12.2016, 13:40

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359319

Marcello

Гость

hvlad,

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

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

SELECT
        DC.DC_ID AS DC_ID,
        DC.DC_ID_WHOSE_CD AS DC_ID_WHOSE_CD,
        DC.DC_DATE AS DC_DATE,
        (SELECT
             MIN(DCX.DC_DATE)
         FROM
             DOCUMENT DCX
         WHERE DCX.DC_TYPE = DC.DC_TYPE
           AND DCX.DC_ID_WHOSE_CD = DC.DC_ID_WHOSE_CD
           AND DCX.DC_DATE >= DC.DC_DATE
           AND DCX.DC_ID <> DC.DC_ID) AS PREV_DATE
    FROM
        DOCUMENT DC
    WHERE DC.DC_TYPE = 75
      AND DC.DC_ID_WHOSE_CD IS NOT NULL

О результате отпишусь через час.

...

Рейтинг:

0 / 0

02.12.2016, 13:51

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359324

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

MarcelloExecute time = 59m 16s 74ms

однако терпеливый ты.

Хм... в новом плане не вижу не индекса по дате, ни сортировки. Тебе точно надо 200000 записей таким запросом вернуть? Подозрительно это.

...

Рейтинг:

0 / 0

02.12.2016, 13:54

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359325

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

не надо через час. Если запрос не отработал за 5 минут срубай его.

...

Рейтинг:

0 / 0

02.12.2016, 13:55

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359326

hvlad

Участник

Сообщения: 13 125

Рейтинг: 0 / 0

MarcelloМогу выполнить похожее, но в обратную сторону.Просто покажи план, для начала

...

Рейтинг:

0 / 0

02.12.2016, 13:56

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359334

Marcello

Гость

Симонов ДенисТебе точно надо 200000 записей таким запросом вернуть? Подозрительно это.
Совершенно точно. Этот же запрос, но без подзапроса возвращает то же число строк, только делает это намного быстрее.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

SELECT
        DC.DC_ID AS DC_ID,
        DC.DC_ID_WHOSE_CD AS DC_ID_WHOSE_CD,
        DC.DC_DATE AS DC_DATE
    FROM
        DOCUMENT DC
    WHERE DC.DC_TYPE = 75
      AND DC.DC_ID_WHOSE_CD IS NOT NULL

Вот план и статистика:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

Plan
PLAN (DC INDEX (FK_DOCUMENT_9))

------ Performance info ------
Prepare time = 32ms
Execute time = 5s 562ms
Avg fetch time = 0,03 ms
Current memory = 36 238 608
Max memory = 36 255 992
Memory buffers = 2 048
Reads from disk to cache = 12 516
Writes from cache to disk = 0
Fetches from cache = 889 963

Используется индекс

Код: sql

ALTER TABLE DOCUMENT ADD CONSTRAINT FK_DOCUMENT_9 FOREIGN KEY (DC_TYPE) REFERENCES DOC_TYPE (DCT_CODE);

...

Рейтинг:

0 / 0

02.12.2016, 14:03

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359337

Marcello

Гость

hvladПросто покажи план, для начала

Код: sql

1.
2.
3.

Plan
PLAN (DCX ORDER DOCUMENT_IDX2 INDEX (FK_DOCUMENT_5, FK_DOCUMENT_9))
PLAN (DC INDEX (FK_DOCUMENT_9))

DC_DATE снова не используется:

Код: sql

1.
2.

ALTER TABLE DOCUMENT ADD CONSTRAINT FK_DOCUMENT_5 FOREIGN KEY (DC_ID_WHOSE_CD) REFERENCES CARD_HOLDERS (CH_ID);
ALTER TABLE DOCUMENT ADD CONSTRAINT FK_DOCUMENT_9 FOREIGN KEY (DC_TYPE) REFERENCES DOC_TYPE (DCT_CODE);

...

Рейтинг:

0 / 0

02.12.2016, 14:05

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359338

Marcello

Гость

Marcello,
а нет! Теперь используется.

PLAN (DCX ORDER DOCUMENT_IDX2 INDEX (FK_DOCUMENT_5, FK_DOCUMENT_9))

...

Рейтинг:

0 / 0

02.12.2016, 14:06

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359340

Marcello

Гость

Симонов ДенисЕсли запрос не отработал за 5 минут срубай его.
Все же дождусь. Индекс по дате похоже теперь "включился".

...

Рейтинг:

0 / 0

02.12.2016, 14:07

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359388

Marcello

Гость

Не дождался. Запрос висел почти полчаса. Результата нет.

...

Рейтинг:

0 / 0

02.12.2016, 15:12

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359423

kdv

Участник

Откуда: iBase.ru

Сообщения: 30 139

Рейтинг: 0 / 0

Marcello,

если запрос вот этот 19959670 то вместо MIN(DCX.DC_DATE) напиши MIN(DCX.DC_DATE+0). Видимо, из-за min в плане
DCX ORDER DOCUMENT_IDX2 приводит к конскому числу чтений с диска, потому ты результата и не можешь дождаться.
Хотя, сомневаюсь, что это улучит ситуацию.

...

Рейтинг:

0 / 0

02.12.2016, 15:46

| Ответить | Цитировать | Написать

Помогите составить запрос

#39359430

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

kdv,

в его случае вряд ли. У него внешняя сортировка в подзапросе, а значит жопа гарантирована. С оконными функциями запрос выглядит элегантней и выполняется сравнительно быстро.

ИХМО, на 2.5 проще процедурным подходом задачу решить

...

Рейтинг:

0 / 0

02.12.2016, 15:53

| Ответить | Цитировать | Написать

32 сообщений из 32, показаны все 2 страниц

все

Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Помогите составить запрос

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=40&tid=1561818]:	0ms
get settings:	10ms
get forum list:	11ms
check forum access:	4ms
check topic access:	4ms
track hit:	30ms
get topic data:	13ms
get forum data:	3ms
get page messages:	65ms
get tp. blocked users:	2ms
others:	281ms

total:	423ms