What is the most efficient way to copy UniDataSet to SQL Server?

Question

I have a U2/UniVerse database, and need to copy the data in one table into a SQL Server table. The table in question has approx 600,000 rows and just under 200 columns. I didn't create the table, and can't change it.

For other tables, I'm looping thru a UniDataSet one record at a time and adding it to a DataTable, and then using SqlBulkCopy to copy the records to the SQL Server. This works fine, but with the large table I seem to be running out of memory when creating the DataTable.

DataTable dt = new DataTable("myTempTable");
dt.Columns.Add("FirstColumn", typeof(string));
dt.Columns.Add("SecondColumn", typeof(string));
... //adding a bunch more columns here
dt.Columns.Add("LastColumn", typeof(string));

U2Connection con = GetU2Con();
UniSession us1 = con.UniSession;
UniSelectList s1 = us1.CreateUniSelectList(0);
UniFile f1 = us1.CreateUniFile("MyU2TableName")
s1.Select(f1);

UniDataSet uSet = f1.ReadRecords(s1.ReadListAsStringArray());

foreach (UniRecord uItem in uSet)
{
    List<String> record = new List<String>(uItem.Record.ToString().Split(new string[] { "þ" }, StringSplitOptions.None));

    DataRow row = dt.NewRow();

    row[0] = uItem.RecordID;
    row[1] = record[0];
    row[2] = record[1];
    ... //add the rest of the record
    row[50] = record[49]

    dt.Rows.Add(row);
}

con.Close();

So that copies the records from the UniDataSet into a DataTable. Then, I SqlBulkCopy the DataTable into a SQL table:

string SQLcon = GetSQLCon();

using (SqlBulkCopy sbc = new SqlBulkCopy(SQLcon))
{
    sbc.DestinationTableName = "dbo.MySQLTableName";
    sbc.BulkCopyTimeout = 0;
    sbc.BatchSize = 1000; //I've tried anywhere from 50 to 50000

    try
    {
        sbc.WriteToServer(dt);
    }
    catch
    {
        Console.WriteLine(ex.Message);
    }
}

This works just fine for my U2 tables that have 50,000 or so rows, but it basically crashes the debugger (VS Express 2012) when the table has 500,000 rows. The PC I'm doing this on is Windows 7 x64 with 4GB ram. The VS process looks like it uses up to 3.5GB RAM before it crashes.

I'm hoping there's a way to write the UniDataSet right to SQL using SqlBulkCopy, but I'm not too familiar with the U2 .Net toolkit.

The problem I face is the UniDataSet records are multivalue, and I need to pick them apart before I can write them to SQL.

Thanks!

score 1 · Accepted Answer · answered Jun 12 '15 at 19:38

1

DataTable it gets too much bigger in memory, before inserting to Database. Why don't you split the bulk insert operation? For example read the first 50.000 results and the insert to Sql server database, clear the DataTable Memory and start again with the next 50.000 rows.

if (dt.Rows.Count > 50000)
{
    //do SqlbulkCopy
    dt.Rows.Clear();
}

answered Jun 12 '15 at 19:38

Marc Cals

2,963
4
30
48

I'm trying something similar now, where I'm deleting half of the records from the UniFile, running the rest of my code, and then doing it again with the other half. Thank you for your suggestion .. I'm going to try it that way too. – Jeff Brady Jun 12 '15 at 19:55
I accepted this since it was the basis for my solution (and the only answer I got .. :). I moved my `SqlBulkCopy` inside the `ForEach` loop, and prefaced it with `if (dt.Rows.Count == 300000 || uSet.AfterLast == true)`. This fires off the SqlBulkCopy twice, one about halfway thru the `UniDataSet` and again at the end. Thanks! – Jeff Brady Jun 15 '15 at 13:25

score 1 · Answer 2 · answered Jun 18 '15 at 04:51

1

In U2 Toolkit for .NET v2.1.0 , we have implemented Native Access. Now you can create DataSet/DataTable from UniData/UniVerse File directly. You can specify WHERE and SORT Clause too. You will see performance improvement as it will not make too much Server Trip to get IDs. For example, if you have 1000 record IDs, it will make 1000 times Server Trip. Whereas if you use Native Access, it will make one Server Trip.

Please download U2 Toolkit for .NET v2.2.0 Hot Fix 1 and try the following code. For more information , please contact u2askus@rocketsoftware.com.

            U2Connection con = GetU2Con();
            U2Command cmd = lConn.CreateCommand();
            cmd.CommandText = string.Format("Action=Select;File=MyU2TableName;Attributes=MyID,FirstColumn,SecondColumn,LastColumn;Where=MyID>0;Sort=MyID");
            U2DataAdapter da = new U2DataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];

answered Jun 18 '15 at 04:51

Rajan Kumar

718
1
4
9

Rajan, this is great! I will give it a try. How would I modify your Select statement to include all attributes in a given U2 table? – Jeff Brady Jun 18 '15 at 14:50
This is not recommended. But if your dictionary is clean, you can try the following syntax. cmd.CommandText = string.Format("Action=Select;File=MyU2TableName"); We are looking for ID in the dictionary other than "@ID". For example, ordinal/position 0 can have @ID and MYID. See more sample code here : C:\Program Files (x86)\Rocket Software\U2 Toolkit for .NET\U2 Database Provider\samples\C#\UniData\NativeAccess. Regards, Rajan – Rajan Kumar Jun 18 '15 at 15:53
I get the error "Object reference not set to an instance of an object" when it hits `da.Fill(ds)`. When looking at the sample code, I see the connection string uses `RpcServiceType=udcs` but my code uses uvcs. When using udcs, I cannot connect to the server. – Jeff Brady Jun 18 '15 at 16:10
Yes, “uvcs” is for UniVerse. Could you please do the following? 1. Contact Rocket U2 support and create an issue 2. Send Log File (u2askus@rocketsoftware.com) (Attn: Rajan Kumar) 3. Hoe to create Log file : https://github.com/RocketSoftware/multivalue-lab/tree/master/U2/Demos/U2-Toolkit/Log%20File – Rajan Kumar Jun 18 '15 at 16:27

What is the most efficient way to copy UniDataSet to SQL Server?

2 Answers2