#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    8
    Rep Power
    0

    Question Insert operation is taking more when comapred to MySQL and PostgreSQL


    Time estimates after Insert operation

    1. Insert 1, 00,000 records

    Insert Record
    Insert Transaction Start Time: 1/12/2011 5:30:23 PM
    Insert Transaction End Time: 1/12/2011 5:33:47 PM
    Elapsed Time in Insert Operation: 00:07:58.5903766

    2. Insert 1, 50,000 records

    Insert Record
    Insert Transaction Start Time: 1/12/2011 5:52:36 PM
    Insert Transaction End Time: 1/12/2011 6:01:32 PM
    Elapsed Time in Insert Operation: 00:08:56.3574804

    3. Insert 2, 00, 000 records

    Throwing exception "Unable to allocate memory from OS"

    In MySQL or PostgreSQL, the same 1 or 2 Lakhs of records are inserted in seconds ~ 40 seconds.

    So please help me to improve the performance using C# .NET

    I will paste you the code used for Insert operation:

    FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
    cs.UserID = "SYSDBA";
    cs.Password = "masterkey";
    cs.DataSource = "localhost";
    cs.Database = "c:\\Shibu\\Parameters.fdb";
    cs.Port = 3050;
    cs.Dialect = 3;
    cs.Charset = "NONE";
    cs.Role = string.Empty;
    cs.ConnectionLifeTime = 15;
    cs.Pooling = false;
    cs.MinPoolSize = 0;
    cs.MaxPoolSize = 50;
    cs.PacketSize = 8192;
    cs.ServerType = 0;

    FbConnection myConnection = new FbConnection(cs.ToString());

    string insertString = "INSERT INTO DeviceInfo (id, name, type, ipaddress, currentDateTime, status) VALUES " +
    "(@id, @name, @type, @ipaddress, @currentDateTime, @status)";

    FbCommand insertCommand = null;
    FbTransaction myTransaction = myConnection.BeginTransaction();

    for (Int32 counter = 1; counter <= 100000; counter++)
    {
    insertCommand = new FbCommand(insertString, myConnection, myTransaction);

    insertCommand.Parameters.Add("@id", counter);
    insertCommand.Parameters.Add("@name", "s");
    insertCommand.Parameters.Add("@type", "M");
    insertCommand.Parameters.Add("@ipaddress", "S");
    insertCommand.Parameters.Add("@currentDateTime", "1999-01-08 04:05:06");
    insertCommand.Parameters.Add("@status", '1');
    insertCommand.Parameters.Add("@sv1", 1.5);
    insertCommand.Parameters.Add("@sv2", 1.5);
    insertCommand.Parameters.Add("@sv3", 1.5);
    insertCommand.Parameters.Add("@sv4", 1.5);

    // Execute Update.
    insertCommand.ExecuteNonQuery();
    }

    // Commit changes
    myTransaction.Commit();

    //Release all the resources.
    myTransaction.Dispose();

    So please help me.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I am not familiar with C# so I do not know whether this can be changed but
    you seem to be building the query for every iteration of the loop.
    I assume it would be possible to build and prepare the query once outside the loop.

    Is the code identical to the code you use against the other databases?
    Is the table definition, including all constraints, indexes and triggers identical to
    the tables in the other databases?

    Is this the real code?
    These parameters do not exist in your SQL
    insertCommand.Parameters.Add("@sv1", 1.5);
    insertCommand.Parameters.Add("@sv2", 1.5);
    insertCommand.Parameters.Add("@sv3", 1.5);
    insertCommand.Parameters.Add("@sv4", 1.5);

    I see no code to catch exceptions. Are you getting any?
    Are all the records inserted?

    What happens if you Commit (commitRetaining) every 10,000 inserts?

    How can you run this code more than once, the second time through the values of
    counter will cause a PK violation assuming ID is the PK?

    Not much help; but some suggestions.

    Clive

IMN logo majestic logo threadwatch logo seochat tools logo