2

I'm not sure if this is a thing. I'm sorry about the ambiguous title--I wasn't really sure how to explain it. Basically I have a ASP.NET web page that runs two SQL queries and does some logic to produce a status page for our delivery department. It refreshes / updates every 30 seconds, therefore the database is being queried twice on every client refresh. Well, more and more people are using this application therefore there are more and more queries / minute. Also, it is running against our main database and I can see it causing slowdowns as more these custom web apps are created.

What I'm considering is making a console application and running it as a service on separate server that does the querying / logic every 10 seconds (less than the amount of refreshes being done by multiple clients). The service will update a table which is separate from our main database. Then the client will just have to read that table and format the output instead of doing the main logic.

Thoughts? Other ideas?

Edit: Execution times (happens up to 30 times / minute):

(1605 row(s) affected)
   SQL Server Execution Times:
   CPU time = 1123 ms,  elapsed time = 1199 ms.
(78 row(s) affected)
   SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

Also happening on the main database which hundreds use throughout the day. There hasn't been a significant slowdown, but I'm looking to future-proof. Although I did heavily slow it down when I was sending ~300 queries a minute once for testing. "Sorry!"

  • 1
    How about caching the result of your query within the web application, so the DB only gets hit once every 30 seconds no matter how many users want to see the dashboard? – Max Sorin Jun 1 '16 at 13:36
  • Writing a service just for this seems like overkill. Could you not just create a SQL Server agent job (or whatever the equivalent is on your RDBMS)? – Robbie Dee Jun 1 '16 at 13:37
  • Why would it query twice on every refresh? Wouldn't it query once when you do the refresh, then again 30 second later, and again 30 seconds after that, etc? – Jay Jun 1 '16 at 13:54
  • Max, I'm not really sure about caching because I haven't done it before. What exactly do you mean? Robbie, I'll look into a SQL server agent job--that also seems like a good solution, as well. Jay, I need to look at two different tables which can't be joined in the context of what I'm doing. For context, it's giving an overall status of a delivery driver based on their orders. I have to load all the orders for the day and look at them per delivery route and determine an overall status for that route. Then I have to compare the route to their delivery handheld info to do some more logic. – justiceorjustus Jun 1 '16 at 14:02
1

How much work is it to derive the data to display? If it's a fairly simple query, than re-doing it every time may be little more work than doing it once, saving the results somewhere else in the DB, and then drawing from there.

Do all users see the same data? I mean, are there any parameters on this screen, or is the display the same for everyone? If there are options available, then you'd have to store more than one set of data, fewer users would use each set, and any benefit diminishes.

If performance is really an issue, you could store the results of the query in memory rather than on the DB. In asp.net there's a thing called "application data", data that is shared by all users of the application. You could create an object to hold the data, time stamp it, and store it in application data. Then when the page needs it, it checks if there's anything there and if the time stamp indicates it's still fresh. If so, use the value from application data. Otherwise run the query and create the object.

I did something like this in an app of mine: we read a bunch of data off the DB that changes very rarely, like once every few weeks, but is accessed by thousands of users per day. So I read it from the DB and cache it in memory. In my case, I don't use a time stamp, but rather the application that updates the data sends a message to the application that displays it telling it to flush the cache when data is updated, but similar idea.

Code would be something like (I haven't done C# in a while -- excuse me if I don't have the syntax quite right):

StatusData sd=Application("StatusData");
if (sd==null || sd.timestamp < now().AddSeconds(-10)) {
  sd=new StatusData();
  sd.timestamp=now();
  Application("StatusData")=sd;
}
... whether the IF was true or false, you now have a valid StatusData object ...

(Note that this code is potentially flawed in that if a user hits this logic and starts building a new StatusData object, and before it's done another user hits this logic, you'll build another one. The code should work, as it doesn't put a new StatusData object in application data until it's successfully built, but there could be some wasted duplication. You can beat that by creating a Mutex or various other techniques for forcing it to single thread; I didn't want to get into another layer of complexity. Whether it matters depends on how likely that is to happen.)

All this assumes, of course, that the work required to do the query to build the status page is significant and happens often enough to matter.

  • I would say the queries are significant enough for me to consider doing something differently. Here are the stats: (1605 row(s) affected) SQL Server Execution Times: CPU time = 1123 ms, elapsed time = 1199 ms. | (78 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 4 ms. Basically I'm caught up on the parameters thing, too. It's HEAVILY used for "today's date" and is rarely used for looking at the past and future, although it is an option. The data for "today" is updated heavily, while yesterday stays the same mostly and tomorrow+ changes often. – justiceorjustus Jun 1 '16 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.