NodeJS connector latency even though query is cached

user_17920747 0
Viewed 68

I'm using the Snowflake NodeJS connector, and executing the same query several times. Snowflake's query history shows me the results are being returned from cache, as expected (so ~40ms). However, measuring on the client shows responses are taking up to 6s. What could be some causes of the latency, and how would I go about debugging this?

    const startTime = performance.now();
    const conn = await this.connection;
    return new Promise((resolve, reject) => {
      conn.execute({
        sqlText,
        complete: (err, stmt, rows) => {
          console.log(
            'Finished query, took: ' + (performance.now() - startTime)
          );
          err ? reject(new Error(err.message)) : resolve(rows);
        },
      });
    });
snowflake-cloud-data-platform
1 Answers
const startTime = performance.now();
    const conn = await this.connection;
    return new Promise((resolve, reject) => {
      conn.execute({
        sqlText,
        complete: (err, stmt, rows) => {
          console.log(
            'Finished query, took: ' + (performance.now() - startTime)
          );
          err ? reject(new Error(err.message)) : resolve(rows);
        },
      });
    });

will give large times, as the timer starts when the promise is made, yet the execution only starts when the promise is awaited on, outside this code.

if you add another timer inside the promise like

const startTime = performance.now();
    const conn = await this.connection;
    return new Promise((resolve, reject) => {
      const execStartTime = performance.now();
      conn.execute({
        sqlText,
        complete: (err, stmt, rows) => {
          console.log(
            'Finished query, total took: ' + (performance.now() - startTime) + ' exec took: ' + (performance.now() - execStartTime )
          );
          err ? reject(new Error(err.message)) : resolve(rows);
        },
      });
    });

now you are measuring the call to snowflake round trip + the ~40ms of the snowflake server time.

The snowflake answer time is how long it takes then to "answer the question after getting the request" aka match a cache. But how long it take your client to make the request, fetch the data, parse it, and declare the transfer done, will be much longer.

You could use something like fiddler or wireshark to watch the transactions (if they are being made from a local machine).

Also are you "experiencing it as slow" and thus looking for slowness, or doing some bad measurements and being shocked they are not zippy fast?

user_439920
Related