Delphi - TSQLQuery оставляет процесс на MySQL даже после освобождения

Question

Я использую DBExpress в Delphi 2007 для подключения к серверу базы данных MySQL5 в сети.

Все работает нормально, пока я не попытаюсь загрузить большое количество данных. Я пытаюсь вставить 8000+ записей в базу данных, по одной в цикле, в цикле я передаю объект TSQLConection в функцию вместе с данными для вставки.

Функция создает объект TSQLQuery и запускает запрос на вставку до освобождения TSQLQuery. когда я запускаю его на больших наборах данных, я получаю сообщения о том, что сервер MySQL имеет множество соединений. Глядя в список процессов для сервера MySQL, я вижу это.

+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+
| Id      | User | Host                          | db     | Command | Time | State  | Info                  |
+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+
| 2962500 | name | myispdomain.co.uk:27812       | data   | Sleep   |    3 |        | [NULL]                |
+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+

Существует одна запись для каждого объекта TSQLQuery, который я создал, и если я пошагово выполняю код, то при запуске ExecSQL() я вижу новую. Я вызываю FreeAndNil в TSQLQuery и пытался вызвать Close перед его освобождением.

Мои настройки подключения MySQL следующие

   ConnectionName := 'MySQLConnection';
   DriverName     := 'MySQL';
   GetDriverFunc  := 'getSQLDriverMYSQL';
   KeepConnection := TRUE;
   LibraryName    := 'dbxmys30.dll';
   LoadParamsOnConnect := False ;
   LoginPrompt := FALSE;
   Name := 'mySQLConnection';
   VendorLib := 'LIBMYSQL.DLL';
   TableScope := [tsTable,tsView];

    Params.Add('DriverName=MySQL');
    Params.Add('HostName=www.sample.com');
    Params.Add('Database=data'); 
    Params.Add('User_Name=myuser'); 
    Params.Add('Password=mypassword'); 
    Params.Add('BlobSize=-1');
    Params.Add('ErrorResourceFile=');
    Params.Add('LocaleCode=0000');
    Params.Add('Compressed=False');
    Params.Add('Encrypted=True');

Если я установил для KeepConnection значение False, проблема исчезнет, ​​но время для выполнения запросов возрастет.

Есть ли способ обойти это?

Answer 1

Не создавайте заново ваше соединение, ни ваш запрос. Используйте параметры для запроса; откройте соединение один раз, заполните параметры запроса, выполните его, закройте запрос (но не соединение), снова заполните параметры запроса и выполните его снова.

Примерно так (с использованием Advantage Database Server, но концепция та же):

// Both Create() calls should be followed by try..finally to ensure they're 
// cleaned up after. Omitted for brevity.
Conn := TAdsConnection.Create(nil);
// Configure connection parameters here
Conn.Open;

Qry := TAdsQuery.Create(nil);
Qry.AdsConnection := Conn;
Qry.SQL.Add('INSERT INTO SOMETABLE (COL1, COL2, COL3)');
Qry.SQL.Add('VALUES (:COL1, :COL2, :COL3)');
while not OtherTable.Eof do
begin
  Qry.ParamByName('COL1').AsInteger := OtherTable.FieldByName('COL1').AsInteger;
  Qry.ParamByName('COL2').AsString := OtherTable.FieldByName('COL2').AsString;
  Qry.ParamByName('COL3').AsDateTime := OtherTable.FieldByName('COL3').AsDateTime;
  Qry.ExecSQL;
  Qry.Close;
  OtherTable.Next;
end;
// Free query
Conn.Close;
// Free connection.

Answer 2

Очевидная вещь, которую нужно сделать, - это рефакторинг для использования одного TSQLQuery и передачи его. Если вы создадите это с параметрами, вы можете сделать все это гораздо более эффективным, чем сделать новый запрос для каждого элемента данных.

Answer 3

ConnectionName := 'MySQLConnection';
DriverName     := 'MySQL';
GetDriverFunc  := 'getSQLDriverMYSQL';
**KeepConnection := TRUE;**    //   <---------- this setting is persisting the database connection, change to false if you want to disconnect 
LibraryName    := 'dbxmys30.dll';
LoadParamsOnConnect := False ;
LoginPrompt := FALSE;
Name := 'mySQLConnection';
VendorLib := 'LIBMYSQL.DLL';
TableScope := [tsTable,tsView];

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

Команда ---> sleep указывает на то, что вы подключены к базе данных, но в данный момент не выполняется ни один запрос (TSQLQuery и т. Д.). Фактически, если вы не создаете операцию с высокой нагрузкой, скорее всего, вы никогда не увидите ничего, кроме " сна ".

Answer 4

Вы можете создать большую вставку, что-то вроде:

Qry := TAdsQuery.Create(nil);
Qry.AdsConnection := Conn;
String qry = "INSERT INTO SOMETABLE (COL1, COL2, COL3)";
String values = "";

while not OtherTable.Eof do
begin
  values += "('"  + OtherTable.FieldByName('COL1').AsString + 
            "','" + OtherTable.FieldByName('COL1').AsString + 
            "','" + OtherTable.FieldByName('COL1').AsString + 
            "'),";
  OtherTable.Next;
end;

//to remove last ',' and add query terminator ";"
values = values.SubString(0, values.Length()-1) + ";"; 

qry = qry + values; //build the bulk insert

Qry.Add(qry);
Qry.ExecSQL;
//Qry.Close() no need of this, exec executes the command and leave, theres nothing to close
// Free query
Conn.Close;

оператор вставки должен выглядеть следующим образом: значения INSERT INTO (col1, col2, col3) ("val1", "val2", "val3"), ("val1", "val2", "val3"), ("val1",'val2','val3'), ..., ('val1','val2','val3');

При этом, как предполагает Кен, вы используете только одно соединение, но ваше приложение работает быстрее, поскольку вы вставляете больше данных в один запрос.

минусы: - данные должны быть безошибочными, так как определить, какой набор данных вызывает проблемы, сложнее, поскольку вы вставляете сотни наборов одновременно. -Компонент не работает нормально с большими запросами, поэтому лучше разбивать данные на куски и обрабатывать вставки, например, по 500 строк за раз.

В настоящее время я использую этот подход со вставками 1000 строк (с 4-8 столбцами данных) без проблем, я тестирую его с 5000 строками, но выбрасываю исключение "потерянное соединение во время запроса", поэтому я оставил его в 1000. Я думаю, что настройка На сервере вы можете увеличить скорость передачи данных, при условии, что компонент не является проблемой, в моем случае 1000 достаточно.

Наконец, я использую Builder C++, а не Delphi, поэтому код, который я публикую, может иметь ошибки

-Jose