Improving program performance using DBLIB?

[Former Member]

My company has developed software that needs to load data transferred to clients from a server using XMPP. The client uses SQL Anywhere for its database; the server uses a different database product entirely.

Our process works by keeping track of which rows of data have been transferred from one machine to the other for various data types that a client can subscribe to. When a client is added to the system for the first time, all of the data in the server to date gets downloaded. From then on, only the rows that change get sent.

Our code is written in C# and we transmit batches of data over the wire from the server to the client. On the client, we iterate over the data in the batch one row at a time and run a stored procedure which performs an "upsert" operation on a table. This is generally done using an INSERT INTO ON EXISTING UPDATE statement, but in a couple of cases is a bit more complex.

We've built the application and its running, but we're not happy with the performance of loading data of one particular type. There are millions of rows of data of that type that have to be downloaded to the client from the server and inserted into the SA database on the first run. Our code is running as fast as it cans loading data one row at a time right now. We need to find a way to speed it up, preferably by sending all of the rows of this type in the batch to the database in one I/O.

I've been kicking around the idea of writing a DLL in C language to speed this process up. The C# code would convert the objects in a batch into an array of structs. It would then marshal that array to a function in the DLL. The C language DLL would then create a temporary table and somehow bulk load all of that data into the temporary table. Then it would execute a stored procedure which would update the main table using all of the rows in the batch in a single operation, probably using a MERGE statement or a cursor.

I can do all of this, except that I don't know how to do the BCP into the temporary table. I've read the documentation on Embedded SQL and nothing in there pops out at me as a way to do this. And when I search the documentation for "BCP" all I get back are references to the LOAD TABLE command and the INPUT statement. The former requires the data to be in a file on the file system and the latter is for Interactive SQL only. Not what I've looking for.

Can anyone point me in the right direction?

P.S. It's important that the actions of the C DLL participate in the same transaction used by the C# code. Can that be done?

[VolkerBarth]

Doesn't that sound like a solution for some sync technology like MobiLink (which would batch inserts/updates by default, AFAIK)?

[VolkerBarth]

Just to ask: Are you aware that as of v12, the INSERT ... VALUES statement can handle multiple rows in one step by specifying

INSERT ON EXISTING UPDATE ... VALUES (row-1-values), (row-2-values),...

That might be a way to batch inserts on the SQL side, as well, without having to use temporary tables.

---

Using the OPENSTRING function, i.e.

INSERT ... SELECT FROM OPENSTRING(FILE, ...)

comes to my mind, too.

[Former Member]

Yes, I know about INSERT being able to insert multiple rows of data, but the problem is how do you get that to work from C#? You'd need one parameter per row of data to be inserted. That would probably put a limit on the number of rows in the batch equal to `MaxNumberOfParameters / NumberofColumns'. I don't know what the first number is, but we're talking about operating on at least 500 rows in a batch, maybe more. Regarding MobiLink, I don't think we have a license and the word "Mobi" in the name made me think of cell phones. This is a PC application, so it never occurred to me to look at that. Finally, thanks for the information on the OPENSTRING function. I didn't know that existed.

[VolkerBarth]

• As to MobiLink: It can surely also be used to synchronize between different server-class boxes, and MobiLink clients are available on "normal" platfroms like Windows, Linux, Solaris, too.
• As to the C# question: Sorry, I don't know - I thought you would generate "hard-coded" SQL statements for performance reasons, so parameter issues would not apply...

[johnsmirnios · Accepted solution]

LOAD TABLE does have a "USING CLIENT FILE" clause that will allow it to read from a file on the client machine: http://dcx.sybase.com/index.html#sa160/en/dbreference/load-table-statement.html*d5e60003

Take note of some of the requirements in the 'Privileges' section though:

When loading from a file on a client computer:

READ CLIENT FILE privilege is also required.

Read privileges are required on the directory being read from.

The allow_read_client_file database option must be enabled.

The read_client_file secure feature must be enabled.

From ESQL, you can also do wide "puts" to insert multiple rows in one request. I'm not sure if the .net driver has a way of exposing it natively -- it might. See: http://dcx.sybase.com/index.html#1201/en/dbreference/put-statement.html

[regdomaratzki · Accepted solution]

I added an answer that shows a bulk insert sample using our .NET provider.

[Former Member · Accepted solution]

John:

Thanks! We'll be looking at the PUT statement in ESQL. We intend to create a temporary table, PUT all of the data for all of the rows in the batch into the temporary table in one operation, and then MERGE the rows in the temporary table into the production table. Hopefully, this will be faster than inserting the data one row at a time using SACommand.