Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Помогите составить запрос / 25 сообщений из 32, страница 1 из 2
  1  все
01.12.2016, 20:08
    #39358894
Marcello
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, 21:00
    #39358921
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Убери из подзапроса таблицу картовладельцев.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 21:10
    #39358923
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Marcello,

нафига ты left join тут накрутил?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 21:11
    #39358925
Marcello
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:17
    #39358926
Marcello
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:37
    #39358931
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
MarcelloТеперь нужно к каждому полученному документу прицепить дату предыдущей продажиИндекс по дате где ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 21:41
    #39358932
Marcello
Marcello
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
hvladИндекс по дате где ?
Он есть
Код: sql
1.
CREATE INDEX DOCUMENT_IDX2 ON DOCUMENT (DC_DATE);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 21:49
    #39358937
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
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:53
    #39358939
Marcello
Marcello
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Симонов Денис,

2.5, к сожалению
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.12.2016, 21:54
    #39358940
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
и кстати

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, 22:01
    #39358945
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
MarcelloОн есть
А назачем он возрастающий, если ты ищешь на убывание? ССЗБ?..
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 09:30
    #39359093
Marcello
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:32
    #39359096
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Marcello,

дак ты смысл запроса поменял
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 09:36
    #39359103
Marcello
Marcello
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Симонов Денис,

у Сибирякова было предположение, что индекс не работает из-за его "возрастания". Вместо создания убывающего индекса поменял условие, индекс не "включился".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 09:41
    #39359107
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
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:44
    #39359109
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Marcello,

если не поможет попробуй выполнить отдельно запрос который в находится CTE и сообщи его статистику выполнения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 11:07
    #39359163
Marcello
Marcello
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Симонов Дениспопробуй выполнить отдельно запрос который в находится CTE
Сорри, не понял
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 11:09
    #39359164
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Marcello,

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

Ты мой запрос пробовал запускать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 11:26
    #39359183
Marcello
Marcello
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Симонов Денис,

да, виснет наглухо... Попробую CTE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 13:23
    #39359291
Marcello
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:40
    #39359307
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Marcello,

Это с DESC индексом по дате ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 13:51
    #39359319
Marcello
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:54
    #39359324
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
MarcelloExecute time = 59m 16s 74ms

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

Хм... в новом плане не вижу не индекса по дате, ни сортировки. Тебе точно надо 200000 записей таким запросом вернуть? Подозрительно это.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 13:55
    #39359325
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
Marcello,

не надо через час. Если запрос не отработал за 5 минут срубай его.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2016, 13:56
    #39359326
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить запрос
MarcelloМогу выполнить похожее, но в обратную сторону.Просто покажи план, для начала
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Помогите составить запрос / 25 сообщений из 32, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 423ms
Закрыть
start [/forum/topic.php?fid=40&mobile=1&tid=1561818]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 40ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 63ms
get tp. blocked users:
 1ms
others: 275ms
total:  423ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]