Я почти убежден, что это явная ошибка во внутреннем соединении MS Access. Что действительно пугает, так как это то, что я должен использовать для разработки приложений. И нет, это не предполагаемое поведение внутреннего соединения - у меня есть 3 разных человека (включая меня) для подтверждения.
Таким образом, запрос реализует «наибольшее число групп» с помощью максимального запроса для получения наивысшего приоритета, а затем использует этот результат в качестве фильтра обратно к исходному для извлечения требуемых негрупповых полей. (Дополнительный вопрос - действительно ли «Первое» в упорядоченной таблице достаточно стабильно, чтобы использовать его за один проход?) Указанное негруппированное поле является частью ключа в третьей таблице.
Конкретная конфигурация этого запроса отбрасывает половину записей в определенном подмножестве моих данных (которое я увеличил). Если я переключаю соединения третьей таблицы на таблицу «максимального фильтра» вместо базовой таблицы, она внезапно работает правильно. (Рассматриваемые поля появляются во всех трех таблицах и уже объединены от «основы» к «максимальному фильтру».)
Моя группа работает по 11 полям. И замена копий объединенных полей меняет мой план выполнения запроса. Но версия без сортировки просто не работает. Я понятия не имею, как внутренние объединения и группировки могут взаимодействовать с ошибочным планом запроса, но, похоже, мне это удалось.
Я знаю, что настоящего sql-кода [сейчас опубликовано] нет, но он распределен по нескольким запросам и может потребовать данные, которые у меня есть. Есть ли способ загрузить настоящий файл .mdb, чтобы люди могли поиграть с копией, вызывающей ошибку? Я новичок в публикации, но мне еще не приходилось видеть сообщение с .mdb на нем, поэтому я не уверен, как лучше всего получить подробности, необходимые для этого.
Или, если кто-то имеет представление о том, что может происходить (несортированные объединения имеют ограничение в 10 полей?), Что может вызвать это, я рассмотрю это в понедельник. Раньше я изолировал утечки памяти в C, поэтому мне хотелось бы думать, что я кое-что знаю, но это просто сбивает с толку - мне действительно не нравится думать, что мне, возможно, придется реализовать внутреннее соединение в vba, потому что Я не могу доверять Access (и не могу получить зеленый свет на лучшие инструменты).
[Дальнейшее редактирование: я единственный разработчик этого процесса, так что на этот раз это не чей-то чужой код - моя проблема сама по себе.]
2013-12-30 edit - добавление стены SQL Wall of SQL:
Плохой запрос возвращает только 8 из 16 результатов на тестовом подмножестве:
SELECT
Allocations.Allocation_ID,
SLJ.Exp_Sign,
SLJ.Proj_ID,
SLJ.Doc_ID,
Allocations.Expense_Catagory,
SLJ.Prod_ID,
SLJ.OrigP_Type,
SLJ.OrigP,
SLJ.Class_ID,
Sum(SLJ.Total*[share]) AS Resposible_Doc_Count,
MP.MaxOfPriority,
SLJ.Priority,
MP.OrigP,
MP.Exp_Sign,
IIf(MP.exp_sign=SLJ.exp_sign,"true","false") AS What
FROM
(MP INNER JOIN SLJ
ON (MP.Exp_Sign = SLJ.Exp_Sign)
AND (MP.E_Delivery = SLJ.E_Delivery)
AND (MP.Expense_Type = SLJ.Expense_Type)
AND (MP.Proj_ID = SLJ.Proj_ID)
AND (MP.Doc_ID = SLJ.Doc_ID)
AND (MP.Doc_Typ_ID = SLJ.Doc_Typ_ID)
AND (MP.Prod_ID = SLJ.Prod_ID)
AND (MP.OrigP_Type = SLJ.OrigP_Type)
AND (MP.Class_ID = SLJ.Class_ID)
AND (MP.OrigP = SLJ.OrigP)
AND (MP.MaxOfPriority = SLJ.Priority)
AND (MP.Invested = SLJ.Invested))
INNER JOIN Allocations
ON (SLJ.Alloc_ID = Allocations.Allocation_ID)
AND (SLJ.Invested = Allocations.Invested)
AND (SLJ.E_Delivery = Allocations.E_Delivery)
AND (SLJ.Expense_Type = Allocations.Expense_Type)
GROUP BY
Allocations.Allocation_ID,
SLJ.Exp_Sign,
SLJ.Proj_ID,
SLJ.Doc_ID,
Allocations.Expense_Catagory,
SLJ.Prod_ID,
SLJ.OrigP_Type,
SLJ.OrigP,
SLJ.Class_ID,
MP.MaxOfPriority,
SLJ.Priority,
MP.OrigP,
MP.Exp_Sign,
IIf(MP.exp_sign=SLJ.exp_sign,"true","false");
Хороший запрос возвращает все 16 результатов:
SELECT Allocations.Allocation_ID,
SLJ.Exp_Sign,
SLJ.Proj_ID,
SLJ.Doc_ID,
Allocations.Expense_Catagory,
SLJ.Prod_ID,
SLJ.OrigP_Type,
SLJ.OrigP,
SLJ.Class_ID,
Sum(SLJ.Total*[share]) AS Resposible_Doc_Count,
MP.MaxOfPriority,
SLJ.Priority,
MP.OrigP,
MP.Exp_Sign,
IIf(MP.exp_sign=SLJ.exp_sign,"true","false") AS What
FROM
(MP INNER JOIN SLJ
ON (MP.Invested = SLJ.Invested)
AND (MP.MaxOfPriority = SLJ.Priority)
AND (MP.OrigP = SLJ.OrigP)
AND (MP.Class_ID = SLJ.Class_ID)
AND (MP.OrigP_Type = SLJ.OrigP_Type)
AND (MP.Prod_ID = SLJ.Prod_ID)
AND (MP.Doc_Typ_ID = SLJ.Doc_Typ_ID)
AND (MP.Doc_ID = SLJ.Doc_ID)
AND (MP.Proj_ID = SLJ.Proj_ID)
AND (MP.Expense_Type = SLJ.Expense_Type)
AND (MP.E_Delivery = SLJ.E_Delivery)
AND (MP.Exp_Sign = SLJ.Exp_Sign))
INNER JOIN Allocations
ON (Allocations.Expense_Type = MP.Expense_Type)
AND (Allocations.E_Delivery = MP.E_Delivery)
AND (Allocations.Invested = MP.Invested)
AND (SLJ.Alloc_ID = Allocations.Allocation_ID)
GROUP BY Allocations.Allocation_ID,
SLJ.Exp_Sign,
SLJ.Proj_ID,
SLJ.Doc_ID,
Allocations.Expense_Catagory,
SLJ.Prod_ID,
SLJ.OrigP_Type,
SLJ.OrigP,
SLJ.Class_ID,
MP.MaxOfPriority,
SLJ.Priority,
MP.OrigP,
MP.Exp_Sign,
IIf(MP.exp_sign=SLJ.exp_sign,"true","false");
Разница в конструкторе запросов - объединены "Распределения" для MP вместо SLJ для трех полей, которые существуют на обоих ... allocation_id только на SLJ. (Max_Priority и Stats_Loose_Join соответственно.)
Базовые запросы / таблицы:
MP:
SELECT
Max(SLJ.Priority) AS MaxOfPriority,
SLJ.Exp_Sign,
SLJ.Invested,
SLJ.E_Delivery,
SLJ.Expense_Type,
SLJ.Proj_ID,
SLJ.Doc_ID,
SLJ.Doc_Typ_ID,
SLJ.Prod_ID,
SLJ.OrigP_Type,
SLJ.OrigP,
SLJ.Class_ID
FROM SLJ
GROUP BY
SLJ.Exp_Sign,
SLJ.Invested,
SLJ.E_Delivery,
SLJ.Expense_Type,
SLJ.Proj_ID,
SLJ.Doc_ID,
SLJ.Doc_Typ_ID,
SLJ.Prod_ID,
SLJ.OrigP_Type,
SLJ.OrigP,
SLJ.Class_ID;
SLJ - фильтруется (через where) на тестовый набор из 4 записей:
SELECT
KS.Alloc_ID,
KS.Priority,
KS.Exp_Sign,
StatsT.*
FROM
KS,
StatsT
WHERE
(
(
(KS.Proj_ID) Is Null
Or (KS.Proj_ID)=[StatsT].[proj_id]
)
AND (
(KS.Doc_ID) Is Null
Or (KS.Doc_ID)=[StatsT].[doc_id]
)
AND (
(KS.Doc_Typ_ID) Is Null
Or (KS.Doc_Typ_ID)=[StatsT].[doc_typ_id]
)
AND (
(KS.Prod_ID) Is Null
Or (KS.Prod_ID)=[StatsT].[prod_id]
)
AND (
(KS.OrigP_Type) Is Null
Or (KS.OrigP_Type)=[StatsT].[OrigP_Type]
)
AND (
(KS.OrigP) Is Null
Or (KS.OrigP)=[StatsT].[OrigP]
)
AND (
(KS.Class_ID) Is Null
Or (KS.Class_ID)=[StatsT].[class_id]
)
AND ((StatsT.Doc_ID)=10437)
AND ((StatsT.Prod_ID)=104));
Результаты SLJ:
Alloc_ID Priority Exp_Sign Invested E_Delivery Expense_Type Proj_ID Doc_ID Doc_Typ_ID Prod_ID OrigP_Type OrigP Class_ID Total
1 10 Gross Y N Inforce 2013_199 10437 5 104 Fund_Doc 40 -1 1
1 10 Gross Y N Inforce 2013_199 10437 5 104 Fund_Doc 43 -1 1
-1 10 Reimb Y N Inforce 2013_199 10437 5 104 Fund_Doc 40 -1 1
-1 10 Reimb Y N Inforce 2013_199 10437 5 104 Fund_Doc 43 -1 1
StatsT:
SELECT
E2_In__Bound_Stats__Long.Invested,
E2_In__Bound_Stats__Long.E_Delivery,
E2_In__Bound_Stats__Long.Expense_Type,
E2_In__Bound_Stats__Long.Proj_ID,
E2_In__Bound_Stats__Long.Doc_ID,
E2_In__Docs_List__Doc.Doc_Typ_ID,
E2_In__Bound_Stats__Long.Prod_ID,
E2_In__Bound_Stats__Long.OrigP_Type,
E2_In__Bound_Stats__Long.OrigP,
E2_In__Bound_Stats__Long.Class_ID,
E2_In__Bound_Stats__Long.Total
FROM
E2_In__Bound_Stats__Long
INNER JOIN
E2_In__Docs_List__Doc
ON
E2_In__Bound_Stats__Long.Doc_ID = E2_In__Docs_List__Doc.Doc_ID;
Таблица KS (Kitchen_Sink) - 119 строк - пустые записи предотвращают первичный ключ:
Alloc_ID Priority Exp_Sign Proj_ID Doc_ID Doc_Typ_ID Prod_ID OrigP_Type OrigP Class_ID
Таблица Docs_List - 12465 строк - первичный ключ - Doc_ID:
Doc_ID Doc_Typ_ID
Таблица Bound_Stats - 1289 строк (или около 100k), только 4 выживают фильтр на SLJ - первичный ключ изначально не установлен, должно быть все, кроме Total, установка не устранила ошибку:
Invested E_Delivery Expense_Type Proj_ID Doc_ID Prod_ID OrigP_Type OrigP Class_ID Total
Планы запросов доступа:
Плохой план:
--- Matrix_Math_AAA ---
Входы в запрос
Table 'KS'
Table 'E2_In__Bound_Stats__Long'
Table 'E2_In__Docs_List__Doc'
Using index 'PrimaryKey'
Having Indexes:
PrimaryKey 12465 entries, 22 pages, 12465 values
which has 1 column, fixed, unique, primary-key, no-nulls
Doc_Type_ID1 12465 entries, 15 pages, 14 values
which has 1 column, fixed
Doc_ID1 12465 entries, 22 pages, 12465 values
which has 1 column, fixed
Table 'KS'
Table 'E2_In__Bound_Stats__Long'
Table 'E2_In__Docs_List__Doc'
Using index 'PrimaryKey'
Having Indexes:
PrimaryKey 12465 entries, 22 pages, 12465 values
which has 1 column, fixed, unique, primary-key, no-nulls
Doc_Type_ID1 12465 entries, 15 pages, 14 values
which has 1 column, fixed
Doc_ID1 12465 entries, 22 pages, 12465 values
which has 1 column, fixed
Table 'Allocations'
Завершить ввод в запрос
- Ограничьте строки таблицы E2_In__Bound_Stats__Long путем сканирования выражения тестирования "StatsT.Prod_ID = 104 And StatsT.Doc_ID = 10437"
- Результат внутреннего соединения '01) 'с таблицей' E2_In__Docs_List__Doc 'с использованием индекса' E2_In__Docs_List__Doc! PrimaryKey 'выражение соединения "E2_In__Bound_Stats__Long.Doc_ID = E2_In__Docs_List__Doc.Doc_ID"
- Таблица сортировки "Распределения"
- Результат внутреннего соединения '02) 'с результатом '03)' с использованием выражения временного индексного соединения "SLJ.Expense_Type = Allocations.Expense_Type And SLJ.E_Delivery = Allocations.E_Delivery And SLJ.Invested = Allocations.Invested"
- Сортировать результат '04) '
- Внутренняя таблица соединения 'KS' с результатом '05) 'с использованием выражения временного индексного соединения "SLJ.Alloc_ID = Allocations.Allocation_ID", затем тестовое выражение "(KS.Class_ID равен нулю или KS.Class_ID = StatsT.class_id) And ((KS .OrigP имеет значение Null или KS.OrigP = StatsT.OrigP) And ((KS.OrigP_Type is Null or KS.OrigP_Type = StatsT.OrigP_Type) And ((KS.Prod_ID Is Null or KS.Prod_ID = StatsT.prod_id) And ((( KS.Doc_Typ_ID равен нулю или KS.Doc_Typ_ID = StatsT.doc_typ_id) и ((KS.Doc_I "
- Ограничьте строки таблицы E2_In__Bound_Stats__Long путем сканирования выражения тестирования "StatsT.Prod_ID = 104 And StatsT.Doc_ID = 10437"
- Результат внутреннего соединения '07) 'с таблицей' E2_In__Docs_List__Doc 'с использованием индекса' E2_In__Docs_List__Doc! PrimaryKey 'выражение соединения "E2_In__Bound_Stats__Long.Doc_ID = E2_In__Docs_List__Doc.Doc_ID" сохранить результат во временной таблице
- Внутренняя таблица соединения 'KS' с результатом '08) 'с использованием соединения X-Prod, затем проверьте выражение "(KS.Class_ID Is Null или KS.Class_ID = StatsT.class_id) И ((KS.OrigP Is Null или KS.OrigP = StatsT.OrigP) и ((KS.OrigP_Type равен Null или KS.OrigP_Type = StatsT.OrigP_Type) And ((KS.Prod_ID is Null или KS.Prod_ID = StatsT.prod_id) And ((KS.Doc_Typ_ID Isoc_Typ_ID Isoc_Typ_ID Null Or = StatsT.doc_typ_id) И ((KS.Doc_I "
- Групповой результат '09) '
- Результат внутреннего соединения '06) 'с результатом '10)' с использованием выражения временного соединения индекса "SLJ.Exp_Sign = MP.Exp_Sign And SLJ.Invested = MP.Invested And SLJ.E_Delivery = MP.E_Delivery And SLJ.Expense_Type = MP .Expense_Type и SLJ.Proj_ID = MP.Proj_ID и SLJ.Doc_ID = MP.Doc_ID и SLJ.Doc_Typ_ID = MP.Doc_Typ_ID и SLJ.Prod_ID = MP.Prod_ID и SLJ.OrigP_Type = MP.OrigP_Type.Class, затем тестовое выражение = SLJ.Class_ID И (MP.OrigP = SLJ.OrigP И MP.MaxOfPriority = SLJ.Priority) "
- Групповой результат '11) '
Хороший план:
--- Matrix_Math_AAA_Good ---
Входы в запрос
Table 'KS'
Table 'E2_In__Bound_Stats__Long'
Table 'E2_In__Docs_List__Doc'
Using index 'PrimaryKey'
Having Indexes:
PrimaryKey 12465 entries, 22 pages, 12465 values
which has 1 column, fixed, unique, primary-key, no-nulls
Doc_Type_ID1 12465 entries, 15 pages, 14 values
which has 1 column, fixed
Doc_ID1 12465 entries, 22 pages, 12465 values
which has 1 column, fixed
Table 'KS'
Table 'E2_In__Bound_Stats__Long'
Table 'E2_In__Docs_List__Doc'
Using index 'PrimaryKey'
Having Indexes:
PrimaryKey 12465 entries, 22 pages, 12465 values
which has 1 column, fixed, unique, primary-key, no-nulls
Doc_Type_ID1 12465 entries, 15 pages, 14 values
which has 1 column, fixed
Doc_ID1 12465 entries, 22 pages, 12465 values
which has 1 column, fixed
Table 'Allocations'
Using index 'PrimaryKey'
Having Indexes:
PrimaryKey 312 entries, 4 pages, 312 values
which has 5 columns, fixed, unique, primary-key, no-nulls
Allocation_ID 312 entries, 1 page, 6 values
which has 1 column, fixed
Завершить ввод в запрос
- Ограничьте строки таблицы E2_In__Bound_Stats__Long, просмотрев тестовое выражение "StatsT.Prod_ID = 104 And StatsT.Doc_ID = 10437"
- Ограничьте строки таблицы E2_In__Bound_Stats__Long, просмотрев тестовое выражение "StatsT.Prod_ID = 104 And StatsT.Doc_ID = 10437"
- Результат внутреннего соединения '02) 'с таблицей' E2_In__Docs_List__Doc 'с использованием индекса' E2_In__Docs_List__Doc! PrimaryKey 'выражение соединения "E2_In__Bound_Stats__Long.Doc_ID = E2_In__Docs_List__Doc.Doc_ID" сохранить результат во временной таблице
- Внутреннее соединение таблицы 'KS' с результатом '03) 'с использованием соединения X-Prod, затем проверьте выражение "(KS.Class_ID Is Null или KS.Class_ID = StatsT.class_id) И ((KS.OrigP Is Null или KS.OrigP = StatsT.OrigP) и ((KS.OrigP_Type равен Null или KS.OrigP_Type = StatsT.OrigP_Type) And ((KS.Prod_ID is Null или KS.Prod_ID = StatsT.prod_id) And ((KS.Doc_Typ_ID Isoc_Typ_ID Isoc_Typ_ID Null Or = StatsT.doc_typ_id) И ((KS.Doc_I "
- Групповой результат '04) '
- Внутренняя таблица соединения «KS» с результатом «05)» с использованием выражения временного индексного соединения «SLJ.Exp_Sign = MP.Exp_Sign», затем проверьте выражение «MP.MaxOfPriority = SLJ.Priority»
- Сортировка результата '06) '
- Внутренний результат соединения '01) 'с результатом '07)' с использованием выражения временного индексного соединения "SLJ.E_Delivery = MP.E_Delivery And SLJ.Expense_Type = MP.Expense_Type And SLJ.Proj_ID = MP.Proj_ID And SLJ.Doc_ID = MP .Doc_ID И SLJ.Prod_ID = MP.Prod_ID И SLJ.OrigP_Type = MP.OrigP_Type И SLJ.Class_ID = MP.Class_ID И SLJ.OrigP = MP.OrigP И SLJ.Invested = MP.Invested "затем проверьте выражение" (KS. Class_ID имеет значение Null или KS.Class_ID = StatsT.class_id) And ((KS.OrigP Is Null или KS.OrigP = StatsT.OrigP) And ((KS.OrigP_Type Is Null или KS.OrigP_Type = StatsT.OrigP_Type) And ((KS.OrigP_Type) .Prod_ID равен нулю или KS.Prod_ID = StatsT.prod_id) и ((KS.Doc_ID равен нулю или KS.Doc_ID = StatsT.doc_id) и (KS.Proj_ID равен нулю или "
- Результат внутреннего соединения '08) 'с таблицей' Allocations 'с использованием индекса' Allocations! PrimaryKey 'выражение соединения "SLJ.Alloc_ID = Allocations.Allocation_ID And MP.Invested = Allocations.Invested And MP.E_Delivery = Allocations.E_Delivery And MP.Expense_Type = Allocations.Expense_Type "
- Результат внутреннего соединения '09) 'с таблицей' E2_In__Docs_List__Doc 'с использованием индекса' E2_In__Docs_List__Doc! PrimaryKey 'выражение соединения "E2_In__Bound_Stats__Long.Doc_ID = E2_In__Docs.Docs.Docs.Docs.Doc_Doc.Doc_ID", затем KJP_List__Doc.Doc_ID_Doc_Ty_Ty_png KS.Doc_Typ_ID = StatsT.doc_typ_id) "
- Групповой результат '10) '
Конец 2013-12-30 править