база ODBC содержит 37миллионов строк из ODBC мне нужно выгрузить эти данные в MySQL, делаю таким способом. Всё работает как надо. Но очень медленно!!! я понимаю что у меня комп не супер, но всётаки.. прикинул при такой скорости как идёт выгрузка.. 100тысяч строк в час .....моя быза будет перекачиваться просто нереально долго? как оптимизировать? что я не правильно написал в коде или так и должно быть? Код (Text): set_time_limit(0); $host = "localhost"; $user = "root"; $password = ""; // Производим попытку подключения к серверу MySQL: if (!mysql_connect($host, $user, $password)){ echo "<h2>MySQL Error!</h2>"; exit; } $db = 'forever'; // Выбираем базу данных: mysql_select_db($db); $dsntd = "DSN=TECDOC2013;Database=TECDOC_CD_1_2013;Server=localhost;Port=;UID=tecdoc;PWD=tcd_error_0"; $id = odbc_connect($dsntd, "", ""); echo $id; echo odbc_tables($id); $res = odbc_exec($id, "select * FROM TOF_ART_LOOKUP"); $r = odbc_fetch_array($res); //Создаём таблицу с полями, если не существует mysql_query("CREATE TABLE IF NOT EXISTS `TOF_ART_LOOKUP`( `ARL_ART_ID` integer(11) NOT NULL, `ARL_SEARCH_NUMBER` varchar(25) NOT NULL, `ARL_BRA_ID` smallint(6) NOT NULL, `ARL_DISPLAY_NR` varchar(30) NOT NULL ) ENGINE=InnoDB"); do { $query = mysql_query("INSERT INTO `tof_art_lookup` (`ARL_ART_ID`, `ARL_SEARCH_NUMBER`, `ARL_BRA_ID`, `ARL_DISPLAY_NR`) VALUES ('$r[ARL_ART_ID]', '$r[ARL_SEARCH_NUMBER]', '$r[ARL_BRA_ID]', '$r[ARL_DISPLAY_NR]')"); } while ($r = odbc_fetch_array($res));
Попробуй так, должно быстрее пойти по идее: Код (PHP): <?php set_time_limit(0); $host = "localhost"; $user = "root"; $password = ""; // Производим попытку подключения к серверу MySQL: if(!mysql_connect($host, $user, $password)) { echo "<h2>MySQL Error!</h2>"; exit; } $db = 'forever'; // Выбираем базу данных: mysql_select_db($db); $dsntd = "DSN=TECDOC2013;Database=TECDOC_CD_1_2013;Server=localhost;Port=;UID=tecdoc;PWD=tcd_error_0"; $id = odbc_connect($dsntd, "", ""); echo $id; echo odbc_tables($id); $res = odbc_exec($id, "SELECT * FROM TOF_ART_LOOKUP"); //Создаём таблицу с полями, если не существует mysql_query("CREATE TABLE IF NOT EXISTS `TOF_ART_LOOKUP`( `ARL_ART_ID` integer(11) NOT NULL, `ARL_SEARCH_NUMBER` varchar(25) NOT NULL, `ARL_BRA_ID` smallint(6) NOT NULL, `ARL_DISPLAY_NR` varchar(30) NOT NULL ) ENGINE=InnoDB"); $data = array(); $i = 0; while($r = odbc_fetch_array($res)) { $data[] = "('{$r['ARL_ART_ID']}', '{$r['ARL_SEARCH_NUMBER']}', '{$r['ARL_BRA_ID']}', '{$r['ARL_DISPLAY_NR']}')"; ++$i; if($i > 3000) { mysql_query('INSERT INTO `tof_art_lookup` (`ARL_ART_ID`, `ARL_SEARCH_NUMBER`, `ARL_BRA_ID`, `ARL_DISPLAY_NR`) VALUES ' . implode(', ', $data)); $i = 0; $data = array(); } } if(count($data)) { mysql_query('INSERT INTO `tof_art_lookup` (`ARL_ART_ID`, `ARL_SEARCH_NUMBER`, `ARL_BRA_ID`, `ARL_DISPLAY_NR`) VALUES ' . implode(', ', $data)); } ?>
абалдеть!!! скорость выше намного. попробовал за минуту выгрузило: ваш скрипт -- 30338 мой -- 1736 спасибище огромное за подсказку!!! завтра разберу код подробнее чтоб понять от чего так получается. И посчитаю за сколько выгрузится база при такой скорости....... посчитал .. вроде как за 20 часов справлюсь.. найти бы комп пошустрее......
graf_vorontsov, вы по чаще попова смотрите =))) Код (PHP): do {} while(); Вот на <матное слово> оно тут нужно? Код (PHP): <?php set_time_limit(0); $host='localhost'; $user='root'; $password=''; // Производим попытку подключения к серверу MySQL: $con=mysql_connect($host,$user,$password); if(!$con) { exit('<h2>MySQL Error '.mysql_error($con).'!</h2>'); } $db='forever'; // Выбираем базу данных: if(!mysql_select_db($db,$con)) { exit('<h2>MySQL Error '.mysql_error($con).'!</h2>'); } $dsntd='DSN=TECDOC2013;Database=TECDOC_CD_1_2013;Server=localhost;Port=;UID=tecdoc;PWD=tcd_error_0'; $id=odbc_connect($dsntd,'',''); echo $id; echo odbc_tables($id); $res=odbc_exec($id,"SELECT * FROM TOF_ART_LOOKUP"); //Создаём таблицу с полями, если не существует mysql_query("CREATE TABLE IF NOT EXISTS `TOF_ART_LOOKUP`( `ARL_ART_ID` integer(11) NOT NULL, `ARL_SEARCH_NUMBER` varchar(25) NOT NULL, `ARL_BRA_ID` smallint(6) NOT NULL, `ARL_DISPLAY_NR` varchar(30) NOT NULL ) ENGINE=InnoDB",$con); $data=array(); $i=0; while($r=odbc_fetch_array($res)) { $data[]='ARL_ART_ID='.$r['ARL_ART_ID'].',ARL_SEARCH_NUMBER='.$r['ARL_SEARCH_NUMBER'].',ARL_BRA_ID='.$r['ARL_BRA_ID'].',ARL_DISPLAY_NR='.$r['ARL_DISPLAY_NR']; ++$i; if(3000<$i) { mysql_query("INSERT INTO `tof_art_lookup` SET ".join(',',$data)); $i=0; $data=array(); } } if(sizeof($data)) { mysql_query("INSERT INTO `tof_art_lookup` SET ".join(',',$data)); } ?> Mysql больше не надо использовать... Используйте Mysqli.
Это то что радио изобрёл ? Если на хостинге установлен MySQL как я могу пользовать другую БД(Mysqli)? эти ж функции равнозначны как я понинимаю... а какие предпочтительней использавать и почему, можете рассказать...? sizeof == count join == implode
СУБД остается прежней - mySQL, меняется расширение. разрабы пыха улучшили механизмы работы библиотеки. отсюда название i - improved - усовершенствованный. переходить на mysqli это не просто полезный совет, это одно из основных действий. потому что в какой-то релиз php, устаревшая версия mysql не будет включена и все кто пользовался mysql_* функциями с горящими задницами дружно сядут переписывать под mysqli_ вместо того чтоб сейчас спокойно перетащить весь код. это не функции а библиотеки и они не равнозначны. подробно описано в документациях это алиасы для разрабов пришедших с других яп
спасибо Ganzal за подробное разъяснение. Добавлено спустя 44 минуты 16 секунд: не хочет так работать, не выгружает ничего
по мне так и не должно =) задумка использовать буффер правильная. а вот реализация какая-то странная. при такой байде каждую исходную строку мы конкатенируя запятой кладем в массив а потом элементы этого массива конкатенируя запятой отправляем как SET для INSERT'а. мне бы очень хотелось увидеть текст ошибки которым мускул отвечает на попытку выполнения такого запроса. по мне так вставку нужно переписать на VALUES ибо я сильно сомневаюсь что SET поддерживает множественный ввод.
51064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ARL_DISPLAY_NR=,ARL_ART_ID=2,ARL_SEARCH_NUMBER=DIESELLEICHTL10W40,ARL_BRA_ID=,AR' at line 1
ну как и заказывал, спасибо =) давайте немного перепишем. порядок полей, я так понимаю, согласно описанию таблицы Код (Text): `ARL_ART_ID` integer(11) NOT NULL, `ARL_SEARCH_NUMBER` varchar(25) NOT NULL, `ARL_BRA_ID` smallint(6) NOT NULL, `ARL_DISPLAY_NR` varchar(30) NOT NULL меняем первое - буфферные данные. $data[]=sprintf( "(%d, '%s', %d, '%s)', $r['ARL_ART_ID'], $r['ARL_SEARCH_NUMBER'], $r['ARL_BRA_ID'], $r['ARL_DISPLAY_NR'] ); то есть мы будем хранить готовую конструкцию для VALUES ()...()...() второе (дважды) - запросы. в них во-первых надо поменять имя таблицы - оно у вас же создается заглавным регистром во-вторых - для надежности указать порядок полей и в-третьих - собственно SET на VALUES mysql_query("INSERT INTO `TOF_ART_LOOKUP` (`ARL_ART_ID`,`ARL_SEARCH_NUMBER`,`ARL_BRA_ID`,`ARL_DISPLAY_NR`) VALUES ".join(', ',$data)); пока все
При хороших системных настройках компов, эти функции отрабатывают быстрее, почему-то, хотя там хоть и не большой отрыв, ну все равно, заметно станет подальше А сработает ли вообще запрос INSERT INTO SET у вас? Код (PHP): <?php set_time_limit(0); $host='localhost'; $user='root'; $password=''; $dsntd='DSN=TECDOC2013;Database=TECDOC_CD_1_2013;Server=localhost;Port=;UID=tecdoc;PWD=tcd_error_0'; if(!$mysql=mysql_connect($host,$user,$password)) { exit('<h2>MySQL Error '.mysql_error($mysql).'!</h2>'); } else if(!mysql_select_db($db,$mysql)) { exit('<h2>MySQL Error '.mysql_error($mysql).'!</h2>'); } else if(!$odbc=odbc_connect($dsntd,'','')) { exit('<h2>ODBC Error: '.odbc_error($odbc).'</h2>'); } else if($res=odbc_exec($odbc,"SELECT * FROM `TOF_ART_LOOKUP`")) { echo nl2br($odbc.' '.odbc_tables($odbc)); mysql_query("CREATE TABLE IF NOT EXISTS `TOF_ART_LOOKUP`( `ARL_ART_ID` integer(11) NOT NULL, `ARL_SEARCH_NUMBER` varchar(25) NOT NULL, `ARL_BRA_ID` smallint(6) NOT NULL, `ARL_DISPLAY_NR` varchar(30) NOT NULL ) ENGINE=InnoDB",$mysql); while($sql=odbc_fetch_array($res)) { mysql_query("INSERT INTO `tof_art_lookup` SET `ARL_ART_ID`='".$sql['ARL_ART_ID']."',`ARL_SEARCH_NUMBER`='".$sql['ARL_SEARCH_NUMBER']."',`ARL_BRA_ID`='".$sql['ARL_BRA_ID']."',`ARL_DISPLAY_NR`='".$sql['ARL_DISPLAY_NR']."'",$mysql); } } ?> odbc_ fetch_ row можно попробовать еще это для ускорения)
по скорости выгрузки это Код (PHP): while($r=odbc_fetch_array($res)) { $data[]=sprintf( "(%d, '%s', %d, '%s')", $r['ARL_ART_ID'], $r['ARL_SEARCH_NUMBER'], $r['ARL_BRA_ID'], $r['ARL_DISPLAY_NR'] ); ++$i; if(3000<$i) { mysql_query("INSERT INTO `TOF_ART_LOOKUP` VALUES ".join(',',$data)); $i=0; $data=array(); } } if(sizeof($data)) { mysql_query("INSERT INTO `TOF_ART_LOOKUP` VALUES ".join(',',$data)); } аналогично этому Код (PHP): while($r = odbc_fetch_array($res)) { $data[] = "('{$r['ARL_ART_ID']}', '{$r['ARL_SEARCH_NUMBER']}', '{$r['ARL_BRA_ID']}', '{$r['ARL_DISPLAY_NR']}')"; ++$i; if($i > 3000) { mysql_query('INSERT INTO `tof_art_lookup` (`ARL_ART_ID`, `ARL_SEARCH_NUMBER`, `ARL_BRA_ID`, `ARL_DISPLAY_NR`) VALUES ' . implode(', ', $data)); $i = 0; $data = array(); } } if(count($data)) { mysql_query('INSERT INTO `tof_art_lookup` (`ARL_ART_ID`, `ARL_SEARCH_NUMBER`, `ARL_BRA_ID`, `ARL_DISPLAY_NR`) VALUES ' . implode(', ', $data)); }
да. почему? Добавлено спустя 2 минуты 36 секунд: очень медленно выгружает, так как и мой первоначальный способ
никогда не сравнивал производительность форматированной строки против строки с подстановками. выиграет все равно конкатенация =) почему лично я бы не использовал подстановку: 0. в ней вы все свои переменные окружаете кавычками. мускул получит три байта '1' и ему нужно будет из этого сделать число длины подходящей под описание поля, хотя достаточно одного байта 1 чтоб он понял что это число. имхо, числа не надо окружать кавычками. плюс, 1.2 будет однозначно пониматься как дробное, вне зависимости от локализации. а "1.2" - как строка "один-точка-два", что только на части (впрочем, таких большинство) машин будет корректно транслироваться в число с дробной частью. 1. форматом я указываю какие данные ожидаются в переменных. 2. если переменная каким-то случайным образом будет не инициализована - мускул получит валидную строку например, для формата %d, %d, '%s', если мы вообще все переменные опустим - получим 0, 0, '' (ноль, ноль, пустая строка) в случае вложенных пременных - ,,'' - то есть на 100% инвалидный запрос. 3. о неверном кол-ве аргументов я узнаю из сообщения от пхп, а не от того что мускул подставит значения по умолчанию. 4. код удобнее читать. хотя бы потому что $r['some_string'] еще нужно real_escape'нуть, и в случае формата я просто обрамляю функцией соответствующий аргумент. в случае с подстановкой может получиться трудно читаемая каша.
судя по коду на каждую строку результата выполняется по одному запросу вставки. это медленно. предлагаемые виранты накапливают данные а потом делают пакетную вставку. и еще раз повторяю - не время вставки/обновления большого числа данных - выключайте индексы
да, я понял ещё с начала темы что пакетами вставлять данные намного быстрее про отключение индексов не пойму, что значит выключить их? в приведённом вами коде они же не участвуют в вставке... так?
Там, судя по коду из первого сообщения - никаких индексов и нету. Вообще никаких. Там же показан create table. На счёт генерации строки из переменных - самый быстрый способ - это заключить всё в двойные кавычки: Код (PHP): "string {$var}, {$arr['first']}, {$arr['second']}."; На втором месте будет конкатенация точкой Код (PHP): 'string ' . $var . ', ' . $arr['first'] . ', ' . $arr['second'] . '.'; Ну и на третьем месте будет использование функции sprintf(). Экспериментировал и на локалхосте и на Linux-сервере. Результат примерно одинаковый. Вот скрипт для тестирования: Код (PHP): <?php $data = array( 'first' => 'Первый элемент', 'second' => 'Второй элемент', 'third' => 'Третий элемент' ); $start = microtime(true); for($i = 0; $i < 10000000; ++$i) { $str = "Строка состоит из: {$data['first']}, {$data['second']}, {$data['third']}."; //$str = 'Строка состоит из: ' . $data['first'] . ', ' . $data['second'] . ', ' . $data['third'] . '.'; //$str = sprintf('Строка состоит из: $s, %s, %s.', $data['first'], $data['second'], $data['third']); } $execTime = microtime(true) - $start; header('Content-Type: text/plain; charset=utf-8'); echo round($execTime, 3) . ' sec'; ?> Вот полученные мною результаты: Код (Text): Localhost (Windows 7): 3.970 5.131 6.923 3.968 5.147 6.925 3.983 5.130 6.932 3.966 5.124 6.907 3.970 5.127 6.916 ===================== 3.971 5.132 6.921 Хостинг (Linux какой-то): 4.095 5.497 7.940 4.712 5.674 7.943 4.700 6.060 7.907 4.708 6.059 7.943 4.176 6.061 7.982 ===================== 4.478 5.870 7.943 Добавлено спустя 7 минут 4 секунды: Но вообще, способ конкатенации - это мелочи. Не стоит особо обращать на это внимания, поскольку любой из способов - это настолько мизерные затраты времени по сравнению с самой вставкой данных в б.д., что ими можно пренебречь. Можно в скрипте, использующем буферизацию (например в том, который я разместил тут в начале обсуждения) поэкспериментировать с количеством строк в "пакете". Я написал там 3000 (то есть вставлять по 3000 строк одним запросом). Можно попробовать поставить значение побольше, например 10000 - по идее процесс должен будет ещё ускориться. Правда такое завышение размеров пакета - может упереться в ограничение на объём выделяемой скрипту памяти. Кроме того, на сколько я помню, у MySQL есть ещё какие-то ограничения на размер SQL-запроса.
интересные результаты получаются, кстати. при включенном eA - все многократно укладываются в значения от 5.979 до 6.206. а вот при выключенном eA - sprintf начинает "радовать" - 10.493 - 10.839. интересно, каким же образом eA оптимизирует форматирование? в целом, на 10млн итераций получить разницу пусть даже и в 5 секунд - не самое узкое место. да, я только порядок полей там прочитал, дальше не вчитывался. действительно нет индексов.
а такой вопрос: Код (PHP): if($i > 3000) это вставляются данные пакетом по 3000, а если в конце у меня останется при последней вставке не 3000 строк а меньше.... пакет получается не вставится... но как я понимаю за это отвечает Код (PHP): if(count($data)) это подсчитывает остаток? так я понимаю
Это проверяет, есть ли остаток. Ну и далее по коду, если остаток есть - то он тоже вставляется в базу.