Hello, I been struggling with some decisions related to SQL in my project.
So to explain a bit what queries I will be doing. I am working on a monitoring software and in the database I have information about all the clients and all of their checks (CPU usage, network usage etc.)
So every time I restart the server I need to build all the clients, so first I select every client in a table, from there I loop through and get their groups, commands and latest checks.
Once that is done I have a timer that is ran every second to go through all the clients to see if its time to send out a new check to the client or not.
When a check has been sent it will save it to the database.
So my question that I been struggling to figure out is, what should I use, just regular query/exec, prepared statement or transactions?
I do most of the stuff through goroutines, so on startup I first grab all of the client info, then I build the client in a goroutine to make things go faster, so on startup there will be a lot of similar queries, so I am guessing a prepared statement can be good on startup. Since I might have like 20-30 clients and I need to do about 2-3 queries for each client (I could probably rework the startup process to lower it to 1-2 queries for each client, but still gonna require some frequent queries in the beginning).
Once the server is up and running it wont be that frequent querying, but once a minute or so, I might need to do 10-20 checks, which will result in 10-20 queries.
So in those situations would it be worth using prepared statement if I think I will be doing regular querying, or is it better to just use query/exec all together?
I have also read about SQL transactions but I am not really sure if it’s something that will benefit me or not.
This is more a question about performance rather then security, I know prepared statement gives extra security, but as all the data will be coming directly from the database and in memory, I am not really worried about the security, only security problem I can see is if someone would inject something bad in the response I get from the client, so I guess that’s one benefit I would have with prepared statement.
