SQL question prepared, transaction or query/exec?

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.

Performance: use prepared statements. A prepared statement is just as its name says: prepared, already parsed.

Security: pass parameters as parameters, never build your query string from user input!


Hmm… That’s what I thought, I just wanted to make sure prepared statements is the best choice.
Is it worth looking more at transactions or do you think regular prepared statements will be enough?

Transactions are not for speed but correctness.
Use them if you have multiple operations which must all succeed or none.
(Transfer money from Alice to Bob: deduce from bc Alice, add to Bob. Don’t want any to happen if the other fails.)

1 Like

Ohh… That makes sense, alright then I’ll just stick with prepared statements.
Thanks :slight_smile:

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.