News website which contains different sections like TOP NEWS,BREAKING NEWS, LOCAL NEWS, MOVIE etc. All this sections are loaded with data

Question

1.00/5 (1 vote)

See more:

I have a News website which contains different sections like TOP NEWS,BREAKING NEWS, LOCAL NEWS, MOVIE etc. All this sections are loaded with data at the time of page loading by a serial execution of many sql queries one by one. Each section get data from different tables by executing corresponding sql query; so they all in serial form. But by using this approach my website loading time is very high in order to complete all this queries. Is any way to execute all this queries parallel and fill website section with data parallel at a time. so that by a single execution cycle time I need to complete the process instead to wait all the serial execution cycles.

Posted 2-Feb-16 1:58am

satpal 2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Answer 1 · 2016-02-02T02:22:00

Solution 1

I recommend to change your approach. Instead of querying all this for each pageload have a separate process which runs in regular intervals (like 15 minutes or so) in order to prepare all this ahead of time and saves it into a separate table (or separate tables) from which your ASP application reads when a page is requested.

Edit - more detailed after comment:
1) Build an application (best would be a Windows Service but could be a console application) that does all the preparational work: Query the news tables, arrange everything as required and save it into separate tables so that your ASP application basically just has to transform the contents of these prepared tables into HTML on a page request.
2) Run that table-preparation in intervals that depend on how often the news source data changes. Maybe every 15 minutes or so.
3) Your ASP application doesn't have to wait at all on a page request: It just displays what the prepared tables contain from the last preparation-run.

So, unless you currently have less than one page-request per 15 minutes, this should reduce database usage. In any case it will speed up the response time of your ASP app.

Posted 2-Feb-16 2:22am

Sascha Lefèvre

Updated 2-Feb-16 3:38am

v2

Comments

satpal 2 2-Feb-16 9:11am

thank fro reply Sascha Lefèvre!

if i post news and want to display on my default page at once that news than i have to wait for 15 minute for separate process to fill table ?? except this if this separate process hit on every 15 minute(or any short interval eg;30 second, 1 min)this will be headache for sql server and make it busy unecessary??

Sascha Lefèvre 2-Feb-16 9:39am

I think you misread: Not every 15 seconds but every 15 minutes or so. I wrote my idea a bit more in detail, please take a look (above).

satpal 2 3-Feb-16 0:55am

yes it is misread.I want to say that 15 minute please take a look (above).

Sascha Lefèvre 3-Feb-16 5:00am

I'm a bit confused where above I should look and why? :)
Did you see what I added to my answer?

satpal 2 3-Feb-16 5:54am

Sascha Lefèvre i editied my question..i am not interested in making service that burden server every 15 min or any other time interval..so any alternative for this

Sascha Lefèvre 3-Feb-16 6:10am

How many page requests does your website get in one hour?

satpal 2 5-Feb-16 1:44am

600-800 request per hour

Sascha Lefèvre 5-Feb-16 8:00am

Ok. Let's say that's 200 requests per 15 minutes. So currently your code executes those expensive queries 200 times per 15 minutes.

What I'm proposing: A separate process that runs once per 15 minutes to execute the same queries and then saves a prepared result in a single table. The preparation should be that way that on a page request only a dead simple select-query is required to read from that table.

So then instead of 200 expensive queries per 15 minutes you would have 1 expensive query and 200 very fast queries. That would be less of a burden for your database and quicker response times.