# Querying MySQL from an external application

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.

• 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. – Bobby May 27 '11 at 9:54

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.