Optimize an application with a huge number of database queries per minute

Question

I have to provide free demo some service to end users in my application. Free demos can be 30 mins, 1 hours, 5 hours , etc. ( predefined time ) for a new user only once.

The user can also use this time in parts. for example, after 30 minutes of a free demonstration, they can use it like 10 minutes today, 15 minutes tomorrow and the rest of the time the next day, etc. Now, if the user selects a free demo in 30 minutes and logs into the system and uses the service. I can limit the user to 30 minutes through his start and end times. I can send them to the payment page if the start and end amount is 30 minutes.

Now the problem arises with some vague conditions, for example, if the user closes the browser or their Internet stops working or something else at the end of their active session. In this case, I can’t calculate their consumed time due to lack of end time.

The scenario could be as below (for a 30 minute demo).

 UserID StartTime EndTime Consumed(mins) 10 09-04-2015 10:00 09-04-2015 10:10 10 10 10-04-2015 05:00 10-04-2015 05:04 4 10 11-04-2015 07:46 11-04-2015 07:56 10 10 11-04-2015 10:00 // Browser closed or any uncertain condition 10 11-04-2015 11:00 // How to restrict user to use actual 30 mins because I do not have EndTime in above row to calculate Consumed mins. 

I can have more than 100,000 users at the same time to use our services. Therefore, I find an effective solution for this.

According to my understanding, I can create a separate work for checking the LastActiviteTime user and based on this I can update their Consumption (min.) In the database. This task will be executed every minute, and on the other hand, the browser of each session user will update LastActiveTime in the database.

This may solve my problem, but I'm not very sure about the performance of my application due to the huge number of database queries per minute.

Answer 1

Perhaps you can also check the start time, the end time through the client script using JavaScript and save the start time, the end time in the browser cookie and run the timely script (java script that runs every minute), so if the check is not performed on the side client, you do not need to check it on the server side (database), thus many user queries to db will be reduced.

Answer 2

I would suggest conducting a performance test to evaluate the impact on the database a little more than the expected maximum load. Then, depending on the result, you can use a NoSQL solution, such as RavenDB, to save usage data. NoSQL can work wonders and is great for such problems.

Of course, you can do this with the planned client side script and AJAX, as Rolwin C has already suggested. Just keep in mind that even if there are some obstacles to malicious users, there is always a level of risk associated with this approach. See if this risk is acceptable in your case, as it can significantly reduce the load on the database server.

Answer 3

If a user interacts with your service, use these interaction instances as the last time used, and if you need to determine the time when you do not have a finite time, use the last interaction time as the time to identify the end of the session.

The simplest would be to add another column to the table that you showed as lastInteractionTime. And if there is no end time, use lastInteractionTime to calculate the consumption time.