Гость
Войти | Профиль | Очистить
Действия ...
Форумы / SQLite [игнор отключен] [закрыт для гостей] / SQLite: Как правильно готовить LEFT OUTER JOIN ? / 9 сообщений из 9, страница 1 из 1
13.05.2009, 10:17
    #35982308
agakhov
agakhov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
Добрый день,
к сожалению я не профи в по работе с бд, заранее извиняюсь если вопрос тривиальный.

работаю с sqlite встроенной в adobe air

Столкнулся с очень низкой производительностью запроса на базе конструкции LEFT OUTER JOIN.
Интерес вызван тем, что в документации рекомендуется использовать именно JOIN и избегать подзапросов и IN
Возможно я не правильно готовлю LEFT OUTER JOIN.

Иходные таблицы:
1) Groups - (в запросе не участвует привидена для общей картины) , 2000 строк

2) Devices - (устройства) , 30000 строк
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
		
CREATE TABLE IF NOT EXISTS Devices 
(
id INTEGER NOT NULL PRIMARY KEY UNIQUE,
name VARCHAR( 45 ) default NULL,
...(примерно  50  полей свойств) 
)

3) DeviceDetails - таблица связей. Один device может входить в несколько групп. 80000 строк
Код: plaintext
1.
2.
3.
4.
5.
6.
			
CREATE TABLE IF NOT EXISTS DeviceDetails
(
deviceId INTEGER NOT NULL,
groupId INTEGER NOT NULL
)

Задача получить девайсы (Device) по заданной группе.

Конструкция с JOIN на рутовой группе отрабатывает 38 секунд (результат 14000 элементов):
Код: plaintext
1.
2.
3.
SELECT * FROM Devices as d LEFT OUTER JOIN DeviceDetails as a
ON a.deviceId = d.id 
WHERE a.groupId = :groupId
Конструкция с подзапросом отрабатывает 2 секунды:
Код: plaintext
1.
2.
SELECT * FROM Devices WHERE id in(
SELECT deviceId from DeviceDetails WHERE groupId = :groupId)

добавление индексов, ощутимого прироста производитьльности не даёт.
Код: plaintext
1.
2.
3.
4.
5.
CREATE INDEX IF NOT EXISTS GroupIndex
on DeviceDetails (groupId);

CREATE INDEX IF NOT EXISTS DeviceIndex
on DeviceDetails (deviceId);

Буду благодарен если поможете прояснить ситуацию.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.05.2009, 11:30
    #35982495
Bard
Bard
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
Сначала попробуй
Код: plaintext
1.
ANALYZE Devices
ANALYZE DeviceDetails
и запрос, по-моему, у тебя должен выглядеть так :
Код: plaintext
1.
2.
3.
SELECT *
FROM DeviceDetails as a
   INNER JOIN Devices as d ON a.deviceId = d.id 
WHERE a.groupId = :groupId
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.05.2009, 17:26
    #35983748
White Owl
White Owl
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
agakhovЗадача получить девайсы (Device) по заданной группе. Здесь действительно нужен inner а не outer join.
Можно сделать как уже показал Bard, а можно перевернуть порядок таблиц и сделать вот так:
Код: plaintext
1.
2.
SELECT *
FROM Devices as d
   INNER JOIN DeviceDetails as a ON a.deviceId = d.id and a.groupId = :groupId
Подобный запрос обычно более эффективен чем фильтрующий после соединения таблиц.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.05.2009, 19:02
    #35983962
agakhov
agakhov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
Спасибо Bard, White Owl

смысл комадны ANALYZE,  я не осознал. Возможно её нужно запускать из командной строки. По крайней мере все GUI инструменты по работе с sqlite, молча её выполнили, оставив меня в неведении о происходящем.


Данный запрос у меня работает 1656 ms, что гораздо лучше чем OUTER JOIN и сравнимо с подзапросом(953ms)
Код: plaintext
1.
2.
3.
4.
SELECT *
FROM DeviceDetails as a
   INNER JOIN Devices as d ON a.deviceId = d.id 
WHERE a.groupId = :groupId

Вариант OUTER JOIN и без Where отрабатывает быстрее(653ms) чем конструкция с подзапросом(953ms), за что отдельная благодарность White Owl
Код: plaintext
1.
2.
3.
SELECT *
FROM Devices as d
   INNER JOIN DeviceDetails as a ON a.deviceId = d.id and a.groupId = :groupId

p/s
Вернулась вера в документацию, буду изучать матчасть по работе с конструкцией JOIN.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2009, 13:18
    #35985363
Bard
Bard
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
agakhovсмысл комадны ANALYZE,  я не осознал. Возможно её нужно запускать из командной строки. По крайней мере все GUI инструменты по работе с sqlite, молча её выполнили, оставив меня в неведении о происходящем.sb-news.netКоманда ANALYZE собирает статистику о индексах и их хранении в специальных таблицах базы данных, когда оптимизатор запросов может использовать их для выбора лучших индексов.На больших объемах при использовании индексов весьма полезная команда. Обычно выполняется после вставки/изменении большого объёма данных. См. ANALYZE

White OwlПодобный запрос обычно более эффективен чем фильтрующий после соединения таблиц.Век живи...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.09.2009, 17:18
    #36213206
Oleg Zhovtanyuk
Oleg Zhovtanyuk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
White Owl
Код: plaintext
1.
2.
SELECT *
FROM Devices as d
   INNER JOIN DeviceDetails as a ON a.deviceId = d.id and a.groupId = :groupId
Подобный запрос обычно более эффективен чем фильтрующий после соединения таблиц.

Спасибо!
Действительно, джойны без where быстрее...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.09.2009, 23:52
    #36213812
MBG
MBG
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
Смотрите

Код: plaintext
explain query plan _запрос_

Эта команда покажет, какие таблицы и индексы используются. Еще была в сети презенташка от DRH, кажется, 2004-го года, где было подробно расписано, как работает планировщик в эскулайт - он полностью детерминирован, так что вы можете заранее оценить план выполнения запроса. Также бывает полезно при выборках пользоваться конструкцией indexed by, чтобы планировщик использовал именно указанный вами индекс (если это невозможно, будет сгенерирована ошибка).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
15.11.2013, 08:48
    #38465950
vik.kz
vik.kz
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
White Owl,

объясните, что означает a.groupId = :groupId в вашем примере?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2013, 17:04
    #38466736
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQLite: Как правильно готовить LEFT OUTER JOIN ?
vik.kz,

сравнение поля таблицы (a.groupId) с переменной подстановки (:groupId). Значение переменной передается в запрос из внешней (вызываемой) среды.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / SQLite [игнор отключен] [закрыт для гостей] / SQLite: Как правильно готовить LEFT OUTER JOIN ? / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 411ms
Закрыть
start [/forum/topic.php?fid=54&mobile=1&tid=2008854]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 42ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 268ms
total:  411ms
x
x
Закрыть


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