запрос для профессионалов (C++, ACCESS) - Базы данных

Здравствуйте. Очень понравился Ваш сайт. Спасибо создателям, а также всем кто задает вопросы и отвечает. Надеюсь вы мне поможете решить проблему.
Есть у меня одна программа, которая очень медленно работает. Тормозит из-за двух таблиц(точнее из-за запросов к этим таблицам).

таблица 1.(T1)
id — ид изделия или детали
name — имя изделия или детали

таблица 2.(T2)
id1 — какое изделие(ид изделия из табл.1)
id2 — что в него входит(ид изделия или детали из табл.1)
ccount — в каком количестве входит..

T1:
id | name
---------
1 | name1
2 | name2
3 | name3
.. .. (~55 000 записей)
T2:
id1 | id2 | ccount
------------------
1 | 2 | 20
1 | 3 | 700
2 | 7 | 33
3 | 4 | 14
5 | 4 | 10
.. .. .. (~130 000 записей)

В изделия могут входить изделия и детали. В детали ничего не входит. Вхождение узнаем в таблице T2.
Например, по этим двум таблицам можно определить, что в name1 входит name2 и name3, а в name2 в свою очередь входит name7 и т.д. В конце концов получается, что в name1 входят: name2, name3, name7.. и т.д.
Задача состоит в подсчете этих входящих изделий(name2, name3, name7..), на определенное изделие(в name1) одним запросом, т.е. без tmp-table.
Ответьте, пожалуйста, можно ли это сделать. Заранее спасибо всем ответившим.

P.S.(самое главное): БД — MS ACCESS 97, программа на VC++6, подключение через ADO "import..".

Здравствуйте, БуМарат, Вы писали:

БМ>Задача состоит в подсчете этих входящих изделий(name2, name3, name7..), на определенное изделие(в name1) одним запросом, т.е. без tmp-table.

БМ>Ответьте, пожалуйста, можно ли это сделать. Заранее спасибо всем ответившим.

Это вряд ли. Я б попробовал считать через рекурсивно вызываемую хранимую процедуру.

БМ>P.S.(самое главное): БД — MS ACCESS 97, программа на VC++6, подключение через ADO "import..".

А хранимых процедур там нет...

Тогда добавь в T1 поле "сложность", означающее, из скольких деталей состоит в конце концов данное изделие/деталь, и пересчитывай при каждом изменении таблицы T2. Тогда исчисление сложности будет просто запросом на это поле.

Здравствуйте, George Seryakov, Вы писали:

GS>Тогда добавь в T1 поле "сложность", означающее, из скольких деталей состоит в конце концов данное изделие/деталь, и пересчитывай при каждом изменении таблицы T2. Тогда исчисление сложности будет просто запросом на это поле.

Дело в том, что эти таблицы я беру в другом месте на дискетках в виде "*.dbf" и я не могу их изменять. А каждый раз(в неделю 1 раз!) подсчитывать..8-((

... << RSDN@Home 1.0 beta 6a >>

Здравствуйте, БуМарат, Вы писали:

GS>>Тогда добавь в T1 поле "сложность", означающее, из скольких деталей состоит в конце концов данное изделие/деталь, и пересчитывай при каждом изменении таблицы T2. Тогда исчисление сложности будет просто запросом на это поле.

БМ>Дело в том, что эти таблицы я беру в другом месте на дискетках в виде "*.dbf" и я не могу их изменять. А каждый раз(в неделю 1 раз!) подсчитывать..8-((

А ты подумай. Есть (по крайней мере) два способа справиться с этой проблемой (невозможность изменять).

Здравствуйте, George Seryakov, Вы писали:

GS>А ты подумай. Есть (по крайней мере) два способа справиться с этой проблемой (невозможность изменять).

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

(

... << RSDN@Home 1.0 beta 6a >>

Здравствуйте, БуМарат, Вы писали:

GS>>А ты подумай. Есть (по крайней мере) два способа справиться с этой проблемой (невозможность изменять).

БМ>Я конечно подумал, но что-то ничего путного в голову не приходит. Может подскажете?

Да без проблем. 1) Отредактировать dbf-ы. Фоксом или чем они там создавались. Вряд ли это так просто сделать программно, но в режиме дизайна — просто. 2) Втянуть данные в акссесс, а там уже форматом таблиц ты распоряжаешься.

БМ>И еще, неужели нельзя как-нбдь решить задачу при помощи хранимых запросов? Как нибудь рекурсивно их вызывать? Я пробовал, у меня не получилось..

(

Да нет в акссессе хранимых процедур. Еще можно в какой нибудь SQLServer импортировать, но это сложнее, чем другие методы. Особенно если есть проблемы с изменением структуры dbf файлов.

Здравствуйте, George Seryakov, Вы писали:

GS> Да без проблем. 1) Отредактировать dbf-ы. Фоксом или чем они там создавались. Вряд ли это так просто сделать программно, но в режиме дизайна — просто. 2) Втянуть данные в акссесс, а там уже форматом таблиц ты распоряжаешься.
Извините, но Вы не поняли. У меня нет проблем с конвертацией в Access и добавлением поля. У меня проблемы с заполнением этого поля. Нужно будет рекурсией пройти все изделия из таб.1. Это займет очень много времени.

GS>Да нет в акссессе хранимых процедур.
Я говорил про хранимые ЗАПРОСЫ, я знаю что нет процедур, но может можно что-то с запросами написать?!

И еще. А оценки нужно ставить на каждый ответ или один раз в конце? Я тут в первый раз..

... << RSDN@Home 1.0 beta 6a >>

Здравствуйте, БуМарат, Вы писали:

БМ>Извините, но Вы не поняли. У меня нет проблем с конвертацией в Access и добавлением поля. У меня проблемы с заполнением этого поля. Нужно будет рекурсией пройти все изделия из таб.1. Это займет очень много времени.

Не рекурсией. Просто проверяем, что у данного изделия все составляющие имеют оценку сложности, тогда присваиваем сложность и данному изделию. Применив такой вызов столько раз, сколько уровней в иерархии, получим заполненную таблицу.

Да, времени займет изрядно. Можно так: делаем поле сложности, не заполняем его, но при любом вычислении сложности проставляем в это поле вычисленную сложность. И, если она вычислена, используем в лдальнейшем ее.

БМ>Я говорил про хранимые ЗАПРОСЫ, я знаю что нет процедур, но может можно что-то с запросами написать?!

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

Здравствуйте, БуМарат.

что-то я понять не могу что требуется... ну да ладно давай думать и анализировать.

сразу предупрежу что с Аксесом не работал, так что буду говорить общими словами. может кому-то еще пригодится.

итак.

есть деревья: id -> parent_id. описывающе изделия и их "состав".

пусть есть:

изделие А, которое состоит из двух деталей (А1 и А2)

деталь А1, которая состоит из детаи В и изделия Б

деталь А2

изделие Б, которое состоит из двух деталей (Б1 и Б2)

деталь Б1

деталь Б2

деталь В

в таблицах это всё выглядеть буудет так:

справочник:
id      | name
--------------------
1    | изделие А    
2    | деталь А1    
3    | деталь А2    
4    | изделие Б    
5    | деталь Б1    
6    | деталь Б2    
7    | деталь В    

состав:
master_id | detail_id | quan
----------------------------
  1       |   2       |   1
  1       |   3       |   1
  2       |   4       |   2
  2       |   7       |   1
  4       |   5       |   1
  4       |   6       |   2

внимание вопрос: что ты ожидаешь увидеть в результате работы запроса для изделия А1???

"для изготовления изделия А потребно ... " :

"... деталь А1 — 1 шт., деталь А2 — 1 шт." (скорее всего не это :), но тем не менее пусть будет)

"... изделие Б — 2 шт., деталь В — 1 шт., деталь А2 — 1 шт."

"... деталь Б1 — 2 шт., деталь Б2 — 4 шт., деталь В — 1 шт., деталь А2 — 1 шт." (скорее всего это)

рассмотрим варианты:
1. — делается элементарно: взять всех "детей" (не всех "потомков", а только непосредственных) для данного "папы".
2. — просто второй шаг рекурсии и тоже очевиден, но не понятно кому оно в таком виде надо :)
3. — собственно хотим получить все листья у данной подветки (танцуем от А), т.е. полная рекурсия. (на самом деле тоже не пойму к чему это надо, но да ладно. надо — значит надо)

в случае варианта 3 решение простое: выполни один раз (при импорте данных) честный обход дерева, т.е. для каждого из изделий выполни процедуру "раздеталивания" и сохрани результаты и пользуйся ими. получишь что-то типа

master_id | detail_id | quan
-----------------------------
  1       |   3       |   1
  1       |   7       |   1
  1       |   5       |   2
  1       |   6       |   4
  4       |   5       |   1
  4       |   6       |   2

если же такой способ построения данной таблицы тебя не устраивает, то давай прикинем как нам жить себе упростить при ее получении:

как же нам получить вышеописанную красоту??? конечно рекурсивно. но!!!

1. понадобится структура TZ_table вида: [master_id, detail_id, quan, level]. создадим ее.
2. берем все листья и постим в эту таблицу таки образом: состав.detail_id -> TZ_table.master_id, null -> TZ_table.detail_id, 1 -> TZ_table.quan
3. в цикле выполняем:

вставку в таблицу TZ_table (@level — переменная, счетчик уровней):

insert into TZ_table (master_id, detail_id, quan, level) 
select 
  TZ_table.master_id as master_id, 
  состав.master_id as detail_id, 
  TZ_table.quan * состав.quan as quan, 
  @level as level 
from состав 
inner join TZ_table on TZ_table.detail_id = состав.detaile_id and level = (@level - 1)

иными словами: находим в таблице [состав] все такие записи, которые являются родителями для элементов предыдущего уровня, и вставляем из в нашу таблицу взяв:
== в качестве значения для master_id значеине master_id элемента предыдущего уровня,
== в качестве значения для detail_id значение master_id у найденного элемента,
== quan вычисляем как произведение quan из предыдущего уровня и quan для найденного элемента,
== значение для level берем из счетчика уровней

инкриментируем @level

цикл длится до тех пор пока в TZ_table вставлена хотя бы одна запись. итераций в цикле будет ровно столько сколько уровней вложенности.

вот мы и получили красоту. но не совсем ту, что собирались: например если А = А1 + 2*А2 + 2*Б + Б2, где Б = Б1 + 2*Б2, то мы получим такую картинку:

master_id | detail_id | quan | level |
--------------------------------------
  А1      |   null    |   1  |   0   |
  А2      |   null    |   1  |   0   |
  Б1      |   null    |   1  |   0   |
  Б2      |   null    |   1  |   0   |
  А1      |   А       |   1  |   1   |
  А2      |   А       |   2  |   1   |
  Б1      |   Б       |   1  |   1   |
  Б2      |   А       |   1  |   1   |
  Б2      |   Б       |   2  |   1   |
  Б1      |   А       |   2  |   2   |
  Б2      |   А       |   4  |   2   |

а) записи у которых detail_id == null можно и удалить: полезной нагрузки они не несут, но их много.
б) для детали Б2 мы видим два вхождения в изделие А (одно "по рецептуре" непосредственно для А, второе из-за того, что в А входит Б, которое "по рецептуре" тоже содержит Б2), но это не страшно. надо просто не забывать их суммировать при запросе (делов-то)

примечание: перед тем как браться строить эту "красоту" было бы неплохо предварительно провериться на отсутствие в деревьях циклов, но можно это делать и по мере вставки.

ну вот и всё. счастье есть :))
запрос возвращающий состав изделия [А] выглядит так:

select 
  master_id, sum(quan) as quan
from TZ_table
where detail_id = [А]
group by master_id

Здравствуйте, _MarlboroMan_, Вы писали:

M>пусть есть:
M>

изделие А, которое состоит из двух деталей (А1 и А2)
M>

деталь А1, которая состоит из детаи В и изделия Б

По-моему, Вы не совсем поняли..
Я же писал, что в детали(в данном случае, А1) уже ничего не входит. Деталь — это конечный узел(конечный корень дерева). В изделия могут входить только изделия(разузловываем дальше), только детали(нашли всех сыновей), изделия(здесь раузловываем дальше) и детали.
Например, Изделие1 состоит из двух изделий и двух деталей:

Изделие1->Изделие2(1 шт.)
Изделие1->Изделие3(2 шт.)
Изделие1->Деталь4(1 шт.)
Изделие1->Деталь5(1 шт.)

Допустим, в Изделие2 и в Изделие3 входит по одной детальке:

Изделие2->Деталь4(2 шт.)
Изделие3->Деталь5(3 шт.)

Естественно, в Деталь4 и в Деталь5 ничего не входит.

M>внимание вопрос: что ты ожидаешь увидеть в результате работы запроса для изделия А1???

Я хочу получить всё, что входит в Изделие1 и в каком количестве:
Изделие2 — 1 шт.
Изделие3 — 2 шт.
Деталь4 — (1+1) — 2 шт.
Деталь5 — (1+2*1) — 3 шт.

Вопрос вот в чем: могу ли я одним запросом получить вот такую таблицу, при условии, что данные организованы в виде таблиц T1 и T2? Если это нельзя сделать, то как малой кровью решить проблему?

M>2. — просто второй шаг рекурсии и тоже очевиден, но не понятно кому оно в таком виде надо :)
M>3. — собственно хотим получить все листья у данной подветки (танцуем от А), т.е. полная рекурсия. (на самом деле тоже не пойму к чему это надо, но да ладно. надо — значит надо)

А для чего это надо я Вам объясню. Технологический отдел дает данные об основных и вспомогательных материалах на каждое изделие и деталь без учета этого дерева. Для того, чтобы получить все материалы на определенный узел(ну, изделие) я должен получить вышеописанный список(с количествами) и одной выборкой (умножая в селекте на количества, т.к. материалы только на одну единицу) получить материалы на этот узел. Здесь как раз проблем нет. Проблема в построении вышеописанного списка.

M>1. понадобится структура TZ_table вида: [master_id, detail_id, quan, level]. создадим ее.
M>2. берем все листья и постим в эту таблицу таки образом: состав.detail_id -> TZ_table.master_id, null -> TZ_table.detail_id, 1 -> TZ_table.quan
M>3. в цикле выполняем:
M>

вставку в таблицу TZ_table (@level — переменная, счетчик уровней):
M>

M>insert into TZ_table (master_id, detail_id, quan, level) 
M>select 
M>  TZ_table.master_id as master_id, 
M>  состав.master_id as detail_id, 
M>  TZ_table.quan * состав.quan as quan, 
M>  @level as level 
M>from состав 
M>inner join TZ_table on TZ_table.detail_id = состав.detaile_id and level = (@level - 1)
M>

M>иными словами: находим в таблице [состав] все такие записи, которые являются родителями для элементов предыдущего уровня, и вставляем из в нашу таблицу взяв:
M>== в качестве значения для master_id значеине master_id элемента предыдущего уровня,
M>== в качестве значения для detail_id значение master_id у найденного элемента,
M>== quan вычисляем как произведение quan из предыдущего уровня и quan для найденного элемента,
M>== значение для level берем из счетчика уровней

M>

инкриментируем @level

M>цикл длится до тех пор пока в TZ_table вставлена хотя бы одна запись. итераций в цикле будет ровно столько сколько уровней вложенности.

M>вот мы и получили красоту. но не совсем ту, что собирались: например если А = А1 + 2*А2 + 2*Б + Б2, где Б = Б1 + 2*Б2, то мы получим такую картинку:
M>

M>master_id | detail_id | quan | level |
M>--------------------------------------
M>  А1      |   null    |   1  |   0   |
M>  А2      |   null    |   1  |   0   |
M>  Б1      |   null    |   1  |   0   |
M>  Б2      |   null    |   1  |   0   |
M>  А1      |   А       |   1  |   1   |
M>  А2      |   А       |   2  |   1   |
M>  Б1      |   Б       |   1  |   1   |
M>  Б2      |   А       |   1  |   1   |
M>  Б2      |   Б       |   2  |   1   |
M>  Б1      |   А       |   2  |   2   |
M>  Б2      |   А       |   4  |   2   |
M>

M>а) записи у которых detail_id == null можно и удалить: полезной нагрузки они не несут, но их много.
M>б) для детали Б2 мы видим два вхождения в изделие А (одно "по рецептуре" непосредственно для А, второе из-за того, что в А входит Б, которое "по рецептуре" тоже содержит Б2), но это не страшно. надо просто не забывать их суммировать при запросе (делов-то)
M>
M>примечание: перед тем как браться строить эту "красоту" было бы неплохо предварительно провериться на отсутствие в деревьях циклов, но можно это делать и по мере вставки.

M>
M>ну вот и всё. счастье есть :))
M>запрос возвращающий состав изделия [А] выглядит так:
M>

M>select 
M>  master_id, sum(quan) as quan
M>from TZ_table
M>where detail_id = [А]
M>group by master_id
M>

M>

M>

... << RSDN@Home 1.0 beta 6a >>

Здравствуйте, БуМарат, Вы писали:

Сорри, случайно отправил.. Не успел ответить на остальное.

... << RSDN@Home 1.0 beta 6a >>

Здравствуйте, _MarlboroMan_, Вы писали:

M>в случае варианта 3 ..
Не совсем вариант 3, конечно, но близко. Нам нужны не только листья!
M>решение простое: выполни один раз (при импорте данных) честный обход дерева, т.е. для каждого из изделий выполни процедуру "раздеталивания" и сохрани результаты и пользуйся ими..

Опять таки, этот честный обход очень долог

M>..как же нам получить вышеописанную красоту??? конечно рекурсивно. но!!!

M>1. понадобится структура..
Что-то я никак не могу въехать, в то, что говорится дальше. Может быть по-подробнее объясните? Если это промежуточный шаг (насколько я понял), тогда по-моему во времени мы не выигрываем. Или я что-то не так понял? Заранее благодарю за ответ.

... << RSDN@Home 1.0 beta 6a >>

Здравствуйте, БуМарат, Вы писали:

БМ>Здравствуйте, _MarlboroMan_, Вы писали:

БМ>Не совсем вариант 3, конечно, но близко. Нам нужны не только листья!

из полученной таблицы можешь брать любуюу информацию. в построенной таблице есть ВСЯ информация.

БМ>Опять таки, этот честный обход очень долог

не такой уж он честный :)) и не так уж и долог :)) ты попробуй :))

БМ>Что-то я никак не могу въехать, в то, что говорится дальше. Может быть по-подробнее объясните? Если это промежуточный шаг (насколько я понял), тогда по-моему во времени мы не выигрываем. Или я что-то не так понял? Заранее благодарю за ответ.

суть в том, что при твоей структуре данных выполнить потребные тебюе расчеты за приемлимое время нереально. увы.
поэтому я рекомендую на этапе импорта данных (закачка из DBF-ок) построить дополнительную таблицу. собственно способ ее построения и описан. а в самом низу описан пример запроса.
в принципе ты можешь "подогнать" наполнение дополнительной таблицы ровно так как тебе нужно.

... << RSDN@Home 1.1 alpha 1 >>

	От:	БуМарат
	Дата:	26.06.03 18:01
	Оценка:

	От:	George Seryakov
	Дата:	26.06.03 18:10
	Оценка:

	От:	БуМарат
	Дата:	26.06.03 18:30
	Оценка:

	От:	George Seryakov
	Дата:	26.06.03 18:39
	Оценка:

	От:	БуМарат
	Дата:	26.06.03 18:55
	Оценка:

	От:	_MarlboroMan_
	Дата:	26.06.03 22:22
	Оценка:	31 (2)