6
\$\begingroup\$

I have a database that I need to query over and over as fast as possible. My queries execute pretty quickly, but there seems to be some additional lag. I have a feeling that this lag is due to the fact that I am initiating and de-initiating a connection the connection each time. Is there a way to avoid this?

I am not using libmysql (at least, not directly). I am using the "mysql50" package in Lazarus/FreePascal (similar to Delphi), which in turn uses libmysql (I think).

The purpose of this library is to pass along a query sent from MQL4 (a propitiatory C-like language for the financial exchange market), and return a single row from my MySQL database (to which it connects through a pipe).

I would really appreciate if someone took a look at my code and pointed out (or maybe even fixed) some inefficiencies.

{$CALLING STDCALL}

library D1Query;

{$mode objfpc}{$H+}

uses
  cmem,
  Windows,
  SysUtils,
  profs_win32exceptiontrap,
  mysql50;

var

  sock: PMYSQL;
  qmysql: st_mysql;

type
  VArray = array[0..100] of Double;
  PArray = ^VArray;


  procedure InitSQL; stdcall;
  begin

    mysql_init(PMySQL(@qmysql));
    sock :=
      mysql_real_connect(PMysql(@qmysql), '.', 'root', 'password', 'data', 3306, 'mysql', CLIENT_MULTI_STATEMENTS);
    if sock = nil then
    begin
      OutputDebugString(PChar('  Couldn''t connect to MySQL.'));
      OutputDebugString(PChar(mysql_error(@qmysql)));
      halt(1);
    end;

  end;

  procedure DeInitSQL; stdcall;
  begin

    mysql_close(sock);
  end;

  function SQL_Query(QRY: PChar; output: PArray): integer; stdcall;
  var
    rowbuf: MYSQL_ROW;
    recbuf: PMYSQL_RES;
    i: integer;
    nfields: LongWord;


  begin
    InitSQL();

    if (mysql_query(sock, QRY) < 0) then
    begin
      OutputDebugString(PChar('  Query failed '));
      OutputDebugString(PChar('   ' + mysql_error(sock)));
    end;

    recbuf := mysql_store_result(sock);
    nfields :=  mysql_num_fields(recbuf);
    rowbuf := mysql_fetch_row(recbuf);


    if (rowbuf <> nil) then
    begin
      for i:=0 to nfields-1 do
          output^[i] := StrToFloatDef(rowbuf[i], -666);
    end;

    mysql_free_result(recbuf);
    DeInitSQL();
    Result := i;


  end;

exports
  SQL_Query,
  InitSQL,
  DeInitSQL;

begin
end.
\$\endgroup\$
  • 1
    \$\begingroup\$ I don't know Pascal but it's for sure the best to keep the connection open, especially if you have many queries to execute. It adds much overhead to one query. \$\endgroup\$ – Bobby May 27 '11 at 9:54
3
\$\begingroup\$

The most important thing here is not to create and destroy the connection as part of executing your query. Force the calling code to create the connection first (or create it yourself if its not done) but dont destroy the connection until the library is unloaded. That should improve speed.

\$\endgroup\$
1
\$\begingroup\$

Since you're calling InitSQL() and DeInitSQL() inside SQL_QUERY function, each time you call SQL_QUERY you actually create the connection and destroy it. This causes an I/O overhead in the database. Add another parameter to SQL_QUERY isLastStmt and send true with the last statement to destroy if isLAstStmt then DeInitSQL();

Also add if sock = nil then just after the line mysql_init(PMySQL(@qmysql)); in the InitSQL() procedure. This way if sock is nil the connection will be made. And considering the first step I mentioned about the destroy operation, the connection will be closed after execution of the last statement.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.