In the previous blog post in this series, we took a deep look at the two major different JavaScript modules that act as HANA Clients: @sap/hana-client and hdb. Architectural differences aside, let’s return to that sample usage of the @sap/hana-client we saw at the end of the previous blog post:
import hanaClient from "@sap/hana-client"
import * as xsenv from "@sap/xsenv"
export function example1(dbQuery, callback) {
xsenv.loadEnv()
let hanaOptions = xsenv.getServices({
hana: {
label: "hana"
}
})
let conn = hanaClient.createConnection()
let connParams = {
serverNode: hanaOptions.hana.host + ":" + hanaOptions.hana.port,
uid: hanaOptions.hana.user,
pwd: hanaOptions.hana.password,
ca: hanaOptions.hana.certificate,
encrypt: hanaOptions.hana.encrypt,
sslValidateCertificate: hanaOptions.hana.sslValidateCertificate
}
conn.connect(connParams, (err) => {
if (err) {
callback(err)
} else {
conn.exec(dbQuery, (err, result) => {
if (err) {
callback(err)
} else {
conn.disconnect()
callback(null, result)
}
})
}
return null
})
}This sample starts off straight forward enough. We load the database connection details and then pass them to the Client module. But once we connect, things can get a little complicated if you aren’t used to Node.js asynchronous callback coding. The conn.connect doesn’t return a connection object but instead has an embedded function that will be called once the command is complete. This is what is referred to as a callback. This is a key aspect to the architecture of Node.js and how it achieves elevated levels of parallel performance.
The idea is that in Node.js, Input and Output operations (like database calls) should all be non-blocking. The program execution doesn’t stop and wait for completion of the command before moving onto the next command, like in many programming languages. This is great for optimization of your execution time. Why should operation two wait for the completion of operation one if there is no dependency between them? And it provides an approach that is simpler for humans to use and design than threading models. But complexity comes into play, as shown in this example, when you do have commands that are dependent upon one another. You clearly can’t use the connection to send a SQL statement into the database until the connection to the database is established.
The most basic solution is to use callback functions and embed the next step of the logic only when the previous operation calls the function to indicate it is finished and passes it any results. So, in this example once the connection is established, the next function is called. Only then can the SQL statement be executed. But the results aren’t available immediately. There is another callback that is executed upon completion of the database query and the results are passed into it.
This is an elegant solution as far as the execution engine is concerned. Wasteful waiting time is avoided and other parallel operations can be performed while the execution is waiting on results from some other part of the system/infrastructure. The downside is that the code becomes more difficult for humans to read and maintain. What options do we have that would be more friendly?
Note: In each section, there are code samples to support the tool we are discussing. All the code for these samples is available in GitHub here: https://github.com/SAP-samples/sap-tech-bytes/tree/2022-03-17-hana-javascript.
Synchronous Code
One option is to just say “forget asynchronous”. The @sap/hana-client actually does have a synchronous mode. If you just don’t pass in callback functions, then the module will automatically fall back to synchronous, blocking execution. The same example with synchronous execution:
export function example1(dbQuery) {
xsenv.loadEnv()
let hanaOptions = xsenv.getServices({
hana: {
label: "hana"
}
})
let conn = hanaClient.createConnection()
let connParams = {
serverNode: hanaOptions.hana.host + ":" + hanaOptions.hana.port,
uid: hanaOptions.hana.user,
pwd: hanaOptions.hana.password,
ca: hanaOptions.hana.certificate,
encrypt: hanaOptions.hana.encrypt,
sslValidateCertificate: hanaOptions.hana.sslValidateCertificate
}
conn.connect(connParams)
let result = conn.exec(dbQuery)
conn.disconnect()
return result
}Or the same two blocks side-by-side for even better comparison:
hana-client Asynchronous with Callbacks vs. Synchronous
That’s a significant difference in readability between the two samples and probably the synchronous options feel more comfortable and familiar to most of you. As tempting as that synchronous logic might be, you’re going to want to avoid it once you see what it does to your performance.
We will use the Node.js perf_hooks to perform some tests that will show us execution time but will also measure placement in overall program time, which will allow us to see blocking as well.
import { performance, PerformanceObserver } from "perf_hooks"
const perfObserver = new PerformanceObserver((items) => {
items.getEntries().forEach((entry) => {
console.log(entry)
})
})
perfObserver.observe({ entryTypes: ["measure"], buffered: true })Then we place some performance marks and measurements before and after the execution of both the Asynchronous with Callbacks and Synchronous examples:
function test1() {
performance.mark("1-start")
let result = hanaClientSync.example1(`SELECT SCHEMA_NAME, TABLE_NAME, COMMENTS FROM TABLES LIMIT ${limit}`)
performance.mark("1-end")
performance.measure("Synchronous hana-client example", "1-start", "1-end")
}The code sample for this blog post has a performance test script that can be executed via npm run perf. It lets you choose different performance test scenarios and dataset sizes.
Performance Testing
Our Synchronous test starts at 13.5 seconds and it lasts just about one second. Next, we see two hana-client Asynchronous examples. They both start at about 14.6 seconds and run for a half a second. Now that tells us several things.
First the Synchronous example takes twice as long to execute. One second vs. half a second. That’s because even within its own internal process it’s waiting leading to longer overall execution times. The far bigger impact is that why the synchronous query is running nothing else can execute. It keeps any of the further tests from running. But the Asynchronous tests have no such issue. They both start within a few milliseconds of one another and run entirely in parallel to each other. And not just two queries in parallel either. In this test suite we can see many tests all starting execution within a few milliseconds of one another and all executing in about the same amount of time.
Massively Parallel Queries
Promises
After seeing those performance differences, no one is really going to consider using the synchronous approach. Is there no hope then but to force ourselves to use Callbacks? Thankfully, there are better options.
The @sap/hana-client has recently added support for Promises in the 2.11 version (December 2021). Promises are a construct in Node.js/JavaScript intended to keep the Asynchronous, non-blocking execution but cleaning up the syntax by avoiding the deep nesting that often occurs with Callbacks. Instead of a callback function being triggered an object is returned that “promises” that it will reach a certain state later. Your program logic can then be written to flow and chain assuming the correct completion and only fail when the promise is broken.
import hanaClient from "@sap/hana-client"
import hanaClientPromise from "@sap/hana-client/extension/Promise.js"
import * as xsenv from "@sap/xsenv"
export function example1(dbQuery) {
return new Promise((resolve, reject) => {
xsenv.loadEnv()
let hanaOptions = xsenv.getServices({
hana: {
label: "hana"
}
})
let conn = hanaClient.createConnection()
let connParams = {
serverNode: hanaOptions.hana.host + ":" + hanaOptions.hana.port,
uid: hanaOptions.hana.user,
pwd: hanaOptions.hana.password,
ca: hanaOptions.hana.certificate,
encrypt: hanaOptions.hana.encrypt,
sslValidateCertificate: hanaOptions.hana.sslValidateCertificate
}
hanaClientPromise.connect(conn, connParams)
.then(() => {
return hanaClientPromise.exec(conn, dbQuery)
})
.then((result) => {
conn.disconnect()
resolve(result)
})
.catch(err => {
reject(err)
})
})
}And the most important parts side by side again:
The program execution and runtime are essentially unchanged, but we have syntax for the developer that is easier to read and write. The nested callbacks are replaced by the then chain.
Async / Await
JavaScript syntax continues to evolve. Promises were introduced in ES2015 and were a welcome solution to avoid the Callback Hell situation. ES2017 delivered an even further refinement of promises with the delivery of new syntax ASYNC and AWAIT.
Built on the same technical framework of Promises, Async and Await are further semantic sugar that makes your code read like synchronous code but with none of the performance drawbacks. Here is the same hana-client promises example but now rewritten using Async and Await.
import hanaClient from "@sap/hana-client"
import hanaClientPromise from "@sap/hana-client/extension/Promise.js"
import * as xsenv from "@sap/xsenv"
export async function example1(dbQuery) {
try {
xsenv.loadEnv()
let hanaOptions = xsenv.getServices({
hana: {
label: "hana"
}
})
let conn = hanaClient.createConnection()
let connParams = {
serverNode: hanaOptions.hana.host + ":" + hanaOptions.hana.port,
uid: hanaOptions.hana.user,
pwd: hanaOptions.hana.password,
ca: hanaOptions.hana.certificate,
encrypt: hanaOptions.hana.encrypt,
sslValidateCertificate: hanaOptions.hana.sslValidateCertificate
}
await hanaClientPromise.connect(conn, connParams)
let result = await hanaClientPromise.exec(conn, dbQuery)
conn.disconnect()
return result
} catch (error) {
throw error
}
}The Async keyword is added to the definition of the function. This allows the use of the Await keyword within your syntax. Anywhere you want your logic to wait before continuing you just use the Await keyword. But this waiting isn’t like the Synchronous logic we saw earlier. It doesn’t block the entire program execution. It only waits within the scope of the current function.
This gives you more granular control over the parallelization of your application without introducing complex syntax. If you have two (or more) queries you want to run in parallel, just put them into separate functions. You can then use syntax like await Promise.all. It will run all the inner functions in parallel but only continue processing once all the inner functions are complete.
let [outputOne, outputTwo] = await Promise.all([queryOne(), queryTwo()])Closing
In this blog post we’ve expanded our knowledge around the usage of the @sap/hana-client and looked at more efficient ways to both write and execute asynchronous code with it.
Then in the next part we will look at the functionality provided by wrapper modules like @sap/hdbext, sap-hdbext-promisfied, and sap-hdb-promisfied. All the examples have shown JavaScript running standalone from the command line. But more likely you need to use the functionality within a web enabled service of some sort. Also, in Part 3 we will look at how these tools can be used with express.
Part 2: Promises – This blog post
