SQL implementation

#21 Konrad - You should always use stored procedures in a database where possible, simply because they are faster than using SQL, when you pass an SQL statement to a database what you don't see is the optimizer in the background it looks at your query and how best to run it... should it use an index to lookup values or read the entire table, which table should it read first, and a thousand other factors.

With a stored procedure this isn't calculated each time so saves you that overhead (it is a small overhead but every little helps).

Other factors are it encapsulates as much of your code within the database as possible making it easier to manage.

Again it also adds some security, if someone gets hold of your game code, finding there is a change_password() procedure isn't a big deal, every system with an account management option would have one of them. If the SQL is in your code however, something like:

Quote:UPDATE ACCOUNTS SET Password = 'fred' WHERE ACCOUNT_ID = 2;

As a hacker I now know the table I'm interested in is called accounts and I know what the identifying column (account_id) and password fields are... from there it's a lot easier to reverse engineer your database and hack it further, if you're using some of the bigger databases i.e. Oracle I can restrict a user to only be able to execute stored procedures rather than accessing the tables directly.

#22 It's a great idea to restrict the game server's ip and user to only have access to stored procedures. Some of the SQL injection problems can be avoided this way. Also - about storing passwords and session, it's probably better to store lossy hashes such as md5 instead of clear text passes.

Andy, thanks for the heads up. Do you by chance know anything about MySQL's possibilities with stored procedures? I've only used sp's with MSSQL. T-SQL, Microsoft's expanded SQL implementation makes a pretty good use of stored procedures, and the gain in speed is clearly visible under a stress test.

However, I've heard, that MySQL's stored procedures feature is not too evolved yet. It's there, and you can use it, except it's optimizations are not exactly groundbreaking.

#23 Konrad - You've heard correctly on the mySql front, stored procedures were only added in the latest GA release and so what you get is effectively v1.0 of their stored procedures implementation so there'll be improvements and boosts as later revisions of this code are made.

They do support caching however so you do still get performance gains from using them and a reduction in network traffic (you're not having to send lengthy character strings containing SQL commands over the network and if your procedures do some data crunching then you may not need to pass as much data back to your application).

Plus all the other security and encapsulation benefits, there is really no reason not to use them - however I've only limited experience in their implementation so I'm not sure how functionally complete the language is, we're an Oracle/ MS Sql house so my experience lies with those databases.