anumaz Posted September 20, 2014 Share Posted September 20, 2014 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 Chapters 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 end end) 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 end end) 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 else return outputDebugString("SQL error at updateData function"), false end end 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 end exports.mysql:free_result(sql_table) end) A practical example is available on my gist at https://gist.github.com/tree-/8f97a42d7834c258069f Link to comment
Recommended Posts