Использование запроса UNION для большого числа таблиц

[ZlydenGL]

Добрый день всем читателям форума!

Есть такая задача - в базе M$ Access хранится более 150 однотипных таблиц. В одну их слить не представляется возможным (пока что), поскольку каждая из таблиц отвечает образно говоря за свой объект.

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

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

Select Sum(Values) From (Select * From Tab1 Union ... Union Select * From TabN) Group By идентификатор

Однако он стопорится уже при выборке из 70 таблиц (Query is too complex). Есть ли возможность все же использовать данную конструкцию?

Спасибо!!!

[Leon_]

Однако это слишком много -- 150 таблиц. И в обслуживании неудобно и запросы строить..
У меня была похожая ситуация, но там объединялось ~30 таблиц. Попробуй сделать свой запрос, объединяющий эти таблицы

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

(Select * From Tab1 Union ... Union Select * From TabN)

отдельно, и сохрани его как qryUnionMain, например. Непременно используй уточнение UNION ALL, это сильно ускорит выполнение этого запроса. И используй qryUnionMain уже в своих итоговых запросах:

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

Select Sum(Values) From qryUnionMain Group By идентификатор

Надеюсь, это поможет.

[Rainbow]

Есть такая задача - в базе M$ Access хранится более 150 однотипных таблиц. В одну их слить не представляется возможным (пока что), поскольку каждая из таблиц отвечает образно говоря за свой объект.

Чего? Однотипные таблицы не слить в одну? Это как?
По-моему, они как раз-таки замечательно сливаются, добавляется еще одно поле - идентификатор объекта. Если надо, делается еще одна таблица (идентификатор объекта + его название), если это надо пользователям...

[ZlydenGL]

они как раз-таки замечательно сливаются, добавляется еще одно поле - идентификатор объекта

Дело в том, что уже сейчас размер базы доходит до 100 Мб. Сама база лежит на диске, доступ к которому осуществляется через VPN канал. Если данные по всем предприятиям слить в одно, то база начинает "падать" слишком регулярно (работают с ней минимум 10 человек). При использовании 150 таблиц падения практически не происходят. Почему так - разобраться не смог

Еще одна проблема - сохранение работоспособности со старыми базами, которые менять к сожалению не получится

А насчет еще одной таблицы - списка идентификаторов - так она уже есть! Ты думаешь, имена объектов хранятся в виде наименований таблиц?

[Rainbow]

Сама база лежит на диске, доступ к которому осуществляется через VPN канал. Если данные по всем предприятиям слить в одно, то база начинает "падать" слишком регулярно (работают с ней минимум 10 человек).

Мдааа... Понятно...

А насчет еще одной таблицы - списка идентификаторов - так она уже есть! Ты думаешь, имена объектов хранятся в виде наименований таблиц?

Ну, люди разные бывают

[ZlydenGL]

Попробуй сделать свой запрос, объединяющий эти таблицы

Именно с этим проблема, поскольку больше 70 таблиц объединить не удается даже этим способом

[Rainbow]

А если попробовать создать 5 запросов по 30 UNION'ов в каждом (эквивалент VIEW), а потом соединить эти 5 view?

[ZlydenGL]

А если попробовать создать 5 запросов по 30 UNION'ов в каждом (эквивалент VIEW), а потом соединить эти 5 view?

Извини за незнание терминологии - но что такое VIEW?

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

[Leon_]

Насколько я знаком с теорией СУБД, сохранение запроса (в частности и VIEW) в базе данных не приводит к сохранению полученной на его основе выборки.. Бр-р.. Смутно как то сформулировал.
Ну т.е. никакого "перекрестного обращения" к запросу разными пользователями не возникнет.
А VIEW (поправьте, если ошибусь) -- это те же Access'овские "запросы на выборку", только в применении к терминологии MS SQL Server. Т.е. запрос "UNION ..." и есть VIEW.

[ZlydenGL]

...сохранение запроса (в частности и VIEW) в базе данных не приводит к сохранению полученной на его основе выборки... Ну т.е. никакого "перекрестного обращения" к запросу разными пользователями не возникнет.

Такая ситуация - первый пользователь хочет сделать отчет по предприятиям с 1 по 77. Код пишет ему первую кверю, вторую, третью... Пока он пишет третью - появляется второй пользователь, который хочет видеть отчет по предприятиям с 33 по 150. Его код переписывает первую кверю, в это время код первого пользователя начитает квери выполнять.... Бррр, кошмар получится!!!

[Leon_]

Бр-р..
Это я виноват, не смог объяснить.
Объекты базы данных--> Запросы--> Создать И пишешь здесь свой запрос, тот самый, объединяющий 150 таблиц. Все -- инструкции SQL, никакого VBA-кода. И называешь запрос qryUnionMain, например.
А вот этот запрос (или view, как кому привычнее) уже можешь использовать как источник для других запросов, в т.ч. и в VBA+DAO (или ADO, как душа прикажет)

[Rainbow]

Ммммм.... Генерить уникальный идентификатор запроса...

]А VIEW (поправьте, если ошибусь) -- это те же Access'овские "запросы на выборку", только в применении к терминологии MS SQL Server. Т.е. запрос "UNION ..." и есть VIEW.

Это да, именно так оно и есть.

Объекты базы данных--> Запросы--> Создать

А ты уверен, что это ограничение ADO, а не самого Access? Я думаю, что дело как раз в том, что Access не может выполнить запрос такой сложности... Сейчас посмотрю где-нибудь по документации...

[Leon_]

Генерить уникальный идентификатор запроса...

Это, видимо, я чего-то непонимаю..
Зачем этим заниматься, это дело сервера БД. Выполняется запрос на SQL Server -- отлично, выполняется Jet SQL на машине клиента -- тоже не беда. Никаких перекрестных обращений возникнуть не может.

[ZlydenGL]

Во-первых, одним запросом все 150 таблиц не охватить - Access ругается не по-русски

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

[Rainbow]

Никаких перекрестных обращений возникнуть не может.

Дело в том, что если это делать программно, то исполнение запроса разбивается на несколько частей - сделать вспомогательные запросы, потом последний результирующий. Это НЕ ОДИН ЗАПРОС, а несколько. Поскольку я предлагаю это делать на клиетне, то возникают такие дурацкие сложности.

Судя по документации, дело в длине запроса, а не в количестве соединяемых таблиц... Но я еще не дочитала...

[ZlydenGL]

[quote="Rainbow"][/quote]
C нетерпением жду результата!!! Поскольку свой лоб я уже об эту стенку сломал

[Leon_]

Я сейчас на вас ругаться буду.. Зачем мы трое друг друга запутываем?
ЗлыденьGL спрашивал как ему сделать составной запрос, но НЕ програмно, потом вдруг стал говорить про работу кода.
И еще, можно подумать, вдруг случилось, что надо делать..

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

Можно подумать, что до тебя никто с этим никогда не сталкивался. Это все задача сервера БД -- обрабатывать "полеты" пользователя Как это делается в разных реализациях -- другой вопрос. Вот у тебя на чем стоит база? А клиенты? Какая схема данных?

Мало информации. Мы только путаем друг друга.

[ZlydenGL]

[quote="Leon_"][/quote]
Файл-сервер. База - M$ Access (см. первый пост), хранится на сервере с доступом через VPN (смотри какой-то там пост ), движок - DAO 3.60, среда разработки - VB6. Как показывает практика, в моем случае "полеты" пользователей ДАЛЕКО не всегда обрабатываются правильно.

Я спрашивал не про составной запрос, а как вообще реализовать UNION/VIEW запрос для 150 таблиц. К идее составного запроса пришел позже.

И потом, дело ведь даже не в полетах, а в многопользовательской среде... почитай мой пост насчет трех кверей.

[Leon_]

Ну вот другое дело. Я то, грешным делом, думал у тебя клиентское приложение тоже на Access, а у тебя эвон как.
Но это тоже не беда!
Иди к файлу MDB базы данных, и создай в нем этот самый объединяющий запрос. (Объекты базы данных--> Запросы--> Создать)
И тогда твои приложения-клиенты уже смогут использовать в коде VB не только имена таблиц, но и этот запрос.

[ZlydenGL]

[quote="Leon_"][/quote]
Если бы все так просто было!!! Попробуй для начала сам составить UNION/VIEW запрос хотя бы на 70 таблиц. Пусть даже через Запросы - Создать - Дизайн мод. Так ведь не получится...

[Leon_]

Да я даже пробовать не буду -- тот еще гемор Да у меня и такого количества таблиц нет.
Попробуй идти маленькими шагами, как тебе Rainbow советовала -- объедини в запросы по 30, потом из них -- в главный.
Все у тебя получится, я уверен. Access еще и не такую вложенность запроса потянет. Только не забывай делать UNION ALL, это значительно ускорит запрос.

[ZlydenGL]

[quote="Leon_"][/quote]
Необязательно иметь для этого 70 таблиц - достаточно 70 раз объединить одну таблицу. Точнее попытаться

Насчет вложенностей - уже трехкратная вложенность в Accesse приводит к глюкам с вычислениями, особенно если следом идет GROUP BY.

Если же сохранять несколько запросов по 30 допустим штук - все равно остается вопрос с многопользовательской средой.

[Leon_]

Не, парень, ты на меня свой trouble не перекладывай.
Что мне, делать нечего -- проверять ограничения Access'а ?:wink:
Я тебе даю реальный совет, но ты, похоже, в него не веришь. Остается дождаться других советчиков. Сейчас поближе к вечеру заглянут Ennor, Konst_One, Fedorov, alibek, Sedge.. да много кто, мастера то есть Вот они в раз рассудят.
Это "многопользовательская среда" тебя сбивает. А на самом деле все (99%) приложений БД так работает. И никаких проблем, что один запрос выполниться не успел, а тут уже другой и выборку первого "затирает" -- такие проблемы решаются в Microsoft и Oracle. Ты же не пишешь сервер базы данных (я правильно понял)?

[ZlydenGL]

[quote="Leon_"][/quote]
А! Я понял почему моя твоя не понимать.

С моей точки зрения последовательность действий была такая:

1. Кодом пишем первую кверю
2. Кодом пишем вторую кверю
...
n. Кодом пишем n-ную кверю.
n+1. Запускаем CurrentDB.Execute, объединяя UNION'ом n созданных кверей и формируя выгрузку данных.

Что же получается? Допустим первый юзверь записал Query1 с выборкой объектов с 1 по 70. Добрался до шага n. В этот момент второй юзверь запустил ту же процедуру - но для объекты с 50 по 120. ТЕКСТ первой квери переписывается. Потом первый пользователь выполняет завершающую стадию выборки... И выбирает черт те что (объекты 1-49 вообще не попали, с 50 по 70 записи задвоены... Продолжать? Если же использовать квери с именами пользователей... Тоже не прокатит, часть пользователей сидит под своими логинами на двух машинах (зачем оное надо - не знаю), посему пересечений возможно большое количество.

Кстати, Ennor уже здесь

[gaidar]

Судя по всему именно в длине строки запроса. Примерно те же сообщения выдаются когда пишется огромный запрос типа:

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

select field1, field2, ..., field140 from table_name where fieldxx = '1' and ...

Тут еще одна бяка есть, если на сервере нет места на диске, то большой запрос загнется по этой причине, причем сообщение об ошибке будет просто идиотское и из него пот=нять что-либо будет невозможно. (Кстати, такое у нас на сервере было, пока HDD не расширили).

Я встретился с похожей проблемой - помогал своему другу перелопатить базу. У него было порядка 600 таблиц (по каждому отделу каждого офиса организацции), которые приходилось синхронизировать. Я решил так - объединил все отделы в единую таблицу и припаял справочники с ID отдела и организации. В итоге получилась база из тер (или четырех) таблиц. Весом в 2 Гб (сейчас уже больше), тогда у него возникали проблемы именно с длиной запроса, когда он писал селекты с именами всех полей. Решили просто select * from ...

[ZlydenGL]

[quote="gaidar"][/quote]
На серваке места хватает - еще около двух гигов. При размере базы в 100 М похоже не это является причиной.

Насчет имени полей - везде пытаюсь использовать *, благо полей мало, и все - нужные

Есть все же возможность обойти сию бяку?

[gaidar]

Как уже писала Rainbow - объединить таблицы в меньшее число, либо разделить запросы на несколько частей - в одной части обработать 30 таблиц, в другой 30 и т.д и т.п.
В принципе, если дело только в длине запроса, то нужно сделать имена таблицам как можно короче. Но если и в числе таблиц, то только поэтапная обработка.
В крайнем случае, использование другой СУБД, например, Oracle

[Leon_]

С моей точки зрения последовательность действий была такая:
1. Кодом пишем первую кверю
2. Кодом пишем вторую кверю
...
n. Кодом пишем n-ную кверю.
n+1. Запускаем CurrentDB.Execute, объединяя UNION'ом n созданных кверей и формируя выгрузку данных..

А надо делать так:
В база.mdb создаешь запрос, объединяющий нужное кол-во таблиц. Если возникает ограничение на длину SQL-инструкции (а, похоже, это так), то создаешь этот сложный (состоящий из простых) запрос поэтапно. Это нужно сделать всего один раз, при желании даже можно программно.
А в клиентском приложении уже можно использовать этот Запрос, так же, как и одиночную таблицу.

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

Dim sQuery     As Long
sQuery = "Select Sum(Values) From Запрос Group By идентификатор"
CurrentDb.Execute sQuery

[Leon_]

OFF:
Вот я знал, знал я, что посоветует кто-нибудь Oracle или MS SQL Server
Это уже стало общим местом -- по ходу обругать Access и предлагать другие серверы. А когда проблема с Oracle случится, тогда что посоветуем?

[Rainbow]

Это нужно сделать всего один раз, при желании даже можно программно.

Один раз не сделать, тк не известно, какие таблицы объединять надо.

C нетерпением жду результата!!! Поскольку свой лоб я уже об эту стенку сломал

В MS ничего путного не нашла. В Гугле народ неуверенно говорит про max 32 таблицы.

Провела эксперимент: сделала запрос, соединяющий данные из одной таблицы. Влезло 50 таблиц. После этого сказал "Query is too complex". Поменяла название таблицы. исправила запрос. Результат тот же. У меня Access2000. Ограничение длины запроса, ворде тоже есть - 64К, но подтвердить документально не могу

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

Похоже, у него вообще ограничение на кол-во select'ов. Потому что исполнить Select * from MyQuery он не смог (в MyQuery было 50 select'ов)