|
:: Главная :: Решения :: Статьи :: Сайт М. Дроздова :: Файловый архив :: Книга по VFP 9 :: Русский Help Online :: OFF-LINE Форум | |
|
Лисоводы всех стран, объединяйтесь !!! |
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Syberex Сообщений: 1432 Откуда: Кострома |
Цитата:А какие параметры лучше проверять?  Типа запомнить размер и дату в конце работы? Но для файл серверного приложения не пойдет ...  ------------------  |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Aleksey Tsingauz [MSFT] Автор |
Цитата: Как правило, стоит стремится уменьшить размер промежуточных результатов. Также немаловажно какая из двух таблиц будет ведущей во время JOIN. Как правило, JOIN выполняется быстрее если таблица, в которой остается меньше записей после Rushmore оптимизации, является ведущей. Следующий код это демонстрирует: CLEAR IF .T. CREATE TABLE testJoinPerf1(f1 I) INDEX ON f1 TAG f1 CREATE TABLE testJoinPerf2(f2 I) INDEX ON f2 TAG f2 FOR I=1 TO 10000 INSERT INTO testJoinPerf1 VALUES (I) NEXT FOR I=9901 TO 109900 INSERT INTO testJoinPerf2 VALUES (I) NEXT ENDIF FOR I=1 TO 10 nSeconds1=SECONDS() SELECT * FROM FORCE testJoinPerf1 JOIN testJoinPerf2 ON f1=f2 INTO CURSOR res nSeconds2=SECONDS() ?nSeconds2-nSeconds1,RECCOUNT("res") USE IN res nSeconds1=SECONDS() SELECT * FROM FORCE testJoinPerf2 JOIN testJoinPerf1 ON f1=f2 INTO CURSOR res nSeconds2=SECONDS() ?nSeconds2-nSeconds1,RECCOUNT("res") USE IN res ? NEXT Если есть коррелированный подзапрос, то может иметь смысл коррелировать его не прямо к исходной таблице а к результату подзапроса помещенного во FROM, который "соединит" исходную таблицу с другими таблицами, при условии, что этот подзапрос вернет меньше записей чем число записей в исходной таблице. Много разных факторов влияют на производительность, поэтому не стоит слепо следовать каким-то правилам. Надо попробовать и так и эдак и желательно делать это с данными, которые максимально близки к реальным (количество, разброс значений, и т.д. и т.п.). Поиграть индексами, какие-то создать, какие-то удалить или изменить выражение так, чтобы результат был тот же, но с индексным выражением оно не совпадало. Цитата: В VFP9 во FLUSH добавлен новый модификатор FORCE. Если он упомянут, то VFP вызывает дополнительную функцию, которая таки должна заставить операционку сбросить изменения на диск. Я бы порекомендовал выполнять FLUSH после того, как была произведена какая-то группа изменений. Особенно с таблицами открытыми эксклюзивно, для них VFP может достаточно долго держать изменения в своем кэше и не отдавать их операционке. Aleksey Tsingauz Visual FoxPro Dev Team |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
PaulWist Сообщений: 13589 |
Aleksey Tsingauz [MSFT]
Алексей спасибо за внимание к нашим дурацким вопросам. Igor Korolyov Цитата: Согласен, но одно непонятно почему не возникает ошибка при выдаче Flush, когда сетевой шнурок уже выдернут. Цитата: Ну это вообще не подлежит обсуждению, иначе кто нам будет платить за сопровождение, а так мы вроде уважаемые люди  ------------------ Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам. (В.Шекспир Гамлет) |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Syberex Сообщений: 1432 Откуда: Кострома |
2 Aleksey Tsingauz [MSFT]
Спасибо за пример, теперь понятно. Но в реальных условиях за таким отношением практически не уследить... У меня вот только что произошло еще кое-что связанное с JOIN (как мне кажется )
Всю ночь просидел... Вообщем вот: В модальной форме открывается вид (список деталей), в нем несколько JOIN-ов, вдруг он стал выдавать сообщение Цитата:Я начал проверять где были изменения, но вид пуст, GETFLDSTATE() возвращает .NULL. начал проверять код вида - все нормально, стал базу упаковывать и Validate-ить в результате всего Фокс9 завис с окном с сообщением: Цитата:там только кнопка ОК, жму и оно опять появляется ... короче "снять задачу" 
В хелпе только это: Цитата: Думал базе конец, но пронесло... Потом выяснеилось что для некоторых товаров, вид с деталями всетаки открывается! Оказалось, что не открывается когда поле id_detal (тип С(10)) для первого JOIN пусто... Пока удалил неправильные записи и сделал проверку, что бы не появлялись вновь, но ситуация непонятна ... 
Вод код вида (5-я буферизация) SELECT A.id, A.id_prod, A.id_detal, A.id_mtr, A.type_mtr, A.count, A.id_unit, A.id_form, A.id_size, A.id_color, nvl(D.code, space(20)) as code, nvl(D.naimen, space(100)) as naimen, nvl(M.naimen, space(100)) as material, nvl(U.naimen, space(20)) as unit, nvl(F.naimen, space(20)) as form, nvl(S.naimen, space(20)) as size, nvl(C.naimen, space(20)) as color FROM data1!prod_detal A LEFT JOIN data1!details D ON D.id = A.id_detal LEFT JOIN data1!materials M ON M.id = A.id_mtr LEFT JOIN data1!units U ON U.id = A.id_unit LEFT JOIN data1!mtr_forms F ON F.id = A.id_form LEFT JOIN data1!mtr_sizes S ON S.id = A.id_size LEFT JOIN data1!mtr_colors C ON C.id = A.id_color WHERE A.id = ?lc_id AND A.id_prod = ?lc_id_prod [i][small][color=Gray]Отредактировано (02.09.04 19:27) ------------------ |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
piva Сообщений: 18600 Откуда: Курган |
Хе ! А у меня еще со времен fpd(w) 2.6 тащился крыжик - включать ли FLUSH при записи, который ставился по умолчанию. Дальновидность однако
 , (только почему-то не всегда помогало, были редкие случаи, видимо тогда он был не до конца FLUSH )------------------ Часто бывает так, что есть над чем задуматься, а нечем. |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Igor Korolyov Сообщений: 34065 |
Спасибо, очень интересные эксперименты. Я бы ещё добавил, что если есть
возможность заменить коррелированный подзапрос на некоррелированный, то так наверное и стоит сделать - т.к. коррелированный будет выполняться столько раз, сколько записей будет в промежутьчной выборке (ну в той к которой он привязывается), а некоррелированный - всего 1 раз. Даже если считать, что коррелированный возвращает всего по 1 записи, а некорелированный - несколько тысяч записей (зато всего 1 раз)... Ну пример - это многострадальная задача поиска "последних данных из истории изменения цен". Вплоть до VFP9 (если коенчно не прибегать к неправильным и непредсказуемым запросам с неполным GROUP BY) производительность такого запроса можно было существенно повысить, разделив его на 2 - и избавившись от коррелированного подзапроса. Ну а в VFP9 можно в одном запросе всё сделать - поместив подзапрос во FROM (он будет некоррелированным). В общем оптимизация - это очень непросто Да, и ещё, раз уж речь идёт о производительности и IN, то особо надо отметить эту фразу из Release Notes: Цитата:Это, да и фраза про то что число элементов зависит от SYS(3055), как я понимаю - неявное подтверждение того, что IN в VFP9 работает наподобии набора OR - когда вычисление прекращается при достижении заведомо известного результата, но при том повышает "сложность" выражения. Теперь насчёт FLUSH FORCE - насколько надёжно будет работать это (точнее АПИ-функция FlushFileBuffers) применительно к сетевым файлам (доступ через сетевой редиректор) - гарантируется ли сброс данных на диск сервера, или нет? Зависит ли это от типа сети и сетевого клиента (NT-сеть, Novell, UNIX/Samba-сервер) С общими принципы работы файловых систем и редиректора ознакомился (Opportunistic Locks однако весьма непросты ), но вот уверенности в том как именно пройдёт цепочка сброса буферов в случае сетевого файла пока нету... P.S. Я тут заодно Q332023 почитал, впечатляет Т.е. сначала народ жалуется на то что дескать данные пропадают, вы им р-р-раз и один хотфикс, всё вроде пучком, но тут они начинают жаловаться что дескать "медленно всё стало работать" - и вы им оп-па другой хотфикс, по сути позволяющий отменить действие первого Вот и попробуй угоди им всем ------------------ WBR, Igor |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Igor Korolyov Сообщений: 34065 |
Э-э-э, даже не подумал как-то, что оно должно было давать ошибку когда-либо
Но возможно действительно стоит при неудаче выполнения FlushFileBuffers генерить ошибку... Да кстати, ты уверен, что данные к тому моменту как ты "выдернул шнурок" реально ещё не сбросились из кэша? Ибо в нормальных условиях кэш сбрасывается весьма и весьма быстро после операций записи... Надо будет поэкспериментировать на работе, в том числе посмотреть что за ошибка реально получается при FlushFileBuffers() с "выдернутым шнурком". ------------------ WBR, Igor |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Igor Korolyov Сообщений: 34065 |
Цитата:Как я понимаю фоксовый оптимизатор что-то пытается в этом плане сам сделать (т.е. НЕ ставь FORCE для соединений и всё ).Цитата:А надо было отдохнуть, и с утречка со свежими силами  Цитата:Типичная ситуация, когда ненароком ошибаешься в размерности поля в NVL() т.е. скажем когда там не null, то берётся реальное поле с размером C(10), а когда null - подставляется SPACE(20) - вот и получается эта ошибка. Обычно вылазит при перезапросах, со сменой параметра, или если изначально представление открывалось с опцией NODATA. Да, и ещё - я вообще против однобуквенных алиасов, тем более M Не хочешь длинных или хоть минимально смысловых - пользуй t1, t2, ... например. Цитата:Фокс сам всё проверит по SET TABLEVALIDATE TO 7 И даже в более ранних он может обнаружить "несоответствие" и известить об этом ошибкой - про обработку таких ошибок я и говорил. А самому проверять... IMHO не стоит оно того. ------------------ WBR, Igor |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
PaulWist Сообщений: 13589 |
Цитата: Почти. Создал таблицу с(254), добавил туда 5 млн записей - файл получился чуть больше 1Г поставил 5 буферизацию, затем replace all, tableupdate,End Trans, Flush, set step on и в этот момент дернул сеть, получил сообщение ОС FlushFileBuffers() не может завершить операцию, а у фокса не возникло ошибки (видимо, фокс не спросил как мол завершена операция), затем открыл табличку и увидел, что какая-то часть записей всё таки прилипла. Может быть отсюда и растут ноги , что не всегда данные сохраняются. ------------------ Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам. (В.Шекспир Гамлет) |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Syberex Сообщений: 1432 Откуда: Кострома |
2 Игорь
Спасибо, действительно там должно быть space(10) ! Теперь все открывается, а деталь просто пустая 2 Aleksey Tsingauz [MSFT] Не мешало бы в хелп по этой ошибке добавить пару строк про NVL(), ведь наверняка это основной момент, когда она возникает... ------------------ |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Aleksey Tsingauz [MSFT] Автор |
Здравствуйте, Игорь!
Цитата: Это просто определение результата. На самом деле, VFP выполняет коррелированный подзапрос один раз, но часто (в зависимости от того, что подзапрос возвращает и как сравнивается) это требует один подготовительный шаг. Цитата: На сложность выражения влияют не только логические операторы AND и OR, но и все арифметические операторы, вызовы функций, число переданных параметров, и т.д. и т.п. Раньше, максимальное число параметров для IN было 25 и вероятность превысить сложность выражения была минимальной. Сейчас, этого предела нет и если пытаться передавать сотни параметров, то вероятность превысить сложность выражения довольно высока. Цитата:Обратитесь к документации для FlushFileBuffers. Aleksey Tsingauz Visual FoxPro Dev Team |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Igor Korolyov Сообщений: 34065 |
Цитата: Не понял... Т.е. в запросе вида: SELECT ... FROM xxx ; WHERE Some IN ; (SELECT MAX(Another) FROM yyy ; WHERE yyy.ForLink = xxx.ForLink) Но тогда по какому плану? Сколько он записей будет просматривать в yyy? Я всегда думал что для таких запросов внутренний подзапрос выполняется как минимум столько раз, сколько записей попадает на вход из внешней части (в данном случае из таблицы xxx). И несмотря на "простоту" запроса (допустим он полностью оптимизирован - есть индекс по полю ForLink) получим некоторое замедление - тем больше чем больше записей было в xxx. Цитата:Дык именно оттуда и не понятно как проходит взаимодействие с сетевым редиректором ------------------ WBR, Igor |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Aleksey Tsingauz [MSFT] Автор |
Цитата: В данном конкретном случае подзапрос будет модифицирован так, чтобы подсчитать все искомые MAX(Another) за одно выполнение. Цитата: Логически это верно. Цитата: Конечно, не смотря на то, что модифицированный подзапрос выполняется один раз, чем больше разных MAX(Another) надо подсчитать, тем больше работы надо проделать и тем медленнее он выполняется. Но если взять следующий запрос, то подзапрос как таковой вообще выполнятся не будет. CREATE TABLE foo (f1 I, f2 I) CREATE TABLE bar (f3 I, f4 I) SELECT * from foo WHERE f1 IN (select f3 FROM bar WHERE f2=f4) INTO CURSOR res Запрос будет трансформирован в специального вида JOIN между двумя таблицами. Вся разница между этим запросом и SELECT * from foo WHERE f1 IN (select f3 FROM bar) INTO CURSOR res будет только в том, что в первом JOIN будет по двум условиям, а во втором по одному. Aleksey Tsingauz Visual FoxPro Dev Team |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Igor Korolyov Сообщений: 34065 |
Я понимаю, что целью оптимизатора и является по возможности преобразование
подзапроса в более простое и быстрое соединение. И я заметил существеннейший прирост скорости работы подобных запросов в VFP9 Но всё-же как объяснить достаточно разные результаты у по сути одинаковых запросов? SET COLLATE TO "MACHINE" SET TALK OFF CLEAR CREATE TABLE tmp (nNum I, dDate D, nCode I) FOR ln1 = 1 TO 1000 FOR ln2 = 1 TO 60 INSERT INTO tmp (nNum, dDate, nCode) VALUES ; (m.ln1, DATE() - m.ln2, IIF(m.ln2 > 30, m.ln2 - 30, m.ln2 + 30)) ENDFOR ENDFOR INDEX ON nNum TAG nNum INDEX ON dDate TAG dDate CLOSE TABLES ALL FOR ln1 = 1 TO 5 lnSec = SECONDS() SELECT * FROM tmp t1 ; WHERE t1.dDate IN ; (SELECT MAX(t2.dDate) ; FROM tmp t2 ; WHERE t2.nNum = t1.nNum) ; ORDER BY t1.nNum INTO CURSOR xxx ? "Syntax 1. Try # ", m.ln1, "time ", SECONDS() - m.lnSec, "records ", RECCOUNT("xxx") USE IN SELECT("xxx") ENDFOR CLOSE TABLES ALL FOR ln1 = 1 TO 5 lnSec = SECONDS() SELECT * FROM tmp t1 ; WHERE t1.dDate = ; (SELECT MAX(t2.dDate) ; FROM tmp t2 ; WHERE t2.nNum = t1.nNum) ; ORDER BY t1.nNum INTO CURSOR xxx ? "Syntax 2. Try # ", m.ln1, "time ", SECONDS() - m.lnSec, "records ", RECCOUNT("xxx") USE IN SELECT("xxx") ENDFOR CLOSE TABLES ALL FOR ln1 = 1 TO 5 lnSec = SECONDS() SELECT * FROM tmp t1 ; WHERE BINTOC(t1.nNum) + DTOS(t1.dDate) IN ; (SELECT BINTOC(t2.nNum) + DTOS(MAX(t2.dDate)) ; FROM tmp t2 GROUP BY t2.nNum) ; ORDER BY t1.nNum INTO CURSOR xxx ? "Syntax 3. Try # ", m.ln1, "time ", SECONDS() - m.lnSec, "records ", RECCOUNT("xxx") USE IN SELECT("xxx") ENDFOR CLOSE TABLES ALL FOR ln1 = 1 TO 5 lnSec = SECONDS() SELECT * FROM tmp t1, ; (SELECT tmp.nNum, MAX(tmp.dDate) AS dDateMax FROM tmp GROUP BY tmp.nNum) t2 ; WHERE t1.nNum = t2.nNum AND t1.dDate = t2.dDateMax ORDER BY t1.nNum INTO CURSOR xxx ? "Syntax 4. Try # ", m.ln1, "time ", SECONDS() - m.lnSec, "records ", RECCOUNT("xxx") USE IN SELECT("xxx") ENDFOR ------------------ WBR, Igor |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
PaulWist Сообщений: 13589 |
Игорь, замечательный пример.
Знаешь, я поставил твой 4-ый цикл на место первого и скорость выполнения увеличилась % ещё на 50, далее, что удивительно при проходе от 1-ого до 5-ого Select-a время выполнения увеличивается (казалось бы должно быть наоборот). ------------------ Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам. (В.Шекспир Гамлет) |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Igor Korolyov Сообщений: 34065 |
Я тоже их менял местами (да и просто отключал некоторые и перезапускал
фокс), главное что пропорция в целом сохраняется... Цитата:Хм. Не заметил... У меня разбежка вся в пределах статистической погрешности (т.е. пляшут цифири около среднего, но без явной "тенденций"). Правда я ставил 10-20 выполнений в цикл (это уж потом снизил до 5, после того как на 8-ке этот запрос стал исполняться просто безобразно долго), ну ещё с SYS(1104) баловался и с индексами (в смысле проверял и без индексов и с ними). Вот до тестирования на сетевом диске руки не дошли ------------------ WBR, Igor |
||
| Re: SQL вопросы по оптимизации | |||
|---|---|---|---|
|
Aleksey Tsingauz [MSFT] Автор |
Цитата: После удаления ORDER BY из всех запросов и увеличения внешнего цикла для INSERT до 10000, я получил следующие результаты: VFP 09.00.0000.1720 (Public Beta) Syntax 1. Try # 1 time 3.563 records 10000 Syntax 1. Try # 2 time 3.516 records 10000 Syntax 1. Try # 3 time 3.547 records 10000 Syntax 1. Try # 4 time 3.500 records 10000 Syntax 1. Try # 5 time 3.516 records 10000 AVG= 3.5284 Syntax 2. Try # 1 time 2.688 records 10000 Syntax 2. Try # 2 time 2.641 records 10000 Syntax 2. Try # 3 time 2.656 records 10000 Syntax 2. Try # 4 time 2.609 records 10000 Syntax 2. Try # 5 time 2.625 records 10000 AVG= 2.6438 Syntax 3. Try # 1 time 2.297 records 10000 Syntax 3. Try # 2 time 2.250 records 10000 Syntax 3. Try # 3 time 2.344 records 10000 Syntax 3. Try # 4 time 2.235 records 10000 Syntax 3. Try # 5 time 2.250 records 10000 AVG= 2.2752 Syntax 4. Try # 1 time 2.281 records 10000 Syntax 4. Try # 2 time 2.234 records 10000 Syntax 4. Try # 3 time 2.234 records 10000 Syntax 4. Try # 4 time 2.250 records 10000 Syntax 4. Try # 5 time 2.235 records 10000 AVG= 2.2468 VFP 09.00.0000.2110 Syntax 1. Try # 1 time 2.281 records 10000 Syntax 1. Try # 2 time 2.234 records 10000 Syntax 1. Try # 3 time 2.219 records 10000 Syntax 1. Try # 4 time 2.219 records 10000 Syntax 1. Try # 5 time 2.235 records 10000 AVG= 2.2376 Syntax 2. Try # 1 time 2.860 records 10000 Syntax 2. Try # 2 time 2.813 records 10000 Syntax 2. Try # 3 time 2.812 records 10000 Syntax 2. Try # 4 time 2.812 records 10000 Syntax 2. Try # 5 time 2.828 records 10000 AVG= 2.8250 Syntax 3. Try # 1 time 2.265 records 10000 Syntax 3. Try # 2 time 2.235 records 10000 Syntax 3. Try # 3 time 2.235 records 10000 Syntax 3. Try # 4 time 2.219 records 10000 Syntax 3. Try # 5 time 2.219 records 10000 AVG= 2.2346 Syntax 4. Try # 1 time 2.125 records 10000 Syntax 4. Try # 2 time 2.063 records 10000 Syntax 4. Try # 3 time 2.109 records 10000 Syntax 4. Try # 4 time 2.062 records 10000 Syntax 4. Try # 5 time 2.093 records 10000 AVG= 2.0904 Разное время выполнения закономерно, все эти запросы выполняются по разным планам. Однако абсолютная разница для результата в 10000 записей кажется несущественной. Aleksey Tsingauz Visual FoxPro Dev Team |
||
| © 2000-2021 Fox Club |