Hi:

We need to develop an application to be distributed in a local
network for near 100 users. We had had some problems with Interbase
and/or Firebird related with performance, and I am trying to define
to use Firebird or to quit and try to go into another direction.
The application to be developed has a very intensive Database usage
due to each user insert some parameters (like a simulation) and the
application do a lot of calculation inside the database recording a
final report to be analysed by the user.
To a final decision about using or not Firebird I prepared a small
application with only one SQL doing a reading in a 140000+ records
table. The SQL is very simple:
'select * from palavra where original like '%a' and ordenada not
like 'a%' and original=ordenada'
palavra is the table and original/ordenada string fields.
The test application just ask for the database reading for
letters 'a' to 'z'. I am using only two units in the same application
expecting getting good results and extending the test to a multi-
layer environment (I think using COM+) later.
Running the application with only one user I got near 10 seconds to
read all the queries. Running with two users the processing time
expands to 20 seconds, with three users to 30 seconds and so on.
Considering that this is a test and that the current test could last
2 minutes I will need 200 minutes for the 100 users that will be not
acceptable.
Can someone help me on this, giving me some clues and advices? I am
including the two test units in this text so you can see what I am
doing (Rigth or wrong).

=================
unit uDM;
interface
uses
SysUtils, Classes, DBXpress, FMTBcd, DB, DBClient, Provider,
SqlExpr;
type
TDM = class(TDataModule)
SQLConnection1: TSQLConnection;
qryWord: TSQLQuery;
DataSetProvider1: TDataSetProvider;
cdsPalavra: TClientDataSet;
private
public
end;
var
DM: TDM;
implementation
{$R *.dfm}
end.
===================
unit uFrmCliente;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls,
Forms,
Dialogs, StdCtrls, ExtCtrls, DBCtrls, DB, Mask, DBXpress;
type
TFrmCliente = class(TForm)
Button1: TButton;
ListBox1: TListBox;
L1: TLabeledEdit;
L2: TLabeledEdit;
procedure Button1Click(Sender: TObject);
procedure Ativar_SQL(Letra: String);
procedure FormCreate(Sender: TObject);
private
Trans: TTransactionDesc;
public
{ Public declarations }
end;
var
FrmCliente: TFrmCliente;
implementation
uses uDM;
{$R *.dfm}
procedure TFrmCliente.Ativar_SQL(Letra: String);
var
SQL_Text: string;
label
a;
begin
a:
if not dm.SQLConnection1.InTransaction then
begin
dm.SQLConnection1.StartTransaction(Trans);
end
else
begin
ListBox1.Items.Append(Letra+': Conflito!!!');
goto a;
end;
DM.cdsPalavra.Active:=false;
SQL_Text:='select * from palavra where original like ' +
'''%'+Letra+''' and ordenada not like '''+Letra+'%'' and'+
' original=ordenada';
DM.cdsPalavra.CommandText:= SQL_Text;
DM.cdsPalavra.Active:=true;
dm.SQLConnection1.Commit(Trans);
end;
procedure TFrmCliente.Button1Click(Sender: TObject);
var
SQL_Aux: string;
i: integer;
inicio: Tdatetime;
fim: Tdatetime;
total: TdateTime;
begin
DM.SQLConnection1.Params.Values['User_Name'] := L1.Text;
DM.SQLConnection1.Params.Values['Password'] := L2.Text;
DM.SQLConnection1.Connected:= True;
for i:=97 to 122 do
begin
Trans.TransactionID := i;
inicio:=Now;
SQL_Aux:= FormatDateTime('d/m/yyyy, hh:mm:ss.zzz ', inicio);
Ativar_SQL(chr(i));
fim:=now;
inicio:=fim-inicio;
ListBox1.Items.Append(chr(i)+': '+SQL_Aux+' - '+
FormatDateTime('d/m/yyyy, hh:mm:ss.zzz ',
fim)+ ' '+
FormatDateTime('hh:mm:ss.zzz ', inicio));
total:=total+inicio;
ListBox1.ItemIndex:= ListBox1.Count-1;
Refresh;
end;
ListBox1.Items.Append(FormatDateTime('hh:mm:ss.zzz ', total));
end;
procedure TFrmCliente.FormCreate(Sender: TObject);
begin
Trans.TransactionID := 1;
Trans.IsolationLevel := xilREADCOMMITTED;
//Trans.IsolationLevel :=xilDIRTYREAD;
//Trans.IsolationLevel :=xilREPEATABLEREAD
end;
end.
=====================
The data base:

/* Table PALAVRA */
CREATE TABLE PALAVRA(
C_DIGO INTEGER NOT NULL,
ORIGINAL VARCHAR(50),
ORDENADA VARCHAR(50)
)

================
Some data:

C_DIGO ORIGINAL ORDENADA
1 interbase access
2 operations activating
3 guide adding
4 enterprise adding
5 way administration
6 scotts administration
7 valley advantages
...
...
...
140532 writes your
140533 property and
140534 xii and
140535 interbase youwanttoexit

===============
The database is resided in one Windows 2000 Pentin4 2.8 MH with a
7200 HD without any other usage. The clients are running in Windows
2000 Pentiun4 2.4 Mh with the same HD type. I am doing the database
connection with the IP number (example:
123.111.123.37:e:/simDB/word.fdb). I am using Firebird 1.5 (Firebird-
1.5.0.4290_win32.zip)

My apologies for the long text, but for sure I need some help.
If you need further information I can provide for sure.

Regards

Wagner Bazarin
Mac-Penn
Sao Paulo
Brazil