I have used ZeosLib components for MySQL queries for quite some time. I
thought I might give the dbx components a try in D2010, since they
support MySQL.
Today I did a speed comparison test between dbx and Zeos queries and
found the dbx was 2-4x slower!
Something is obviously wrong, so I ran the Wireshark packet sniffer to
see what the problem is.
It turns out that dbx queries log in, do their query and then
immediately log out again. In contrast, ZeosLib queries log in at the
start of the session, and stay logged in until you close the connection.
The extra dbx time was due to logging in and out for every query.
My question is:
How do I keep the dbx query and connection from logging out after each
query? Do I absolutely have to use parameters and play games with
preparing queries? This is stuff I don't have to do in ZeosLib.
Anyway, here is the benchmark code I used. Both benchmarks create
random names from a very large database of first and last names. I just
wanted a query that would take a long time. Can the dbx25Reads code be
optimized to make it perform more like Zeos?
FUNCTION TForm2.dbx25Reads: STRING;
VAR
StartTime: DWORD;
Q: TSQLQuery;
First, Middle, Last: STRING;
Sex, I, SearchGender: Integer;
BEGIN
StartTime := GetTickCount;
Q := TSQLQuery.Create(NIL);
Q.SQLConnection := SQLConnection1;
TRY
FOR I := 1 TO NUMLOOPS DO BEGIN
Q.SQL.Text := 'select lastname from names.lastnames order by
rand() limit 1';
Q.Open;
Last := VarDefault(Q.FieldValues['lastname'], 'NONAME');
// Q.Close;
Q.SQL.Clear;
Sex := RandLongRange(1, 5); // pick male, female or
transsexual
CASE Sex OF
1:
IF RandBoolBias(15) THEN // cruel parents
SearchGender := 3 // male ambiguous
ELSE
SearchGender := 1; // he-man male
2:
IF RandBoolBias(15) THEN // cruel parents
SearchGender := 3 // female ambiguous
ELSE
SearchGender := 2; // girly-girl female
3:
SearchGender := 3; // unknown
4:
SearchGender := 1; // transgender, f->m
5:
SearchGender := 2; // transgender, m->f
END;
Q.SQL.Clear;
Q.SQL.Add(Format('select firstname from names.firstnames
where gender=%d order by rand() limit 2', [SearchGender]));
Q.Open;
First := VarDefault(Q.FieldValues['firstname'], 'NONAME');
IF RandBoolBias(90) THEN BEGIN
Q.Next;
Middle := VarDefault(Q.FieldValues['firstname'],
'NONAME');
END
ELSE
Middle := '';
END;
Q.Close;
FINALLY
Q.Free;
END;
Result := IntToStr(GetTickCount - StartTime) + ' ms'
END;
FUNCTION TForm2.Zeos25Reads: STRING;
VAR
StartTime: DWORD;
Q: TZQuery;
First, Middle, Last: STRING;
Sex, I, SearchGender: Integer;
BEGIN
StartTime := GetTickCount;
Q := TZQuery.Create(NIL);
Q.connection := ZConnection1;
TRY
FOR I := 1 TO NUMLOOPS DO BEGIN
Q.SQL.Text := 'select lastname from names.lastnames order by
rand() limit 1';
Q.Open;
Last := VarDefault(Q.FieldValues['lastname'], 'NONAME');
// Q.Close;
Q.SQL.Clear;
Sex := RandLongRange(1, 5); // pick male, female or
transsexual
CASE Sex OF
1:
IF RandBoolBias(15) THEN // cruel parents
SearchGender := 3 // male ambiguous
ELSE
SearchGender := 1; // he-man male
2:
IF RandBoolBias(15) THEN // cruel parents
SearchGender := 3 // female ambiguous
ELSE
SearchGender := 2; // girly-girl female
3:
SearchGender := 3; // unknown
4:
SearchGender := 1; // transgender, f->m
5:
SearchGender := 2; // transgender, m->f
END;
Q.SQL.Add(Format('select firstname from names.firstnames
where gender=%d order by rand() limit 2', [SearchGender]));
Q.Open;
First := VarDefault(Q.FieldValues['firstname'], 'NONAME');
IF RandBoolBias(90) THEN BEGIN
Q.Next;
Middle := VarDefault(Q.FieldValues['firstname'],
'NONAME');
END
ELSE
Middle := '';
END;
Q.Close;
FINALLY
Q.Free;
END;
Result := IntToStr(GetTickCount - StartTime) + ' ms'
END;
[Non-text portions of this message have been removed]
