ReSQL.ru
     
powered by simpleCommunicator - 2.0.38     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / оптимизация left outer join (ФИАС)
11 сообщений из 11, страница 1 из 1
оптимизация left outer join (ФИАС)
    #37757432
burashka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
burashka
Гость
Доброго времени суток,

видел, что существует уже достаточно много тем про оптимизацию 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, 16:06
| Ответить | Цитировать | Написать  
оптимизация left outer join (ФИАС)
    #37757960
RXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RXL
Участник
burashka,

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

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

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

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

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

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

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


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