Сега съм във финалните етапи на надграждане на йерархичния дизайн в голяма транзакционна система и известно време се взирам в тази заявка от 150 реда (която ще ви спестя досадата от четене) и си мисля, че има трябва да е по-добър начин.
Кратко обобщение на въпроса е както следва:
Как бихте приложили йерархично търсене, което отговаря на няколко думи за търсене на различни нива в йерархията, оптимизирано за най-бързо време за търсене?
Намерих донякъде свързан въпрос, но всъщност е само около 20% от отговора, от който всъщност се нуждая. Ето пълния сценарий/спецификация:
- Крайната цел е да намерите един или няколко произволни елемента на произволни позиции в йерархията.
- Пълната йерархия е около 80 000 възли, които се очаква да нараснат до 1 милион в рамките на няколко години.
- Пълният текст на целия път надолу по йерархията е уникален и описателен; обаче текстът на отделен възел може да не е такъв. Това е бизнес реалност, а не решение, взето с лека ръка.
- Пример: възел може да има име като „Врата“, което е безсмислено само по себе си, но пълният контекст, „Аарон > Къща > Дневна > Шкаф за алкохол > Врата“ има ясно значение, описва конкретна врата на определено място. (Имайте предвид, че това е само пример, реалният дизайн е много по-малко тривиален)
- За да намери тази конкретна врата, потребителят може да напише "aaron liquor door", което вероятно ще покаже само един резултат. Заявката се превежда като последователност: елемент, съдържащ текста "door", под елемент, съдържащ текста "liquor", под друг елемент, съдържащ текста "aaron."
- Или потребителят може просто да напише "домашен алкохол", за да изброи всички шкафове за алкохол в къщите на хората (не би ли било хубаво). Споменавам този пример изрично, за да посоча, че търсенето не трябва да съответства на конкретно коренно или листно ниво. Този потребител знае точно коя врата търси, но не може да си спомни веднага кой я притежава и би запомнил, ако името изскочи пред него.
- Всички термини трябва да бъдат съпоставени в указаната последователност, но както показват горните примери, нивата в йерархията могат да бъдат "пропуснати". Терминът „шкаф за алкохол на Аарон“ няма да съответства на този възел.
- Платформата е SQL Server 2008, но смятам, че това е проблем, който не зависи от платформата и предпочитам да не ограничавам отговорите до тази платформа.
- Самата йерархия се основава на
hierarchyid
(материализиран път), индексиран както в ширина, така и в дълбочина. Всеки йерархичен възел/запис имаName
колона, към която трябва да се направи заявка. Йерархичните заявки, базирани на възела, са изключително бързи, така че не се притеснявайте за тях. - Няма строга йерархия - един корен може изобщо да не съдържа възли или може да съдържа 30 поддървета, разпръснати до 10 000 листови възли.
- Максималното влагане е произволно, но на практика има тенденция да бъде не повече от 4-8 нива.
- Йерархията може и се променя, макар и рядко. Всеки възел може да бъде преместен във всеки друг възел, с очевидните изключения (родител не може да бъде преместен в собствения си дъщерен възел и т.н.)
- В случай, че това вече не се подразбира: имам контрол върху дизайна и мога да добавя индекси, полета, таблици, каквото може да е необходимо за постигане на най-добри резултати.
Моята „мечта“ е да предоставя незабавна обратна връзка на потребителя, както при прогресивно търсене/филтър, но разбирам, че това може да е невъзможно или изключително трудно. Ще се радвам на всяко значително подобрение спрямо текущия метод, който обикновено отнема между 0,5s до 1s в зависимост от броя на резултатите.
За пълнота съществуващата заявка (съхранена процедура) започва със събиране на всички листови възли, съдържащи крайния термин, след което се присъединява нагоре и изключва всички, чиито пътища не съвпадат с по-ранните термини. Ако на някого това изглежда назад, бъдете сигурни, че е много по-ефективно, отколкото да започнете с корените и да ги разпръснете. Това беше „старият“ начин и лесно можеше да отнеме няколко секунди на търсене.
И така, въпросът ми отново: Има ли по-добър (по-ефективен) начин за извършване на това търсене?
Не търся непременно код, просто подходи. Обмислих няколко възможности, но всички изглежда имат някои проблеми:
- Създайте разделена колона „текст на пътя“ и я индексирайте с пълнотекстово търсене. Проблемът е, че търсене в тази колона ще върне и всички дъщерни възли; "къща на Аарон" съвпада и с "кухня в къщата на Аарон" и "сутерен на къща на Аарон".
- Създаде колона
NamePath
, която всъщност е вложена последователност от низове, използвайки тип CLR, подобен на самияhierarchyid
. Проблемът е, че нямам представа как Microsoft може да "преведе" заявки от този тип в индексни операции и дори не съм сигурен дали е възможно да се направи на UDT. Ако крайният резултат е просто сканиране на пълен индекс, не съм спечелил нищо с този подход.
Всъщност не е краят на света, ако не мога да направя нещо по-добро от това, което вече имам; търсенето е "доста бързо", никой не се е оплакал от него. Но съм готов да се обзаложа, че някой вече се е занимавал с този проблем и има някои идеи. Моля, споделете ги!