Performance issue with SABulkcopy

[former_SQLA_member1694907]

I am using .Net C#. We need to copy large amount of data from SQL database to SQL Anywhere database. In theory, I can use SABulkcopy to insert large amount of data quickly, but I find the performance of SABulkcopy.writeToServer(IDataReader) is very slow: on a PC with Intel Core i7_2760QM CPU, 8GB ram, it took 15 minutes to copy 10000 records. Does anyone know how to improve the performance of SABulkcopy?

Thanks.

[jeff_albion]

Which version of SQL Anywhere / the ADO.NET provider are you using? Is it before or after CR #718761?

[former_SQLA_member1694907]

Hi Jeff:

The SQL Anywhere version is 12.0.1.3457, .Net we are using 3.5 for this project. My guess would be it includes the fix for CR#718761.

Thanks.

[Former Member]

CR 718761 is fixed in 12.0.1.3790 and later, so you would need to upgrade to take advantage of the changes.

[former_SQLA_member1694907]

I'd like to download a trial version of the fix before upgreade, does anyone know where to download the version 12.0.1.3790? I could see a version 16 trial version, but could not find 32 bit dll which is what I need.

Thanks.

[reimer_pods]

AFAIK there's no such thing as a trial version of an EBF. Assuming you're using Windows, the lates EBF 12.0.1.3967 contains 32 and 64 bit version. I downloaded the EBF from here:
http://origin2.sybase.com/swx/17193/SA12_Windows.1201_3942_EBF.exe

[former_SQLA_member1694907]

Thanks.

I finally installed version 12.0.1.39673. But different problem happened this time before I even get to what I'd like to do. I have the following error: System.NullReferenceException {"Object reference not set to an instance of an object."}

the call stack shows there is something wrong in iAnywhere.Data.SQLAnywhere.SADataAdapter.FillSchema(DataTable dataTable, SchemaType schemaType, SADataReader dataReader, DataTableMapping tm)

Does anyone know what is missing? Does the mapping of the columns become compulsory? i.e. with older version, I do not have to specify column mappings because the columns of the insert to table matches exactly of those of the from IDataReader.

Many Thanks.

[jeff_albion]

Can you show us the code you're using in order for us to reproduce the issue? We will likely have some recommendations for you to possibly work around the immediate issue, and hopefully make some suggestions about the performance issue also.

[former_SQLA_member1694907]

Hi Jeff:

The Code is as the following:

    using (SABulkCopy saBulkCopy = new SABulkCopy(SaConnection))
    {
        saBulkCopy.DestinationTableName = "Item";
        saBulkCopy.BulkCopyTimeout = 86400;
        saBulkCopy.BatchSize = 10000;
        saBulkCopy.NotifyAfter = 2500;
        saBulkCopy.SARowsCopied += BulkCopy_SARowsCopied;
    saBulkCopy.WriteToServer(aDataReader);
}

Note:aDataReader is a passed in parameter, which is reading a MS SQL database, the SaConnection is definitely established. This block of code worked with the older version, but very slow; whereas with version 12.0.1.39673, it produces the null reference exception.

Thanks.

[jeff_albion]

This issue can't be strictly related to the upgrade - some other condition must be happening here. I can use the following successfully:

create table t1(
  c1 integer primary key default autoincrement,
  c2 integer,
);

insert into t1 (c2) select row_num from sa_rowgenerator( 1, 10000 );

create table t2(
  c1 integer primary key default autoincrement,
  c2 integer
);

And the code:

    static void Main(string[] args)
    {
        try
        {
            SAConnection conn = new SAConnection("uid=dba;pwd=sql;eng=bulkcopy");
            conn.Open();
            SACommand comm = new SACommand("select c1, c2 from t1", conn);
            SADataReader reader = comm.ExecuteReader();
            using (SABulkCopy saBulkCopy = new SABulkCopy(conn))
            {
                saBulkCopy.DestinationTableName = "t2";
                saBulkCopy.BulkCopyTimeout = 86400;
                saBulkCopy.BatchSize = 10000;
                saBulkCopy.NotifyAfter = 2500;
                saBulkCopy.WriteToServer(reader);
            }
            reader.Close();
            conn.Close();
        }
        catch (Exception ex)
        {
            Console.Out.WriteLine("Exception: " + ex.Message + "\\n\\nStack Trace:\\n\\n" + ex.StackTrace);
        }
    }

Can you change my code to reproduce your error?

Also, in terms of the performance issue, what happens if you get rid of all of the callbacks - don't do any notifications and remove the event handler?

[former_SQLA_member1694907]

Hi Jeff:

Thanks for your suggestion. I have tried your code, it does not crash; but within my project, it does crash with the error stack as per attached. It appears that the only difference is the DataReader is reading a MS SQL database rather than a SQL AnyWhere db. Also, in terms of the performance issue, with version, 12.0.1.34573, it does not make any difference after I remove the event notification, the more columns the copy table has, the slower, the table I am testing against has about 40 columns, with 10000 records, it takes around 30 mins, the table structure in your suggestion only takes few seconds. Anyhow, if the new version 12.0.1.39673 does not crash, i.e. when the reader is reading from the same SQL Anywhere db, the time it takes to do the bulkcopy does reduce significently. So the question is how can we get around this crash problem? i.e. we are copying from MS Sql db.

[former_SQLA_member1694907]

Hi Jeff:

I suspect that the stack error is not attached properly, so here is the message from the crash error:

at iAnywhere.Data.SQLAnywhere.SADataAdapter.FillSchema(DataTable dataTable, SchemaType schemaType, SADataReader dataReader, DataTableMapping tm) at iAnywhere.Data.SQLAnywhere.SABulkCopy.WriteToServer(IDataReader reader) at Converter.WinToMacDBManager.BulkCopy(IDataReader aDataReader, String aTargetTableName) in c:WinMacConverterConverterWinToMacWinToMacDBManager.cs:line 189 at Converter.WinToMacDBManager.BulkCopy(String aGetString, String aTargetTableName) in c:WinMacConverterConverterWinToMacWinToMacDBManager.cs:line 73 at Converter.WinToMacConverterController.CopyDirectTableRecordsToMac() in c:WinMacConverterConverterWinToMacWinToMacConverterController.cs:line 272 at Converter.WinToMacConverterController.Convert() in c:WinMacConverterConverterWinToMacWinToMacConverterController.cs:line 145 at Converter.ConverterController.StartConvert() in c:WinMacConverterConverterConverterController.cs:line 726 at Converter.ConverterController.Run(BackgroundWorker worker, DoWorkEventArgs e) in c:WinMacConverterConverterConverterController.cs:line 112

[jeff_albion]

Update 2013/11/12 - CR #750915 has now been resolved in builds 11.0.1.3070, 12.0.1.4016 and 16.0.0.1737.

---

For the code, where aDataReader is a Microsoft SQL Server SqlDataReader:

using (SABulkCopy saBulkCopy = new SABulkCopy(SaConnection))
{
    saBulkCopy.DestinationTableName = "Item";
    saBulkCopy.BulkCopyTimeout = 86400;
    saBulkCopy.BatchSize = 10000;
    saBulkCopy.NotifyAfter = 2500;
    saBulkCopy.SARowsCopied += BulkCopy_SARowsCopied;
    saBulkCopy.WriteToServer(aDataReader);
}

Because of the ".BatchSize" setting, the ADO.NET provider is internally trying to populate a DataTable (which can be seen in the error stack trace) to push rows in batches to Microsoft SQL Server. This is causing two issues:

1. (Likely related to your performance problem) We will need to read some results from the SQLDataReader on Microsoft SQL Server to populate the DataTable in-memory on the client, to then send the results back over to SQL Anywhere. This extra step of populating the object in-memory may be causing some of the performance slow-down you're seeing.

2. The "Object not set to an instance of an object" is an internal error with the the ADO.NET provider code when reading from a data provider that is not SQL Anywhere. This will only happen in the SABulkCopy operation when the BatchSize is non-zero.

---

I have reported the null object exception issue as CR #750915. For now, can you remove the .BatchSize parameter (set it back to '0', which is the default) and test the performance again? You may even find that by dropping the .BatchSize parameter, the overall data operation is actually faster.

[former_SQLA_member1694907]

Hi Jeff:

Thank you so much.

I am able to run without crash after I got rid of setting of the batchSize. The time that is required to copy 10000 record for a table with 40 columns(there are some nvarchar(255)) is around 6 minutes. Even though, this performance is still no comparison with that of SqlBulkcopy, considering what it was from the older version we are using(which took around 30 - 40 mins), it is a significant improvement.

Thanks again for helping.