13 Replies - 984 Views - Last Post: 21 March 2014 - 02:07 PM Rate Topic: -----

#1 Toto-A   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-March 14

Database query - speed question

Posted 21 March 2014 - 03:02 AM

Hello, I wrote this question in game-forum page but think PHP adiance can help me too.
I am writing a turn-based browser game. Every turn is 12 hour - time to make all necessary moves. On every round user makes many decisions - every one of it changes the variable in Database. Now my code is simple - on every users click (which effects Database data) it makes ajax request and does what have to be done - change data or shows data (or both). I am trying to avoid unnecessary ajax-request and keep unchangeable Database variables in J/Script arrays, but still most click-events are contacting Database through ajax. On my localhost/phpmyadmin it works OK.
Now the question is - will it be significant speed downfall if their will be for example more then 1000 users? Or it will be better to change code in a way that at start we put all necessary DB data in J/Script arrays and user will effect this arrays not contacting DB but every (for example) 2 minutes (if there were any changes in J/Script arrays) ajax will upgrade Database in one query. I know it will give improvement in speed but is this significant issue in my case cause I really prefer the way I am doing it now - make query on every user-click changing Database cause - 1. I already done some code this way, 2. Second style approach I am afraid can sometimes lead to user's changing data lost.
Sorry for my English and I am not a programmer - just learn some Javascript, php to write this game.

Is This A Good Question/Topic? 0
  • +

Replies To: Database query - speed question

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3038
  • View blog
  • Posts: 11,678
  • Joined: 03-December 12

Re: Database query - speed question

Posted 21 March 2014 - 03:19 AM

I don't specifically understand how your game works, but if it is a user's turn for 12 hours, I would do one request at the end of their turn committing everything instead of multiple queries.
Was This Post Helpful? 0
  • +
  • -

#3 Toto-A   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-March 14

Re: Database query - speed question

Posted 21 March 2014 - 03:37 AM

Thank you for quick response.
Its a turn-based war-game. Players have 12(or 24) hours to make their moves. Its time-consuming - u cant finish turn in couple of minutes if you want to win. Player gives numbers of orders which effects Database.
What you proposed - one request at the end (window close for example) makes me afraid that it can cause data loss. I dont know - for example if window was not close properly. So my question is - how significant is speed drop if I make ajax-request on every users click?
Was This Post Helpful? 0
  • +
  • -

#4 ge∅   User is offline

  • D.I.C Lover

Reputation: 195
  • View blog
  • Posts: 1,194
  • Joined: 21-November 13

Re: Database query - speed question

Posted 21 March 2014 - 03:44 AM

What kind of requests are we talking about and how often do they occur ?

Since it is turn based, I guess you send all the necessary data to the client once it is his turn, then you only update it, right ?

In this case, I would avoid event-based requests in favour of timer-base requests :

- When a user interacts with the UI, I would reflect the changes immediately in the document with Javascript and I would construct an object with all the updated data.

- Every 10 seconds or so, I would send my object to the server (if it's not empty) as a JSON string and I would clear it immediately to be sure only fresh data are sent in every request.

- On the server side I would parse the JSON string, check what data have changed and update the database.
Was This Post Helpful? 1
  • +
  • -

#5 Toto-A   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-March 14

Re: Database query - speed question

Posted 21 March 2014 - 04:06 AM

I am talking about ajax-post request on user's click that change data from Database.
How often it happens - there is no need to hurry so it depends on players - some will think for a while and then click to change something, then again think and click, some will click fast several times and then look and think about what he done and is it good or not and change it again.
There are about 100 - 500 clicks to be made in 12 hours that effects Database. You can do it in an half an hour or couple of hours and then just wait. So I think its not a big pressure on DB even if u have thousands users and I can afford ajax request on every database-changing click - am I right or am I very wrong at this?

Also I am not sure is this relevant but every 10 players on each map have their own Database of game data.
Was This Post Helpful? 0
  • +
  • -

#6 Toto-A   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-March 14

Re: Database query - speed question

Posted 21 March 2014 - 05:42 AM

About data I send to client at start - currently I only send him only data that have visual effect, others are send only on users request to look at them. Of cause I ll have to change this approach once I choose to make timer-base requests code.
Was This Post Helpful? 0
  • +
  • -

#7 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2461
  • View blog
  • Posts: 7,490
  • Joined: 15-January 14

Re: Database query - speed question

Posted 21 March 2014 - 09:39 AM

Whether or not the server can support a request on every click depends on how much data is being sent and how much bandwidth the server has available to it. You can get a dedicated server and it will be able to support many more people than a shared hosting account, for example.

As a general rule though, it is better if you minimize the data that you send. That doesn't necessarily mean that you can't send data on every click though, but there's no reason to send data back to the server that hasn't changed. You only need to send the data that actually changes, and likewise the server shouldn't send data back to the client when that data hasn't changed.

I've done quite a bit of work with online training courses, and one of the standards for transferring data between a course and the management system is called SCORM. With SCORM, most often data is cached on the client in local data structures like arrays or hash tables. Part of the SCORM API is a function called LMSCommit. When the course calls LMSCommit, that means the browser must send all of its data to the LMS so that it gets saved in the database, so the course can decide if it's fine to keep data cached on the client, or if all of the data needs to get saved in the database. You might do something similar, maybe after 5 or 10 clicks you send all of the data.

One thing to consider is whether or not other players need to see the updated data. If other players should be able to see the results of the player's turn as they are making their moves, then you need to commit data as often as reasonable so that the other players can see the updates. If the other players don't see the results until the turn is finished, then you can cache data for longer on the client before sending it to the server.
Was This Post Helpful? 1
  • +
  • -

#8 ge∅   User is offline

  • D.I.C Lover

Reputation: 195
  • View blog
  • Posts: 1,194
  • Joined: 21-November 13

Re: Database query - speed question

Posted 21 March 2014 - 09:42 AM

In a turn base game (such as chess) you want to have an overview of the game state, so you are going to analyse all the data eventually. Therefore, instilling it bit by bit isn't very effective. Moreover, as you said, there can be hesitant folks clicking everywhere to see what difference it makes or what information is provided, so you will end up sending the same informations multiple times to the client.

If you prefer an event-based system, you can save a lot of queries simply by sending every static informations at once. This point is a lot more critical than the event-based/timer-based system stuff IMHO.

I can't tell you when your SQL server will die because it depends of the size of your data, the complexity of your tables and queries, etc. If you do basic stuff I'm sure a SQL server can handle more than 1000 queries per second, but it doesn't mean anything really.

The timer-based system will reduce the number of requests, even up the server load over time and improve the user expercience because of the instant feedback client-side updates provide (even a fast ajax request takes time to travel back and forth to the server).
Was This Post Helpful? 1
  • +
  • -

#9 Toto-A   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-March 14

Re: Database query - speed question

Posted 21 March 2014 - 10:29 AM

Thank you guys - I will do as you advice me (though it means re-doing plenty of my amateurs code). The thing I am concerned though is possible data loss if window will not closed properly. Now in my previous approach - instant Database change when user changes something it was not an issue. Though I dont think this (windows was not close properly) will happen very often.
Was This Post Helpful? 0
  • +
  • -

#10 ge∅   User is offline

  • D.I.C Lover

Reputation: 195
  • View blog
  • Posts: 1,194
  • Joined: 21-November 13

Re: Database query - speed question

Posted 21 March 2014 - 11:00 AM

If you save the state every 10 seconds you loose only 10 seconds in the worst case scenario. And I'm sure you've already got a "end turn" button which will ensure no data is missing when the user decides to submit his changes.
Was This Post Helpful? 1
  • +
  • -

#11 Toto-A   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-March 14

Re: Database query - speed question

Posted 21 March 2014 - 11:45 AM

Yes, I ve got "end turn" button and thank you again!
Was This Post Helpful? 0
  • +
  • -

#12 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2461
  • View blog
  • Posts: 7,490
  • Joined: 15-January 14

Re: Database query - speed question

Posted 21 March 2014 - 12:37 PM

I like committing data after a certain number of clicks rather than time. If they haven't clicked for a minute, there's no reason to be sending data.
Was This Post Helpful? 0
  • +
  • -

#13 Toto-A   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-March 14

Re: Database query - speed question

Posted 21 March 2014 - 12:45 PM

View PostArtificialSoldier, on 21 March 2014 - 12:37 PM, said:

I like committing data after a certain number of clicks rather than time. If they haven't clicked for a minute, there's no reason to be sending data.

I am planing to have variable which shows if there were any changes that should update Database. When sending data to Database time comes if this variable = 0 then nothing happens, it skips the process till the next sending data time.
Was This Post Helpful? 0
  • +
  • -

#14 ge∅   User is offline

  • D.I.C Lover

Reputation: 195
  • View blog
  • Posts: 1,194
  • Joined: 21-November 13

Re: Database query - speed question

Posted 21 March 2014 - 02:07 PM

The reason why I prefer timers over events is that I don't want to put an additional layer of checking dedicated to data submission in my event functions. I like to keep things separate so I can focus on the logic of my application when I write my usual events.

Of course you can play with event capturing/bubbling to add your layer of checking outside of your event functions but it is something I rarely do, and I like to use event properties because of IE's attachEvent behaviour regarding the `this` keyword.

Both methods are correct to my mind, you can either check the number of clicks when an event fires or check a variable/object every x seconds, the two offer the same level of control, it's just another implementation.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1