The utilisation of parameters writes in the standard output so the performances
are considerably descreased
-----------------------------------------------------------------------------------------------------------
Key: DNET-577
URL: http://tracker.firebirdsql.org/browse/DNET-577
Project: .NET Data provider
Issue Type: Bug
Components: ADO.NET Provider
Affects Versions: 4.5.2.0
Environment: Client application runs on a Windows 7 64 bits with
FirebirdSql.Data.FirebirdClient.dll 4.5.2.0 (also tested with 2.7.5.0 and
there's the same issue).
Firebird 2.1.4.18393 (Win32) on a Windows Server 2003 SP2 64 bits.
Reporter: Pascal
Assignee: Jiri Cincura
Priority: Blocker
To insert datas in a Firebird database, I use a FbCommand with parameters and
transactions to be as quick as possible.
But the FbCommand writes in the standard ouput all values of all parameters. So
when with anoter database the job lasts one hour, with Firebird it lasts ten
hours.
It's very important for me to get a fix and I can't go to production with this
low performances.
The code is made to work with SQL Server, Oracle, MySQL, PostgreSQL and
Firebird.
This is an example of code to reproduce:
public void InsererEnregistrement(string nomTable)
{
const IsolationLevel isolationLevel =
IsolationLevel.ReadUncommitted;
const int nbreLigneTotal = 1000;
var parametres = new List<string>();
for (int i = 1; i <= 50; i++)
parametres.Add("@p" + i);
var commande = String.Format("INSERT INTO {0} VALUES (" +
parametres.Aggregate((a, b) => a + ", " + b) + ")", nomTable);
using (var connexion = new FbConnection())
{
connexion.ConnectionString =
ConnectionStringBuilder.ConnectionString;
connexion.Open();
using (var command = new FbCommand())
{
command.Connection = connexion;
command.CommandText = commande;
DbTransaction transaction =
connexion.BeginTransaction(isolationLevel);
command.Transaction = transaction;
for (int i = 1; i <= 25; i++)
{
var parametre = new FbParameter();
parametre.ParameterName = "@p" + i;
parametre.DbType = DbType.Int32;
command.Parameters.Add(parametre);
}
for (int i = 26; i <= 50; i++)
{
var parametre = new FbParameter();
parametre.ParameterName = "@p" + i;
parametre.DbType = DbType.String;
command.Parameters.Add(parametre);
}
command.Prepare();
for (int i = 0; i <= nbreLigneTotal; i++)
{
command.Parameters[0].Value = (i + 1) * 100000;
command.Parameters[1].Value = i;
command.Parameters[2].Value = 0;
for (int j = 4; j <= 25; j++)
command.Parameters[j - 1].Value = 1234567890;
for (int j = 26; j <= 50; j++)
command.Parameters[j - 1].Value =
"01234657890123456789012345678901234567890123456789";
command.ExecuteNonQuery();
if ((i != 0) && ((i % 1000) == 0))
{
transaction.Commit();
if (i != nbreLigneTotal)
{
transaction =
connexion.BeginTransaction(isolationLevel);
command.Transaction = transaction;
}
}
}
}
}
}
And for each row inserted, I got this in the standard output :
FirebirdSql.Data.FirebirdClient Information: 0 : Command:
INSERT INTO SMR8_MODIF_C50T50L0100000 VALUES (@p1, @p2, @p3, @p4, @p5, @p6,
@p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19,
@p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32,
@p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45,
@p46, @p47, @p48, @p49, @p50)
Parameters:
Name:@p1 Type:Integer Used Value:100000
Name:@p2 Type:Integer Used Value:0
Name:@p3 Type:Integer Used Value:0
Name:@p4 Type:Integer Used Value:1234567890
Name:@p5 Type:Integer Used Value:1234567890
Name:@p6 Type:Integer Used Value:1234567890
Name:@p7 Type:Integer Used Value:1234567890
Name:@p8 Type:Integer Used Value:1234567890
Name:@p9 Type:Integer Used Value:1234567890
Name:@p10 Type:Integer Used Value:1234567890
Name:@p11 Type:Integer Used Value:1234567890
Name:@p12 Type:Integer Used Value:1234567890
Name:@p13 Type:Integer Used Value:1234567890
Name:@p14 Type:Integer Used Value:1234567890
Name:@p15 Type:Integer Used Value:1234567890
Name:@p16 Type:Integer Used Value:1234567890
Name:@p17 Type:Integer Used Value:1234567890
Name:@p18 Type:Integer Used Value:1234567890
Name:@p19 Type:Integer Used Value:1234567890
Name:@p20 Type:Integer Used Value:1234567890
Name:@p21 Type:Integer Used Value:1234567890
Name:@p22 Type:Integer Used Value:1234567890
Name:@p23 Type:Integer Used Value:1234567890
Name:@p24 Type:Integer Used Value:1234567890
Name:@p25 Type:Integer Used Value:1234567890
Name:@p26 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p27 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p28 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p29 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p30 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p31 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p32 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p33 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p34 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p35 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p36 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p37 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p38 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p39 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p40 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p41 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p42 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p43 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p44 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p45 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p46 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p47 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p48 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p49 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
Name:@p50 Type:VarChar Used
Value:01234657890123456789012345678901234567890123456789
If you think, it's important to continue to write parameters in the standard
output, you can put in the setup the bin/release dll without writing and the
bin/debug dll with writing.
So the developpers can use the release or the debug version depending of their
needs.
Regards,
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
