I'm working on an economy Plugin and use SQLite for storage. When I use 'UPDATE' in SQL the Server lags for about 3 seconds.
Code:
DB:exec("UPDATE `money` SET `money`='" .. Money .. "' WHERE `uuid`='" .. PlayerUUID .. "'")
Three seconds sounds like a serious issue. Does it happen every time, or is it only occasional? We're doing much harder work with SQLite in other plugins and it has no such performance problems. What platform are you using? Debug or release build?
Also please note that your code is kinda vulnerable to SQL injection. You should never put external strings into the query, but rather use prepared statements and placeholders:
local stmt = DB:prepare("UPDATE money SET money = ? WHERE uuid = ?")
stmt:bind_values({Money, PlayerUUID}) -- Binds the question-marks in the previous query to actual values
stmt:step() -- Execute the DB query
stmt:finalize() -- Free up the resources
This is so common that most plugins wrap it in a helper function. In the Gallery plugin that SphinxC0re linked, it's here:
https://github.com/cuberite/gallery/blob...a#L79-L107
It happens every time and the lag is between 1-6 Seconds. I'm using Debian 8 and I don't know what build I am using. Where can i find it?
EDIT: Is it possible to use MySQL instead of SQLite?
Core has the ban and whitelist that use SQLite. You could try that as well.
I tested every Plugin you wrote and it always lags when something changes in the SQL. For example Coiny: /money = No Lag /money give [Player] [Amount] = Lag
I downloaded the executable
I can't believe our code is doing that, we're just a thin wrapper over sqlite3. Could you try opening the affected DB file in the command-line "sqlite3" utility and try to time the queries there?
Code:
sqlite3 file.sqlite
.timer on
select * from Money;
insert into Money (Money, uuid) values (10, "1234");
update Money set money = 20 where uuid = "1234";
select * from Money;
.exit
That looks reasonable. I am out of ideas.
