[Firebird-net-provider] Slow query

[Berko Bubu]

Hi,

I have a problem with performance of firebird net provider.

My query is slow.

In my table there are ~ 5000 records.

Simple query: select * from table

 

In case of IBExpert or Delphi program, it takes ~ 80 ms

In case  of C# ~ 250 ms

I fetch all records in IBExpert and Delphi too.

I have tried on local database and on networking db. No Difference.

My environment:

- Windows 7 64.

- Firebird Superserver  2.5.7

- Net provider 5.7

 

I feel, the number of records (or number of varchar columns) grows, so does the difference between C# and Delphi.

 

My DB Script:

------------------

SET SQL DIALECT 3;

CREATE DATABASE '127.0.0.1:c:\test\test.fdb'
USER 'user' PASSWORD 'password'
PAGE_SIZE 8192;

CREATE TABLE TEST (
    I0  INTEGER NOT NULL,
    C1  CHAR(10),
    F1  VARCHAR(120),
    F2  VARCHAR(120),
    F3  VARCHAR(120),
    F4  VARCHAR(120)
);
COMMIT WORK;
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (I0);
commit work;
set term ^ ;
execute block
as
declare variable i integer;
declare variable par varchar(120);
begin
 i=1;
 par='aaaaaaaaaaaaaaaaaaaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyHHHHHHHHHH';
 while (i<5000) do
   begin
      insert into test(i0,c1,f1,f2,f3,f4) values (:i,trim(cast(:i as varchar(5)))||'_ID',:par, :par,:par, :par);
     i=i+1;
   end

end^
set term ; ^

commit work;

 

C# program:

------------------

            string cs =
                @"User = user; Password=password;Database=c:\test\test.fdb;DataSource=127.0.0.1"
                   + ";Port=3050;Dialect=3;Role=;Connection lifetime = 15; "
                   + "Pooling=false;Packet Size = 8192; ServerType=0;";

            var con = new FbConnection(cs);
            con.Open();
            var watch = System.Diagnostics.Stopwatch.StartNew();
            FbCommand com = new FbCommand("select i0,c1,f1,f2,f3,f4 from test", con);
            var dr = com.ExecuteReader();
            while (dr.Read()) ;
            watch.Stop();
            var elapsedMs = watch.ElapsedMilliseconds;
            Console.WriteLine(elapsedMs.ToString());
            com.Dispose();
            con.Dispose();

 

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider