Quantcast

Slow query

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Loading...