hi, all,

We are doing a SA11's performance test against mssql 2000/2005.
The test application is coded in c# with ado.net.
Please see the datail in
http://groups.google.com.hk/group/sy...3e9eecf9b54785

With your help. Now we can insert 10000 records to SA11 within 1s.
But What shocked me is that MSSQL can do this within 140~250ms.
They r using BulkCopy class(System.Data.SqlClient.SqlBulkCopy).

Also SA11 have SABulkCopy class.
But I found the performance is not so good.
It cost aobut 3000~5000ms to insert 10000 records.

The code is as follow.
//--------------------

private void btnADOBulkWrite_Click(object sender, EventArgs e)
{
dbInit();
if (rb1.Checked) //using SQLAny11
{
if (dbConnSA == null || dbConnSA.State !=
ConnectionState.Open)
{
MessageBox.Show("Connect to a database first.", "Not
connected");
return;
}
SABulkCopy bulkCopy = new SABulkCopy(dbConnSA);
DataTable workTable = new DataTable();
workTable.Columns.Add("c1", typeof(int));
workTable.Columns.Add("c2", typeof(float));
workTable.Columns.Add("c3", typeof(DateTime));

DateTime dtNow = DateTime.Now;

nTickStart = Environment.TickCount;
for (i = 0; i < iEnd; i++)
{
DataRow workRow = workTable.NewRow();
workRow[0] = 1;
workRow[1] = 1.1;
workRow[2] = dtNow;
workTable.Rows.Add(workRow);
}
bulkCopy.DestinationTableName = "tData";
//DataRow[] Rows = workTable.Rows;
bulkCopy.BatchSize = 5000;
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.WriteToServer(workTable);
nTickEnd = Environment.TickCount;

listBox1.Items.Add("Bulk.Write:" + iEnd.ToString() + ":" +
(nTickEnd - nTickStart).ToString());
}

if (rb2.Checked) //using MSSQL
{
if (dbConnMS == null || dbConnMS.State !=
ConnectionState.Open)
{
MessageBox.Show("Connect to a database first.", "Not
connected");
return;
}
SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnMS);
DataTable workTable = new DataTable();
workTable.Columns.Add("c1", typeof(int));
workTable.Columns.Add("c2", typeof(float));
workTable.Columns.Add("c3", typeof(DateTime));

DateTime dtNow = DateTime.Now;

nTickStart = Environment.TickCount;
for (i = 0; i < iEnd; i++)
{
DataRow workRow = workTable.NewRow();
workRow[0] = 1;
workRow[1] = 1.1;
workRow[2] = dtNow;
workTable.Rows.Add(workRow);
}
bulkCopy.DestinationTableName = "tData";
bulkCopy.BatchSize = 5000;
bulkCopy.BulkCopyTimeout = 60;

bulkCopy.WriteToServer(workTable);

nTickEnd = Environment.TickCount;
listBox1.Items.Add("Bulk.Write:" + iEnd.ToString() + ":" +
(nTickEnd - nTickStart).ToString());
}
}
//-----------------------------

According to this article
http://msdn.microsoft.com/en-us/library/7ek5da1a.aspx
it appears that SQLBulkCopy is using the Microsoft Bulk-Copy
API's for this. That is effectively the same as doing a BCP IN
or (in SQL Anywhere) a LOAD TABLE operation.
That might explain the performance of the MS product.

Often one needs to consider the whole task for comparisons
like these to be fair to both products. For example the cost
of loading that data one into the client side structures and
buffers may negate some or all of the benefit of this kind of
'fast' proprietary operation.

For example, if the data is coming from a proxy table or
a table on the same server, then a select-into or insert-from-
select operation could be significant faster than doing the
overhead of setting up the data for the call to the BulkCopy
operation. On the other hand, if the data is a file on the client
side already we have a LOAD TABLE FROM CLIENT FILE
operation that would also be more efficient.

Also, in a competative situation, customers sometimes ask for
unfair comparisons. In one case a customer asked me for us
[way back in the V7 beta days] to make load table operate
as fast as SQL Server 7. What they neglected to identify
is the operation they thought was the same thing as a load
table, was a completely server-side-only, out of cache,
select-into when the equivalent operation would have been
a BCP IN [adding some file system overhead they had,
not considered]. As such it was a false comparison and
worse for the customer, a false expectation of performance.
Hopefully you can detect any such other hidden costs in the
questions posed.

HTH

"Ivan.deng" <gdeng (AT) sybase (DOT) com> wrote

Hi Nick,

If the BCP IN / LOAD TABLE is the equivalent in SQLA, then why does not this
map to the SABulkCopy class such that Ivan's code can be more fairly
compared without the pre-requisite knowledge of your reply? I would assume
that this would make SQLA's figures look better when other people write
comparison code?

Cheers,

Shao


"Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in
message news:4ab79ac6$1 (AT) forums-1-dub (DOT) ..

The fact that SABulkCopy appears to be slower than
the regular methods may have something to do
with the actual test case being used. Ivan should be
sending that repro. to engineering directly; if that is
the concern here.

Just to clarify my earlier response a little more ....

BCP IN/Bulk Copy APIs are ASE/MSSQL and TDS-only
technologies. SQLAnywhere's ADO.Net techology connects
over our native protocol and not tds.

Also, to implement a File Load technology to pull the data
from an ADO.Net memory resident data set is a completely
different thing than Load Table; even if it could use the
server side implementation for part of that.

If that is the focus of this general posting then ....
Feel free to post that as an enhancement request or
discuss that on the "product_futures_discussion"
news group. Product management listens on that
newsgroup for such discussions.




"Shao Chan" <NoSpam (AT) NoSpam (DOT) com> wrote

I would test whether it makes any difference if you change this line:

to this:

Richard

"Richard Biffl" <sybaseforums (AT) removvethiisphrraseblacklettersoftware (DOT) com>
wrote in message news:4ab7cee9$1 (AT) forums-1-dub (DOT) ..
Maybe that should be:

SABulkCopy bulkCopy = new SABulkCopy(dbConnSA, UseInternalTransaction,
null);

Richard

Good suggestion Richard.

Also, using a tablelock will eliminate any contention from consideration
(for test purposes at least); if that is a possible factor.


"Richard Biffl" <sybaseforums (AT) removvethiisphrraseblacklettersoftware (DOT) com>
wrote in message news:4ab7e001$1 (AT) forums-1-dub (DOT) ..

Thank Richard,

I change the code as
SABulkCopy bulkCopy = new SABulkCopy(dbConnSA,
SABulkCopyOptions.UseInternalTransaction, null);
or
SABulkCopy bulkCopy = new SABulkCopy(dbConnSA,
SABulkCopyOptions.TableLock, null);

Still cost over 5s to insert 10000 records.
---------------------
Also I try load table statement.
The time is less than 140ms to insert 10000 records.
But it still cost 5~6 seconds to generate the SQL statement.
Here's the code
---------------------

if (rb1.Checked)
{
SACommand dbCmd = dbConnSA.CreateCommand();

string istr;
istr = "1,1.1,2009-09-09 09:09:09";
nTickStart = Environment.TickCount;
for (i = 0; i < iEnd-1; i++)
{
istr = istr + "\n1,1.1,2009-09-09 09:09:09";
}
dbCmd.CommandText = "load table tData using value
'"+istr+"'";

nTickEnd = Environment.TickCount;
listBox1.Items.Add("GenSQL:" + iEnd.ToString() + ":" +
(nTickEnd - nTickStart).ToString());

nTickStart = Environment.TickCount;
dbCmd.ExecuteNonQuery();
nTickEnd = Environment.TickCount;
listBox1.Items.Add("LT.Write:" + iEnd.ToString() + ":" +
(nTickEnd - nTickStart).ToString());
}
--------------------------
The reuslt is
GenSQL:10000:5610
LT.Write:10000:125

Any idea?

"Richard Biffl" <sybaseforums (AT) removvethiisphrraseblacklettersoftware (DOT) com>
wrote in message news:4ab7e001$1 (AT) forums-1-dub (DOT) ..

LOAD TABLE is certainly faster than INSERT statements. It's taking a long
time to generate your string of data, though, because each concatenation
requires a new memory allocation and copying the previously built string to
the new buffer. Instead of using a string, try using the
System.Text.StringBuilder class. Be sure to preallocate plenty of memory for
the StringBuilder buffer, so that additional allocations are not necessary.
One megabyte will hold 10,000 records of 100 bytes each, so that should be
enough. Here's an untested stab at the code:

using System.Text;
....
StringBuilder isb = new StringBuilder("load table tData using value
'1,1.1,2009-09-09 09:09:09", 1000000);
for (i = 0; i < iEnd-1; i++)
{
isb.Append("\n1,1.1,2009-09-09 09:09:09");
}
isb.Append("'");
dbCmd.CommandText = isb.ToString();

Please let me know if that code performs better.

Richard


"Ivan.deng" <gdeng (AT) sybase (DOT) com> wrote

Thanks Richard.

It's great helpful.
Now the average speed is the same as MSSQL to insert 10000 records.
About 140ms.

And if we insert 100000, the speed of sa is little fast than MSSQL(1400 vs
1600).
Thanks again.


"Richard Biffl" <sybaseforums (AT) removvethiisphrraseblacklettersoftware (DOT) com>
wrote in message news:4ab89e11$1 (AT) forums-1-dub (DOT) ..