Jump to content

Caching your resource


Recommended Posts

Date: 17/09/2014

Author: Anthony '.tree'/anumaz Dupont

Topic: Caching your resource to avoid multi SQL queries

Special thanks for helping out: cat, whoever made the mysql resource I am using, CourtezBoi


A. Identifying the need for caching and what should be cached

B. Visual Method

C. Scripting Method

A. Identifying the need for caching and what should be cached

You will probably need caching if your resource contains a lot of data which changes infrequently, but has to be shown/displayed frequently.. An example of this would most likely be a vehicle system or even an account panel, where data is requested often by the client. What you want to identify is that 'data send' from a server-side sql request being transfered to client. Having a lot of that leads to important technical issues and delay with your server, hence reducing its performance.

If your resource corresponds to that, then you most likely will want caching.

You won't want to cache every data of your resource. Doing that would simply result in making a double-weighted resource which you want to avoid for file size's sake.

B. Visual Method

Typically, cache is used when showing data, not updating. Hence, every time the client requests to show data, you'll want to use the cache. Although, every time the client requests to update data, you'll want to directly do that in the database. It is however logical to always use the cache to update, and then export your cache to your database when the resource stop, simply not typical.

First, at your resource start, you will load your table(s) with an sql update query. Secondly, you will want to use that table when a user requests to see data. Keep in mind to also update that table every time a change is made. On the third step, you will want to clear your cache when your resource is stopped.


C. Scripting Method

First step: Loading your data.

-- s_cache shall be added server type in your meta.xml 
-- On top of your s_cache script, you'll want to create all tables you will want to cache. 
table = { } 
addEventHandler("onResourceStart", resourceRoot, function () 
    local sql_table = exports.mysql:query_fetch_assoc("SELECT * FROM table_name") 
    for k, v in pairs(sql_table) do 
        table[k] = v 

Second step: Updating your database and cache, or using cache to show.

-- To show your cache table, you'll want to go the simple way. Example below, presuming your table is like this below, which is my username with some data. 
local cacheTable = { 
    [".tree"] = "any data", 
    ["anumaz"] = "other data" 
addCommandHandler("mydata", function (player) 
    local username = getPlayerName(player) 
    if cacheTable[username] then -- To check if it exists 
        outputChatBox(cacheTable[username], player) -- Presuming it is a single data, and it's a string 

To update data, you will want to update via the sql query and also to add it to the cache table. A common error is refreshing the cache table every time something is modified to it. Don't even think about it!

function updateData(key, data) 
    if exports.mysql:query_free("UPDATE table_name SET column_name='"..data.."' WHERE key='"..exports.mysql:escape_string(key).."'") then 
        cacheTable[key] = data 
        return true 
        return outputDebugString("SQL error at updateData function"), false 

Third step: Normally, when you stop resource, it should delete everything contained in it. You can also, at the same time, make a 'refresh cache' command.

addCommandHandler("refreshcache", function () 
    cacheTable = { } -- Clears the table 
    local sql_table = exports.mysql:query("SELECT * FROM table_name") 
    while true do 
        local v = exports.mysql:fetch_assoc(sql_table) 
        if not v then break end 
        -- We'll assume the first column in the table is called 'username' and returns a string. Second column is called 'data' and contains all we need. 
        cacheTable[v.username] = v.data 

A practical example is available on my gist at https://gist.github.com/tree-/8f97a42d7834c258069f

Link to comment
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...