|
We have a custom process for loading data into a table of our database that is currently written using an INSERT statement that loads one row of data into the table at a time. I want to speed that up by using the Before anyone tells me to use In any case, it's my assumption that the data in the BLOB must be layed out as an array of rows, with the layout of a row matching the layout of the columns in the table. What I need is documentation on how to format the data. I'm guessing that C# |
|
It's not that hard... it's called openSTRING for a reason ( hint: it deals with strings :) CREATE TABLE t ( a BIGINT NOT NULL PRIMARY KEY, b INTEGER NOT NULL, c VARCHAR ( 10 ) NOT NULL, d DECIMAL ( 11, 2 ) NOT NULL, e DATE NOT NULL, f TIMESTAMP NOT NULL ); BEGIN DECLARE @b LONG VARCHAR; SET @b = STRING ( '1,101,''aaa'',123.45,2013-05-09,2013-05-09 16:11:16.057\x0d\x0a', '2,202,''bbb'',234.56,2013-05-10,2013-05-11 16:11:16.063\x0d\x0a', '3,303,''ccc'',456.78,2013-05-11,2013-05-11 16:11:16.069\x0d\x0a' ); INSERT t SELECT * FROM OPENSTRING ( VALUE @b ) WITH ( TABLE t ( a, b, c, d, e, f ) ) OPTION ( DELIMITED BY ',' ) AS openstring_t; COMMIT; SELECT * FROM t ORDER BY a; END; a b c d e f -------------------- ----------- ---------- ------------- ---------- ----------------------- 1 101 aaa 123.45 2013-05-09 2013-05-09 16:11:16.057 2 202 bbb 234.56 2013-05-10 2013-05-11 16:11:16.063 3 303 ccc 456.78 2013-05-11 2013-05-11 16:11:16.069 See also: Pushing OPENSTRING and CROSS APPLY Thank you, Breck. It'll be easier to build a string on the fly than a binary block.
(10 May '13, 08:46)
TonyV
Replies hidden
1
Keep in mind that it may be easier, but maybe not memory friendly nor faster in the end to do. Using up more memory space in your .NET program to parse, re-generate, and send strings to the database server takes away memory from the database server to actually insert the rows against the database pages. This is also why when MobiLink sends rows down to a remote, both the database server and the synchronization client dynamically manage memory to try and balance these two conditions. MobiLink also uses a packed binary format to efficiently transfer the rows.
(14 May '13, 10:24)
Jeff Albion
2
Jeff: This has been running in production now for a few months. Performance is great and definitely faster than inserting the data one row at a time. We've had to limit the amount of memory that the database server uses to begin with, or other applications do not perform well. Plus, by sending data for many rows in one I/O operation, we end up reducing the total time doing I/O. That is, the time it takes to upload N rows in one I/O is less than the time it takes to upload N rows one at a time. It's been a big win for us.
(07 Aug '13, 09:11)
TonyV
|
So your data is layed out in binary form (as it is sent that way across the wire, as you have written in the other FAQ)?
As Breck has explained, OPENSTRING() usually expects data in string format, just like LOAD TABLE. Therefore you would usually have to convert the data yourself in a appropriate textual representation before running OPENSTRING() on them.
Note: OPENSTRING does also support OPTION (FORMAT BCP), however, I do not know whether that binary format used by ASE (and MS SQL, methinks) fits your data.
Just to add: Whereas I would think it's easier for you to format your own data to fitting SQL string literals yourself than to find out how to make OPENSTRING() parse your binary data accordingly, the usage of casting from binary values with the help of HEXTOINT and similar functions may work, as well - cf. this doc page.
Actually, the data is transmitted over the wire in XML. When it is received, it is deserialized into .NET objects. By the time I am writing it to the database, it's in binary (this code never sees the XML).
I am going to create a memory stream and write the data in binary to that memory stream. Or I could write it to the same memory stream as string data and let OPENSTRING parse it. The problem is what field & record delimiters to use in that case. You don't want the data to contain the same characters that are being used as delimiters.
My blog is your friend; try this search string in Google...
delimiter site:sqlanywhere.blogspot.com
Then try this one...
xml site:sqlanywhere.blogspot.com
If the data is in xml you can use the openxml() function to parse and insert.
This is a sample from a procedure WHERE _XML is the parameter for the xml stream :
INSERT INTO EASYCAT_CATALOGUE (IDCATALOGUE,DESIGNATION,"VERSION",DATEVERSION,IDSUPPLIER,IDMONNAIE,IDLANGUE,IDPOIDS)
SELECT _CAT, DESIGNATION, "VERSION", DATEVERSION, IDSUPPLIER, IDMONNAIE, IDLANGUE, IDPOIDS
FROM openxml(_XML,'/CATALOGUE' )
WITH (DESIGNATION CHAR(60) './DESIGNATION',
"VERSION" CHAR(60) './VERSION',
DATEVERSION DATE './DATEVERSION',
IDSUPPLIER CHAR(3) './IDSUPPLIER',
IDMONNAIE CHAR(3) './IDMONNAIE',
IDLANGUE CHAR(2) './IDLANGUE',
IDPOIDS CHAR(2) './IDPOIDS');
You can see that is a standard INSERT SELECT STATEMENT and in the WITH CLAUSE you define the relation ship between the xml structure and the table structure
Generally, a good hint, but Tony has already stated that
Yes, I have read that, but actually I understand the client :
1) Receive the data in XML format.
2) Transform the data in .NET OBJECT.
3) Build a big string with this objects to do only one call to the server.
My idea is :
1) receive the data in XML format.
2) send the data in XML format to the server in one call.
3) do the parsing on the server side as I show in my sample.
This contruct suppress the .NET Objects step. In my experience this is only between 2 and 3 time faster...