| |
 24 Кб. |
|
| | Задача есть 2 таблицы tblAliases и ostmp_tblAlias почти одинаковой структуры и к ним кадой по справочнику соответвенно tblAlias_Org и ostmp_tblAliasOrg (чтобы снять вопросы о нормализации, поясню, одна таблица это фактически прилинкованный тектовый файл из которого данные, после обработки, попадут во вторую таблицу).
суть таблиц такова
Есть 2 набора алиасов (2 таблицы) существующие и новые, каждому алиасу соответствует один или несколько огранизмов (одна или несколько строк в таблице tblAlias_Org или ostmp_tblAliasOrg)
так вот структура этих таблиц
AliasID - уникальный ключ
ValTok - тектовое поле длинной 255 символов
CaseMaskVal - тектовое поле состоящее из 0 и 1 описывающее положение больших и маленьких букв в поле ValTok, например
ValTok = M2M, CaseMaskVal = 101
ValTok = M2m, CaseMaskVal = 100
ValTok = m2M, CaseMaskVal = 0
в общем это поле нужно для сравнения ValTok с учетом регистра.
все остальные поля не интересны
Далее справочник tblAlias_Org (ну или ostmp_tblAliasOrg для новых алиасов)
AliasID - код алиаса длинное целое
OrgID - код организма Длинное целое (есть еще таблица tblOrg которая содержит все возможные организмы)
Особенность!!!! Если у алиаса установлено orgID = 1 это значит у этого алиаса есть ВСЕ ДОСТУПНЫЕ ОРГАНИЗМЫ.
Задача найти в этих двух таблицах (tblAliases и ostmp_tblAlias) записи совпадающие по
ValTok
CaseMaskVaL
OrgID из соответсвующего справочника
Нужно учесть что если OrgID = 1, то эта запись будет совпадать со всеми записями у которых такие же ValTok + CaseMaskVaL даже если OrgID какой-то другой.
Спасибо всем кто дочитал досюда....
Ка сделано сейчас.
Для разрешения проблемы с OrgID создан запрос который для OrgID = 1 создает все созможные OrgId, т.е. для записи OrgId = 1 "создает" много записей (столько сколько вообще есть этих OrgID)
назвается qAllOrg
Создан запрос который содержит записи из tblAliases со всеми возможными кодами организмов назвается qExistAliasWAllOrg
Создан запрос который содержит записи из stmp_tblAlias со всеми возможными кодами организмов назвается qNewAliasWAllOrg
Ну и результирующий запрос который выводит в отдельную таблицу AliasID для записей у которых совпадает
ValTok
CaseMaskVaL
OrgID
называется этот запрос qAddIdAliasWNotUniqVal
Все это добро можно увидеть в аттаче.
Теперь собственно вопрос:
реально размеры таблиц такие
tblAliases - 600'000 записей (это существующие алиасы, каждой соответствует от 1 до 5 записей в таблице tblAlias_Org, но для id = 1 они размножаются до 12)
ostmp_tblAlias - от 100 до 100'000 записей (это новые алиасы каждой соответствует от 1 до 5 записей в таблице ostmp_tblAliasOrg но для id = 1 они размножаются до 12)
Все работает если записей в новой таблице 200, медленно но работает, а на 100'000 запрос работал 2-е суток, и не отработал.
Задача, снизить время хотя бы да нескольких часов.
Первое что приходит на ум этозаменить запросы на выборку qExistAliasWAllOrg и qNewAliasWAllOrg запросами на добавление во временные таблицы и уже эти таблицы использовать в результирующем запросе qAddIdAliasWNotUniqVal, но может есть другие идеи? Может запросы как переписать?
Файл в формате 2000 прилагается, таблицы почистил, так что бы весил поменьше.
Понимаю что "очень многа букав", и вникать э это добро довольно сложно, так что всем кто хотя бы прочитал - огромное спасибо!  | |
| |
| |
| |
|
|
| | Проблему вижу в том, что слишком большие объемы загружаются в оперативку.
Надо как-то "дробить" задачу на части.
Олег, а ресурсов хватит, что-бы открыть рекордсет на запросе "qExistAliasWAllOrg"?
Я к тому, что в нем (рекордсете), при исходных 600'000 записей в таблице "tblAliases" будет порядка
3'373'333 записей (примерно 800-850 Мб). | |
| |
| |
| |
|
|
| | ХЗ. сообщений о нехватке ресурсов не выскакивает.
Я сам на таких объемах не проверял, т.к. я не могу по полдня ждать что бы увидеть что работает не так, у меня все скромнее
существующие - 300'000
новые 500, отработало примерно за час. Я думал что ну пусть время увеличится в 5 раз (в принципе приемлимо), но я крупно ошибся. Заказчик готовит 2 дня ждал, судя по разговорам машинка у него серьезная.
Готов услышать любые идеи, хоть по частям, хоть через временные таблицы
"хоть тушкой, хоть чучелкой...."
Только пока не придумал как раздробить на части. | |
| |
| |
| |
|
|
| | я дробил так:
1. если были промежуточные запросы - формировал из них таблицы и потом подсоединял в итоговый
2. подправил на предмет работы exists и left join - в моем случае left join отрабатывл быстрее чем , not exists (вроде не путаю)
3. в запросах, чтобы уменьшить количество записей, добавлял фильтр по тем сучьностям, которые потенциально могут быть выбраны...
на данном этапе это помогло формировать таблицу не за несколько часов(суток), а за 10-15 минут
но это мой, частный случай, Олег.  | |
| |
| |
|
|
| |
|
|
| | А скока весят 300'000 и 500 новых? Ко мне никак по почте?
Я поднял копированием количество записей до 600'000 и 100'000, но они копии и в итоговом все-равно мало.
Добавлено: по почте вряд-ли. | |
| |
| |
| |
|
|
| | да в принципе могу куда-нибудь залить. а ты в таблицах с организмами тоже дубли создал?
вообще по идее в итоговом и должно быть не много, и торможение именно из-за большого кол-ва в исходных. Сколько времени работало? Как делал?
Весят 216 мегов в архиве 63 мега.
ну правда кое что можно пожать, но думаю меньше 50 мегов не получится...
хотя есть идея.... выгнать в текст... попробую | |
| |
| |
| |
|
|
| | В текст ненадо, индексы и прочаяя тоже нужны.
Можешь архив на сайт положить? Я скачаю. | |
| |
| |
| |
|
|
| | так я положу структуру в MDB и данные в тексте. Но все позже, так что до завтра можешь не смотреть | |
| |
| |
|
|
| |
|
|
| | просидел до 12 ночи, заменил запросы на выборку запросами на добавление во временные таблицы. Во временных таблицах добавил индексов во всех полях по которым будет сравнение. Отправл заказчику - ушел спать. (у нас с заказчиком разница - 8 часов, так что у него рабочий день как раз в разгаре)
Утром получил ответ. Все отработало!!!
Так что извините за беспокойство, пока на этом остановлюсь, еще кучу всего надо сделать. | |
| |
| |
| |
|
|
| | На тот случай, если придется возвращаться к этому вопросу:
Если у алиаса установлено orgID = 1 это значит у этого алиаса есть ВСЕ ДОСТУПНЫЕ ОРГАНИЗМЫ
|
Для записей с orgID = 1 не нужно объединение с запросом "qAllOrg",
достаточно сравнивать новые и имеющиеся записи по двум полям.
Для записей с orgID <> 1 не нужно объединение с таблицей "tblOrg",
так как поле orgID есть в таблицах ...Alias_Org.
Задачу можно разбить на два потока:
1. orgID = 1
2. orgID <> 1
Даже на тестовых данных ускорение заметно "на глаз". | |
| |
| |
| |
|
|
| | я тоже рассматривал этот вариант как первый шаг к оптимизации | |
| |
| |
| |
|
|
| | Я думал в будующем разбить на 2 этапа
1-й сравнение без учета организмов только по ValTok и CaseMaskVal (тогда сразу резко уменьшается кол-во записей)
2-й сравнение совпадающих по набору организмов. | |
| |
| |
| |
|
|
| | штота мне CaseMaskVal совсем не нравится  | |
| |
| |
| |
|
|
| | всмысле?
вот обсуждение
http://hiprog.com/forum/read.php?id_forum=1&id_theme=5687&page=1 | |
| |
| |
