A performance optimisation for keyword-heavy scripts

Discussion in 'C-Bus Automation Controllers' started by ssaunders, Feb 12, 2024.

  1. ssaunders

    ssaunders

    Joined:
    Dec 17, 2008
    Messages:
    211
    Likes Received:
    26
    Location:
    Melbourne
    I use quite a few scripts that get and decode keywords added to objects. These usually take the form of "MYTHINGS, thingone=bim, thingtwo=bem, ", with MYTHINGS being an event script trigger, also used by a resident, and the rest being equals separated parameters. The residents usually check for updated keywords periodically using the event script trigger keyword, hence the function below.

    I found that GetCBusByKw was just too slow for often use, so came up with the following SQLite query. As the comment says, in my case this approach is usually 2x as fast.

    Code:
    --[[
    Get applicable groups and keywords
    Usually executes twice as fast as GetCBusByKw() overall, especially as equals separated keywords are processed here at the same time, and net/app/group/channel is pre-separated.
    --]]
    local function getGroups(kw)
      local grps = {}
      for _, t in ipairs(db:getall("SELECT o.address, o.tagcache, o.name FROM objects AS o JOIN objecttags AS ot ON o.id=ot.object WHERE ot.tag='"..kw.."'")) do
        local alias = knxlib.decodega(t.address)
        local parts = alias:split('/')
        grps[alias] = { name = t.name, keywords = t.tagcache:gsub(', ',','), net = tonumber(parts[1]), app = tonumber(parts[2]), group = tonumber(parts[3]), channel = tonumber(parts[4]), }
        local tags = {}
        local tg
        for _, tg in ipairs(grps[alias].keywords:split(',')) do
          parts = tg:split('=')
          if parts[2] then tags[parts[1]] = parts[2] else tags[parts[1]] = -1 end
        end
        grps[alias].tags = tags
      end
      return grps
    end
    It should probably strip extra leading/trailing spaces in the ipairs(grps[alias].keywords:split(',')) loop, and that could be accomplished with a function like this to trim parts[1] and parts[2]:

    Code:
    local function trim(s) if s ~= nil then return s:match('^%s*(.-)%s*$') else return nil end end
    I set the keywords without an equals to have a table value of -1, as nil would remove them from the table. Checking parameter values in other code later essentially says, "If it's not -1 then it's a key/value parameter, otherwise it's just a key..."

    By the way, for a fascinating look inside the DB schema, execute log(db:getlist('SELECT sql FROM sqlite_master')) in a script.
     
    Last edited: Feb 12, 2024
    ssaunders, Feb 12, 2024
    #1
Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.