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.

asked 29 Oct '13, 21:17

VMeng's gravatar image

VMeng
1567714
accept rate: 0%

edited 13 Nov '13, 04:57

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659

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

(30 Oct '13, 10:26) Jeff Albion

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.

(30 Oct '13, 18:58) VMeng
Replies hidden
1

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

(30 Oct '13, 19:18) Tyson Lewis

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.

(31 Oct '13, 00:49) VMeng
Replies hidden

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

(31 Oct '13, 04:43) Reimer Pods

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.

(01 Nov '13, 01:28) VMeng
Replies hidden

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.

(01 Nov '13, 02:08) Jeff Albion

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.

(03 Nov '13, 17:41) VMeng
Replies hidden

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?

(05 Nov '13, 16:33) Jeff Albion

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.

(10 Nov '13, 17:23) VMeng

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

(10 Nov '13, 17:28) VMeng
More comments hidden
showing 5 of 11 show all flat view

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.

permanent link

answered 12 Nov '13, 12:37

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 19 Nov '13, 12:40

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.

(12 Nov '13, 18:07) VMeng
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×242
×35
×28

question asked: 29 Oct '13, 21:17

question was seen: 1,432 times

last updated: 19 Nov '13, 12:40