How to make this SQL insert more efficient?

General
1

Good day,

I have a flow which dynamically creates SQL params and updates some values in a table.

Depending on the some criteria, I need to update the daily, weekly, monthly, yearly or all time high score. Currently I have the following SQL code (prepared):

INSERT INTO liquidation_high_scores (
    exchange,
    symbol,
    currency, 
    day_high_score,
    week_high_score,
    month_high_score,
    year_high_score,
    all_time_high_score
) 
values (
    $exchange,
    $symbol,
    $currency, 
    $day_high_score,
    $week_high_score,
    $month_high_score,
    $year_high_score,
    $all_time_high_score
)

ON CONFLICT(exchange, symbol, currency) DO UPDATE SET 
    exchange = $exchange,
    symbol = $symbol,
    currency = $currency,
    day_high_score = $day_high_score,
    week_high_score = $week_high_score,
    month_high_score = $month_high_score,
    year_high_score = $year_high_score,
    all_time_high_score = $all_time_high_score;

My table looks like this:

CREATE TABLE liquidation_high_scores
    (
        exchange TEXT,
        symbol TEXT,
        currency TEXT,
        day_high_score INTEGER,
        week_high_score INTEGER,
        month_high_score INTEGER,
        year_high_score INTEGER,
        all_time_high_score INTEGER,
        PRIMARY KEY (exchange, symbol, currency)
    )

The issue is, unless a param is populated, i.e. only day_high_score is included in the params, the other values are overwritten with NULL. To workaround this, I've created a function with 5 outputs (one for day, week, month, year, all_time), and have connected the SQL node to each with code containing only the correct params. Obviously this is a little messy to manage, I wondered if there's a better way to achieve this, perhaps insert a value ONLY if included in the params, and avoid writing NULL for ones which are not?

I'm also not convinced my table is setup right, in essence, the combination of exchange, symbol and currency should be unique to each row... Duplicates should not be allowed.

Many thanks in advance for any help.

2

I don't understand your workaround, If I understand correctly, in the example you gave, where you only have day_high_score you have created an INJECT statement that only includes that column. However, since each row in the table contains all the columns the end result will be the same, a row with only day_high_score populated and all the rest empty.
Can you explain exactly what you want written to the table when you only have day_high_score available?

3

@Colin ,

My workaround looks like this (excuse my terrible code):

Function node:

// Initilise variables

var symbol = msg.template.content.symbol
var currency = msg.template.content.currency
var exchange = 'BitMEX'

var day_high_score = msg.payload[0].day_high_score
var week_high_score = msg.payload[0].week_high_score
var month_high_score = msg.payload[0].month_high_score
var year_high_score = msg.payload[0].year_high_score
var all_time_high_score = msg.payload[0].all_time_high_score

var new_day_high_score = false
var new_week_high_score = false
var new_month_high_score = false
var new_year_high_score = false
var new_all_time_high_score = false

var units = msg.template.content.units

var params

// Check current units against high scrores

if (units > day_high_score) {
    node.error("BitMEX: New daily high score!")
    new_day_high_score = true
}

if (units > week_high_score) {
    node.error("BitMEX: New weekly high score!")
    new_week_high_score = true
}

if (units > month_high_score) {
    node.error("BitMEX: New monthly high score!")
    new_month_high_score = true
}

if (units > year_high_score) {
    node.error("BitMEX: New yearly high score!")
    new_year_high_score = true
}

if (units > all_time_high_score) {
    node.error("BitMEX: New all time high score!")
    new_all_time_high_score = true
}

// Create SQL params

if (new_all_time_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units,
        $week_high_score:units,
        $month_high_score:units,
        $year_high_score:units,
        $all_time_high_score:units
    }

    msg.template.content.high_score = "all time"
    msg.params = params
    return [null, null, null, null, msg]
}

if (new_year_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units,
        $week_high_score:units,
        $month_high_score:units,
        $year_high_score:units
    }
    
    msg.template.content.high_score = "year"
    msg.params = params
    return [null, null, null, msg, null]
}

if (new_month_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units,
        $week_high_score:units,
        $month_high_score:units
    }
    
    msg.template.content.high_score = "month"
    msg.params = params
    return [null, null, msg, null, null]
}

if (new_week_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units,
        $week_high_score:units
    }
    
    msg.template.content.high_score = "week"
    msg.params = params
    return [null, msg, null, null, null]
}

if (new_day_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units
    }
   
    msg.template.content.high_score = "day"
    msg.params = params
    return [msg, null, null, null, null]
}

I have then connected an SQL node to each output, for example:

INSERT INTO liquidation_high_scores (
    exchange,
    symbol,
    currency, 
    day_high_score
) 
values (
    $exchange,
    $symbol,
    $currency, 
    $day_high_score
)

ON CONFLICT(exchange, symbol, currency) DO UPDATE SET 
    exchange = $exchange,
    symbol = $symbol,
    currency = $currency,
    day_high_score = $day_high_score;

OR

INSERT INTO liquidation_high_scores (
    exchange,
    symbol,
    currency, 
    day_high_score,
    week_high_score,
    month_high_score,
    year_high_score,
    all_time_high_score
) 
values (
    $exchange,
    $symbol,
    $currency, 
    $day_high_score,
    $week_high_score,
    $month_high_score,
    $year_high_score,
    $all_time_high_score
)

ON CONFLICT(exchange, symbol, currency) DO UPDATE SET 
    exchange = $exchange,
    symbol = $symbol,
    currency = $currency,
    day_high_score = $day_high_score,
    week_high_score = $week_high_score,
    month_high_score = $month_high_score,
    year_high_score = $year_high_score,
    all_time_high_score = $all_time_high_score;
4

What does the SQL look like if only one param?

5

Hi Colin, not sure I understand you, the upper most SQL query updates only 1 param (day)?

Obviously I didn't post the other queries, they're just the same bar the _high_score params.

Basically I wondered if there's a way to use the full query (with all _high_score values), but only insert if the param isn't NULL?

6

What SQL is this? mysql, sqlite? mssql?

Why not dynamically build the 'on conflict' portion of the sql statement based on which value is coming in? That way you would only update that single column?

7

Hi,

It's SQLite, that's what I want to do, but have no experience / skill to do it... :frowning:

8

You could try to normalize your data model...
First step would be to create tables for the different high scores.

9

Well lucky you, you get to learn something new! I would start be reading about the sqlite update statement (https://www.tutorialspoint.com/sqlite/sqlite_update_query.htm) then layout your generic update statement.

you could build your update in 'chunks' like

sql_command  = 'the first part'
sql_where = 'where clause'
if condition1 sql_set = 'values to update on condition 1'
if condition2 sql_set = 'values to update on condition 2'
.
if conditionN sql_set = 'values to update on condition n'
sql = sql_command + sql_set + sql_where

make sure to aadd a debug to what ever node you use to create the sql so you can see if it 'looks' correct.

1 Like