question about database queries resource consumption

[fafa-fr]

Hi,

Here's a question about resource consumption of database queries. I read in Reversi Tutorial that it's better to avoid lots of queries when possible, but I'd like to have more details about this.

I'd like to know if the action in itself of connecting to the database requires time, or if it's the query that is time consuming (which would mean that the complexity of the query makes a big difference). Or both.

Answers to the following questions could help me :
- Is it better to use getActivePlayerId and then getActivePlayerName (2 queries, but they're very simple), or to use loadPlayersBasicInfos (only one query, but with informations we don't need)?
- if during the same game state I need 2 or 3 times (in different methods) one row (each time a different one) of a large database table (say, about 100 rows of 8 or 10 fields), I have 2 options : get the entire table once, and pass it to the methods that need a row, or in each of the 2 or 3 method, send a new query to get the single row I need. Is one of these options far better than the other one?

Thanks a lot

[Een]

Hi,

I'd like to know if the action in itself of connecting to the database requires time, or if it's the query that is time consuming (which would mean that the complexity of the query makes a big difference). Or both.

Both
But on BGA, queries in games are often simple, and the tables they run on are not very big. So most of the time it will be better to get as much as you can in one query to avoid the overhead of connecting multiple times.

- Is it better to use getActivePlayerId and then getActivePlayerName (2 queries, but they're very simple), or to use loadPlayersBasicInfos (only one query, but with informations we don't need)?

Usually the second option would be better (loading all the columns needed for one row in one query). In this specific case we have a cache mechanism for those 3 very often used methods to avoid using the database too much, so it actually amounts pretty much to the same.

- if during the same game state I need 2 or 3 times (in different methods) one row (each time a different one) of a large database table (say, about 100 rows of 8 or 10 fields), I have 2 options : get the entire table once, and pass it to the methods that need a row, or in each of the 2 or 3 method, send a new query to get the single row I need. Is one of these options far better than the other one?

If you had to use the table a lot, the first option would probably be better performance wise, as long as you have enough memory (not a problem for tables of the size you describe).
But for only 2 or 3 times, it probably doesn't make much of a difference, so at that level in my opinion you should favor code clarity (most explicit way of doing things) over performance considerations.

But if at some point you find yourself making complex queries in loops, especially recursive ones, well then getting all the table in an array and working on the array in your loops can make quite a difference. I rewrote Saboteur path checker to do just that because I didn't get it right the first time

Hope it helps!

[fafa-fr]

It helps a lot ! It's very complete and precise, and will be useful, many thanks.