Вставка 90 строк.

[nosferatum]

Вставка 90 000 строк.

Всем доброго времени суток!

Есть файл 1.txt содержащий текст:
prog_id user_id date summ
3 1000001 2006-08-19 2.84
6 1000001 2006-08-04 8.64

В файле 90 000 строк нужно их занести в базу данных. Как делаю я:

$arrFileContent = file( "files/1.txt" );
get_query("TRUNCATE TABLE `test`;");
get_query("LOCK TABLES `test` WRITE;");
foreach( $arrFileContent as $nRowNum=>$sValue ) {
    $sValue = str_replace("\r","",$sValue);
    $sValue = str_replace("\n","",$sValue);
    $sValue = trim($sValue);
    if( $sValue!="" && $nRowNum>0 ) {
        list( $nServiceId, $nAbonentId, $sDate, $fAmount ) = explode("	",$sValue);
        $sQuery = "INSERT INTO `test` "
        . "(`service_id`,`abonent_id`, `date`, `amount`) "
        . "VALUES "
        . "('".intval($nServiceId)."','".intval($nAbonentId)."',"
        . "'".fnClearVar2($sDate)."','".floatval($fAmount)."');\n";
        get_query($sQuery);
    }
}
get_query("UNLOCK TABLES;");

Проблема в том, что запрос занимает слишком много времени. Как можно ускорить процедуру вставки?

Заранее спасибо за ответы!

 

[phprus]

nosferatum

В файле 90 000 строк

И ты файл такого размера целиком грузишь в память функцией file? Используй построчное чтение. Это будет быстрее.

$sValue = str_replace("\r","",$sValue);
$sValue = str_replace("\n","",$sValue);
$sValue = trim($sValue);

Что по твоему делает этот код? Тебе не кажется что как минимум второй str_replace бессмысленный, так как trim уберет единственно взмножный перевод строки. А первый str_replace будет бессмысленен если внутри строки нет символов возврата каретки.

P.S. А еще лучше попробовать воспользоваться возможностью MySQL LOAD DATA IN FILE.

 

[FractalizeR]

Re: Вставка 90 строк.

Автор оригинала: nosferatum
Всем доброго времени суток! Проблема в том, что запрос занимает слишком много времени. Как можно ускорить процедуру вставки?

Много - это сколько? Как часто требуется выполнять вставку данных из файла в базу?

P.S. Есть еще mysqlimport утилита.

 

[nosferatum]

Проблема не в том, насколько часто. Проблема в том, что на локальной двухядерной машине с 4 гб оперативы я могу это сделать, пускай даже это занимает около 40 секунд. А вот хостинг ругается! Больше 5000 строк вставляет уже через раз. Иногда выкидывает по истечению отпущенного лимита времени.

П.С. Про LOAD DATA INFILE тоже думал. Есть только одна загвоздка. Это тестовое задания для приёма на работу, точнее его небольшая часть. В условии написано что должно работать быстро. Про то каким образом заносить данные в базу не сказано. Поэтому решил от греха подальше не использовать LOAD DATA INFILE.

 

[Gas]

nosferatum
дурацкое тестовое задание

 

[nail]

вот и раздавай после такого тестовые задания

 

[nosferatum]

На самом деле нормально задание. После занесения данных в базу нужно потом вывод делать по группам и общую сумму выводить. До этого ещё нужно было сделать механизм авторизации и интерфейс. И ещё при нажатии на любую строку из списка менять в ней регистр всех первых букв в словах этой строки, т.е. большие на маленькие, маленькие на большие.

Всё что нужно есть - работа с сессиями/кукисами, работа со строками, работа с базой, работа с файлами и html. Разве что работы с GDlibrary нет и всякими вещами вроде архивации и ftp. Но это уже не тестовое задание.

 

[Mr_Max]

Больше 5000 строк вставляет уже через раз. Иногда выкидывает по истечению отпущенного лимита времени

5000 строк /30 сек = 166 инсертов в сек.
1 инсерт за 0,0060000000000000000000000000000024 сек.
тебе медленно?

-~{}~ 06.02.08 21:35:

Вряд-ли от LOAD DATA IN FILE ты много выиграешь.

-~{}~ 06.02.08 21:39:

2. Попробуй вставлять "порциями".
... table_name (field1,field2,field3) VALUES (1,2,3),(4,5,6)....
3. Наверное можно попробовать INSERT DELAYED.

Вряд-ли ты много выиграешь

.

 

[Gas]

LOAD DATA IN FILE

выиграет, 90K не 900K, но разница будет ощутимая.
с этой командой обычно проблема в другом - права на неё нужны.

0,0060000000000000000000000000000024 сек.

сразу и не заметил 6-ку, ну думаю скорость

 

[Mr_Max]

Gas
Я, наверное, чего-то не понимаю.
Думал, что ЛОК таблицы при многочисленных инсертах медленнее не не в 10 раз

-~{}~ 06.02.08 21:58:

Разве это справедливо и при локе таблицы?
When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.

 

[Gas]

Насчёт лока не совсем понял и наверное даже ответить не смогу
Что касается скорости - из личного опыта знаю что ощутимо быстрее по сравнению со множеством insert'ов (правда клиентом был не php), наверное сказывается оверхед при обмене данными, а там всё оптимизированно + если таблица пустая, не нужно самому делать enable|disable keys.

 

[Mols]

Если определяющим для этого задания фактором является скорость то тут даже думать нечего. Быстрее LOAD DATA INFILE сделать врядли удастся.
Если же LOAD DATA INFILE по каким-то причинам недоступна, то я бы подумал в сторону pcntl_fork. Но это костыли (причем можно и перестараться )))

 

[FractalizeR]

Автор оригинала: nosferatum
Проблема не в том, насколько часто. Проблема в том, что на локальной двухядерной машине с 4 гб оперативы я могу это сделать, пускай даже это занимает около 40 секунд. А вот хостинг ругается! Больше 5000 строк вставляет уже через раз. Иногда выкидывает по истечению отпущенного лимита времени.

Думаю, на хостинге set_time_limit не запрещен.

Кое-что на тему быстродействия вставки данных в таблицу есть тут: http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/

Думаю, выходом будет:

1. set_time_limit(0);
2. Как посоветовал Mr. Max вставлять данные как можно большими порциями (выигрыш будет ощутим, я думаю, за счет оптовой индексации)
3. Если данные вставляются в таблицу InnoDB (хотя, судя по LOCK TABLES у вас MyISAM), их нужно вставлять внутри одной транзакции

Еще момент. Если на хостинге доступен вызов system(), самым быстрым вариантом будет использование mysqlimport. Возможно, в этом и был подвох в тестовом задании? Время, затраченное на выполнение по system(), не включается в max_execution_time, так что даже set_time_limit не понадобится. mysqlimport использует LOAD DATA INFILE фактически.

 

[Mr_Max]

Gas
LOCK TABLES

быстрее по сравнению со множеством insert'ов

без блокировки таблиц - согласен.
С блокировкой индексы пишутся после всех инсертов.
И, я так думаю, что при блокировке таблицы enable|disable keys увеличит время.
К сожалению, протестировать нет сейчас времени.

 

[Gas]

Mols
человека на работу не возьмут - скажут что сильно умный
это ж тестовое задание - кому нужна скорость, мы просто увлеклись.

nosferatum
все варианты (а может ещё какая экзотика есть) тебе назвали, выбирай.

 

[Mr_Max]

Было-бы здОрово, посмотреть на результаты тестированния от ТС-а

 

[nosferatum]

Честно говоря, я рассматривал все эти варианты. Т.к. не было комментариев насчёт прав в системе, настроек PHP и базы MySQL, я решил остановиться именно на том варианте, который изначально здесь выложил. Только с учётом комментов убрал лишние str_replace и заменил разбиение файла в массив на построчное чтение. Я боялся что какой-нибудь экзотический вариант выпал из моего поля зрения.

Насчёт LOCK TABLES... В официальном хэлпе к MySQL написано что при локе индексы сбрасываются в хэш один раз и потом пишутся один раз после UNLOCK TABLES. Поэтому 100 инсертов в одном запросе и 100 запросов по одному инсерту с моей точки зрения не должны особо отличаться с скорости...

Всем спасибо за грамотные и своевременные ответы!