This post provides an introduction to the new feature added to HammerDB v4.9 by @krithikasatish and @JoshInnis to provide accelerated load performance for both SQL Server TPROC-C and TPROC-H schemas.
This accelerated load is implemented using the BCP utility and is turned on or off with the “Use BCP Option” checkbox. With the default for v4.9 to have this feature enabled.
Insert based load
In previous releases, HammerDB loads data using inserts, (or supported a manual use of BCP by generating flat files with the datagen option) and you can still perform schema loads with inserts by deselecting the Use BCP Option.
This earlier functionality has the advantage that there are no intermediate staging files required, and data is inserted into the database with multi-row inserts as soon as it is created. The disadvantage of this approach is that the database sees the insert as a regular database insert, maintaining consistency and recoverability, and with a round-trip to the HammerDB client per multi-row, this is what we see in recent expensive queries.
In this example on a development PC the regular build started at 10:48:35 and ended at 10:54:46 meaning it took 6 minutes 11 seconds to build our 20 warehouse schema.
BCP based load
Now when we select the “Use BCP Option”
We can see that we are now using an insert bulk command (although the item table being fixed at 100K rows and loaded by the monitor virtual user continues to use regular inserts).
In our PC test the build started at 11:28:16 and ended at 11:30:27 meaning it took 2 minutes 11 seconds to build our 20 warehouse schema.
Using BCP our TPROC-C build now completed almost 3X faster and tests on TPROC-H showed similar results.
Note that for the BCP functionality, temporary data files will be created and deleted in the TMP environment variable area, and you can find this location by running the command
(HammerDB-4.9) % puts $::env(TMP) C:\Users\Hammer\AppData\Local\Temp
in the HammerDB console, and then see the files being created and deleted as the build progresses.
Summary
The new Use BCP Option for SQL Server accelerates both TPROC-C and TPROC-H schema loads, so you can get to running your benchmarks faster. We thank @krithikasatish and @JoshInnis for this excellent contribution to HammerDB.