Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 indexed view для оптимизации запроса по подстроке  [new]
баззззлайтер
Guest
задача: отчёт по статистике посещения сайта:
create table hit (id int identity(1,1) primary key clustered, url varchar(255))
insert into hit(url) values ('www.google.com')
insert into hit(url) values ('www.google.ru')
insert into hit(url) values ('www.yahoo.com')
insert into hit(url) values ('www.yahoo.ru')
insert into hit(url) values ('www.udaff.com')

create table domain(name varchar(255) primary key clustered)
insert into domain (name) values ('google')
insert into domain (name) values ('yahoo')

select isnull(domain.name,'other'), count(*) 
from hit 
left outer join domain on hit.url like '%'+domain.name+'%' 
group by isnull(domain.name,'other')
order by count(*) desc

нужно создать indexed view, такие, чтобы он работал с приемлемой скоростью на реальных объёмах данных. Таблицы создавать нельзя по условию.

hint: url часто повторяются, и половина хитов попадает под категорию 'other'. ну т.е. приджойнить и проиндексировать по всем полям, наверное, решением не считается.
14 окт 05, 00:52    [1967938]     Ответить | Цитировать Сообщить модератору
 Re: indexed view для оптимизации запроса по подстроке  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
а если урл, например, такой www.yahoo.com/index.html?www.google.com
поиск по подстроке домена будет истиным и для яху и для гугла...

вообще, домены организуются не так.
сначала верхний уровень (com, ru, org и т.п.), затем второй уровень (yahoo, google и т.п.) и т.д.
14 окт 05, 01:11    [1967947]     Ответить | Цитировать Сообщить модератору
 Re: indexed view для оптимизации запроса по подстроке  [new]
баззззлайтер
Guest
трёп не по делу тоже решением не считается :-)
14 окт 05, 01:23    [1967952]     Ответить | Цитировать Сообщить модератору
 Re: indexed view для оптимизации запроса по подстроке  [new]
aleks2
Guest
баззззлайтер
трёп не по делу тоже решением не считается :-)


Ладно... по делу

on hit.url like '%'+domain.name+'%'

никогда не будет использовать индекс - хоть удавись.

Решение:

X-вычисляемых полей в table hit

domain0 as SUBSTRING(url, LEN(url)-CHARINDEX('.', REVERSE(url)), CHARINDEX('.', REVERSE(url))),
domain1 as ...
domain2 as ...

индексы по этим полям

ну и... будет быстро...
14 окт 05, 07:23    [1968057]     Ответить | Цитировать Сообщить модератору
 Re: indexed view для оптимизации запроса по подстроке  [new]
Glory
Member

Откуда:
Сообщений: 104760
баззззлайтер
hint: url часто повторяются, и половина хитов попадает под категорию 'other'. ну т.е. приджойнить и проиндексировать по всем полям, наверное, решением не считается.

Url-ы у вас действительно url-ы или именно такие как вы привели в своем примере ?
14 окт 05, 10:02    [1968331]     Ответить | Цитировать Сообщить модератору
 Re: indexed view для оптимизации запроса по подстроке  [new]
Crimean
Member

Откуда:
Сообщений: 13148
"." объявить разделителем
поставить Search
пользовать CONTAINS
14 окт 05, 11:08    [1968694]     Ответить | Цитировать Сообщить модератору
 Re: indexed view для оптимизации запроса по подстроке  [new]
баззззлайтер
Guest
url-ы действительно url-ы, кстати.

за вариант с FTS спасибо, он не годится, но я про него совсем не думал.

закладываться на частные случаи функции url->domain как "domain это подстрока url между двумя точками" я не могу, там более сложная может быть зависимость. (например все URL на серверх моего босса соответствуют domain='self'. A dns имён там много)

Слово domain я специально для форума придумал, и это не domain в смысле dns, извините если запутал.

Короче, суть проблемы:
Есть большая таблица T(id,x) и функция f(x) (которая, да, сложная, и индексы не использует). Чтобы искать по f(x) можно
1.добавить вычисляемое поле в T(id,x,f(x)), проиндексировать и искать по нему
2.создать F(x,f(x)) и использовать в каждом запросе.

Условиям задачи второе решение соответствует гораздо лучше, потому что F получится существенно меньше T (в 2300 раз примерно), соответственно меньше индексы, время на перестройку, и.т.д

Однако используя только индексированые view я могу реализовать только первое, и это меня настораживает... Может, есть какое объяснение?
14 окт 05, 19:11    [1971526]     Ответить | Цитировать Сообщить модератору
 Re: indexed view для оптимизации запроса по подстроке  [new]
aleks2
Guest
баззззлайтер
url-ы действительно url-ы, кстати.

за вариант с FTS спасибо, он не годится, но я про него совсем не думал.

закладываться на частные случаи функции url->domain как "domain это подстрока url между двумя точками" я не могу, там более сложная может быть зависимость. (например все URL на серверх моего босса соответствуют domain='self'. A dns имён там много)

Слово domain я специально для форума придумал, и это не domain в смысле dns, извините если запутал.

Короче, суть проблемы:
Есть большая таблица T(id,x) и функция f(x) (которая, да, сложная, и индексы не использует). Чтобы искать по f(x) можно
1.добавить вычисляемое поле в T(id,x,f(x)), проиндексировать и искать по нему
2.создать F(x,f(x)) и использовать в каждом запросе.

Условиям задачи второе решение соответствует гораздо лучше, потому что F получится существенно меньше T (в 2300 раз примерно), соответственно меньше индексы, время на перестройку, и.т.д

Однако используя только индексированые view я могу реализовать только первое, и это меня настораживает... Может, есть какое объяснение?


Вестимо есть... если ты прояснишь что тако F(x,f(x))?
16 окт 05, 11:31    [1973141]     Ответить | Цитировать Сообщить модератору
 Re: indexed view для оптимизации запроса по подстроке  [new]
баззззлайтер
Guest
F(x,f(x)) вестимо материализованое отношение x->f(x). Таблица, если тебе так проще.

в терминах исходной задачи это
create table KnownUrls (url varchar(255) primary key,domain varchar(255))
insert into KnownUrls(url,domain) values ('www.google.com','google')
insert into KnownUrls(url,domain) values ('www.google.ru','google')
..и.т.д
17 окт 05, 07:39    [1973728]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить