Values Values Values

[Former Member]

It’s a nice surprise when you find something useful you weren’t looking for. I was working on inserting LOB data and checking on something in the manual when I came across a feature that I’d wanted a few times in the past – I had totally missed this coming into the feature list (someone is going to point out that it’s been there for ages and that I need to read the manuals more often – please be polite).

SAP Sybase IQ now supports listing multiple sets of values on a single INSERT statement. For example the line below would insert 3 rows into the target table.

INSERT INTO mytable VALUES (1,2,3,4), (5,6,7,8), (9,10,11,12);

There’s more here than the obvious developer convenience. For instance, statements are smaller meaning less time to transfer and parse the query. However the more significant aspect of this is that the rows are interpreted as a single statement.

In an earlier blog post (http://scn.sap.com/community/developer-center/analytic-server/blog/2013/06/21/row-level-locking-in-s...) I mentioned how when dealing with SAP Sybase IQ it’s important to consider statements per second and transactions per second when trying to come up with measures for data insertion rates – so being able to have many rows dealt with in a single statement will be significant. Again, keep in mind we’re talking here about trickle-feed (or row-by-row) performance rather than the bulk load rates.

Going back to the modest setup I had previously (2 cores, 4GB RAM, single SSD disk) I wanted to measure how this technique would compare to regular inserts into a standard table (i.e. not enabled for row-level-locking). The table has 4 integer columns and has tokenization disabled. Using this technique I was able to insert around 5,000 rows per second. This is nearly 10x faster than what the same setup could achieve using regular row-by-row insertions. Above 20,000 values in a single statement, the size of the statement began to take over as the dominant factor and performance dropped gradually, but I'll take 5,000 rows per second.

Good spot.