Гость
Войти | Профиль | Очистить
Действия ...
Форумы / SQLite [игнор отключен] [закрыт для гостей] / оптимизация left outer join (ФИАС) / 11 сообщений из 11, страница 1 из 1
17.04.2012, 16:06
    #37757432
burashka
burashka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
Доброго времени суток,

видел, что существует уже достаточно много тем про оптимизацию JOIN-ов в SQLite, но к своему стыду с пользой применить к своей проблеме не удалось

есть база адресов(около 2,5Г), в ней лежат актуальные названия улиц и устаревшие. Нужно показывать новые и рядом их устаревшее наименование, если оно есть. Более подробное описание базы: http://fias.nalog.ru/Public/DownloadPage.aspx

делаю следующий запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
SELECT
  ADDMAIN.STREETCODE,
  ADDMAIN.FORMALNAME,
  ADDMAIN.SHORTNAME,
  ADDOLD.FORMALNAME AS NAMEOLD,
  ADDOLD.SHORTNAME AS SHORTOLD
FROM
  ADDROBJ ADDMAIN INDEXED BY "ALL"
  LEFT OUTER JOIN ADDROBJ ADDOLD
  ON ADDOLD.STREETCODE = ADDMAIN.STREETCODE AND
       ADDOLD.REGIONCODE = ADDMAIN.REGIONCODE AND
       ADDOLD.AREACODE = ADDMAIN.AREACODE AND
       ADDOLD.CITYCODE = ADDMAIN.CITYCODE AND
       ADDOLD.AUTOCODE = ADDMAIN.AUTOCODE AND
       ADDOLD.CTARCODE = ADDMAIN.CTARCODE AND
       ADDOLD.PLACECODE = ADDMAIN.PLACECODE AND 
       ADDOLD.ACTSTATUS <> '1'
WHERE 
  ADDMAIN.ACTSTATUS = '1' AND 
  ADDMAIN.AUTOCODE = '0' AND 
  ADDMAIN.AREACODE = '000' AND 
  ADDMAIN.REGIONCODE = '77' AND 
  ADDMAIN.CITYCODE = '000' AND 
  ADDMAIN.CTARCODE = '000' AND 
  ADDMAIN.PLACECODE = '000' AND 
  ADDMAIN.STREETCODE <> '0000'



экпериментально установлено, что сам SQLite выбирает индекс:
Код: sql
1.
2.
CREATE INDEX "ALL_NOT_AS"
ON "ADDROBJ" ("AREACODE" ASC, "AUTOCODE" ASC, "CITYCODE" ASC, "PLACECODE" ASC, "CTARCODE" ASC, "REGIONCODE" ASC, "FORMALNAME" ASC)


и руками ему указываю
Код: sql
1.
2.
CREATE INDEX "ALL"
ON "ADDROBJ" ("AREACODE" ASC, "ACTSTATUS" ASC, "AUTOCODE" ASC, "CITYCODE" ASC, "PLACECODE" ASC, "STREETCODE" ASC, "CTARCODE" ASC, "REGIONCODE" ASC)



Работает сие счастье на 3,5к московских улиц около минуты. Прошу помочь, пожалуйста, как можно оптимизировать данный запрос. И как в целом можно ускорить работу left outer join-ов. Остальные игрой с индексами удалось заставить мало-мальски прилично работать, но все-равно хочется быстрее. Вроде встречал, что имеет смысл увеличить размер страницы, но не совсем понял как это можно сделать и насколько именно стоит

P.S.: база - ФИАС, который пришел на смену КЛАДР. Может кто работает(ал)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.04.2012, 20:04
    #37757960
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
burashka,

Операция <> не может использовать индекс.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 10:30
    #37758548
metos
metos
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
burashka,

Оптимизируй саму БД под твои запросы. Благо сама БД практически статична.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 10:47
    #37758580
burashka
burashka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
RXL, исключил из индексов поля с неравно

при индексах
Код: sql
1.
2.
CREATE INDEX "ALL_NOT_A"
ON "ADDROBJ" ("AREACODE" ASC, "AUTOCODE" ASC, "CITYCODE" ASC, "PLACECODE" ASC, "STREETCODE" ASC, "CTARCODE" ASC, "REGIONCODE" ASC)


и
Код: sql
1.
2.
CREATE INDEX "ALL_NOT_S"
ON "ADDROBJ" ("AREACODE" ASC, "ACTSTATUS" ASC, "AUTOCODE" ASC, "CITYCODE" ASC, "PLACECODE" ASC, "CTARCODE" ASC, "REGIONCODE" ASC)



1,5c на извлечение 3500 записей из примерно 1,3млн. Для улиц явно долго, для базы не знаю. Реально быстрее сделать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 10:59
    #37758604
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
burashka,

Конечно реально. Переделайте базу полностью: табличка регионов, городов, улиц, у всех числовые идентификаторы-PK, нормализация, логичные уникальные индексы, никаких километровых индексов по хрен-знает-каким-полям.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 11:00
    #37758607
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
А это у вас часом не КЛАДР?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 11:15
    #37758645
burashka
burashka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
RXL, почти. я же приписал в PS, что это ФИАС, наследник КЛАДРа. Сам КЛАДР умер:
http://habrahabr.ru/post/140378/

Разбить на таблички, кажется, не поможет. Там все-равно понадобятся left join-ы в которых весь затык. like по всей базе с целью найти все населенные пункты начинающийся с определенного сочетания двух букв - 0,5-0,6мс. получить с помощью join названия вместо кодов родительских региона, района и т.п. - 6-10с

хотя, конечно, к улицам можно заранее присобачить старые названия. но тут вопрос: как в sqlite сделать update-select? если пишу update tab1, tab2 set ... - ошибка. если update tab1 set pol = (select tab2.pol from tab2 where tab1.pol2 = tab2.pol2) - то пишет, что не найдено поле tab1.pol2

скоро скину на боевой сервер, посмотрю как на нем будет. может кеширование при большом потоке запросов спасет, если нет - придется думать как перелопатить структуру
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 21:16
    #37759964
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
burashka,

Не обязательно пользоваться именно той структурой базы. Можно иметь свою и написать конвертер.

Попробуйте изменить поля xxxxCODE на INTEGER NOT NULL и сделать индекс согласно иерархии, описанной в п.2.1.1 документации. Можно сделать уникальный индекс по всем этим полям или неуникальный, исключив несколько полей справа.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 21:19
    #37759971
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
Предыдущий в иерархии объект можно выбрать также посредством PARENTGUID.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 21:22
    #37759974
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
авторADDOLD.ACTSTATUS <> '1'

Учитывая, что значений всего два - 0 и 1, сравнение "<> 1" эквивалентно "= 0".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2012, 21:24
    #37759978
RXL
RXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация left outer join (ФИАС)
авторADDMAIN.STREETCODE <> '0000'

Гы :)
"> 0"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / SQLite [игнор отключен] [закрыт для гостей] / оптимизация left outer join (ФИАС) / 11 сообщений из 11, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 414ms
Закрыть
start [/forum/topic.php?fid=54&mobile=1&tid=2009042]:
 0ms
get settings:
 11ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 43ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 280ms
total:  414ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]