Menu
▾
▴
[Firebird-net-provider] Slow query
|
From: Berko B. <vb...@ma...> - 2017-02-23 08:16:20
|
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div> <div>Hi,</div> <div>I have a problem with performance of firebird net provider.</div> <div>My query is slow.</div> <div>In my table there are ~ 5000 records.</div> <div>Simple query: select * from table</div> <div> </div> <div>In case of IBExpert or Delphi program, it takes ~ 80 ms</div> <div>In case of C# ~ 250 ms</div> <div>I fetch all records in IBExpert and Delphi too.</div> <div>I have tried on local database and on networking db. No Difference.</div> <div>My environment:</div> <div>- Windows 7 64.</div> <div>- Firebird Superserver 2.5.7</div> <div>- Net provider 5.7</div> <div> </div> <div>I feel, the number of records (or number of varchar columns) grows, so does the difference between C# and Delphi.</div> <div> </div> <div>My DB Script:</div> <div>------------------</div> <div> <div>SET SQL DIALECT 3;</div> <div>CREATE DATABASE '127.0.0.1:c:\test\test.fdb'<br/> USER 'user' PASSWORD 'password'<br/> PAGE_SIZE 8192;</div> <div><br/> CREATE TABLE TEST (<br/> I0 INTEGER NOT NULL,<br/> C1 CHAR(10),<br/> F1 VARCHAR(120),<br/> F2 VARCHAR(120),<br/> F3 VARCHAR(120),<br/> F4 VARCHAR(120)<br/> );<br/> COMMIT WORK;<br/> ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (I0);<br/> commit work;<br/> set term ^ ;<br/> execute block<br/> as<br/> declare variable i integer;<br/> declare variable par varchar(120);<br/> begin<br/> i=1;<br/> par='aaaaaaaaaaaaaaaaaaaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyHHHHHHHHHH';<br/> while (i<5000) do<br/> begin<br/> insert into test(i0,c1,f1,f2,f3,f4) values (:i,trim(cast(:i as varchar(5)))||'_ID',:par, :par,:par, :par);<br/> i=i+1;<br/> end</div> <div>end^<br/> set term ; ^</div> <div>commit work;</div> </div> <div> </div> <div>C# program:</div> <div>------------------</div> <div> <div> string cs =<br/> @"User = user; Password=password;Database=c:\test\test.fdb;DataSource=127.0.0.1"<br/> + ";Port=3050;Dialect=3;Role=;Connection lifetime = 15; "<br/> + "Pooling=false;Packet Size = 8192; ServerType=0;";</div> <div> var con = new FbConnection(cs);<br/> con.Open();<br/> var watch = System.Diagnostics.Stopwatch.StartNew();<br/> FbCommand com = new FbCommand("select i0,c1,f1,f2,f3,f4 from test", con);<br/> var dr = com.ExecuteReader();<br/> while (dr.Read()) ;<br/> watch.Stop();<br/> var elapsedMs = watch.ElapsedMilliseconds;<br/> Console.WriteLine(elapsedMs.ToString());<br/> com.Dispose();<br/> con.Dispose();</div> <div> </div> </div> </div></div></body></html> |
