4

We have a situation in which our web application calls our webservice, which than calls a database stored procedure to return data according to customer. There are 8 hundred thousand rows returned by SP and involves JOINs in SP so it takes around 8-10 secs.

We have decided that at web service end we put the 8 hundred thousand rows in Cache and web service will return data from the cache. A background thread will be working alongside and updating the data after 15-20 secs.

I wanted to know some authentic mechanism of back ground processing in ASP.NET 2.0.

Thanks in advance for your help and cooperation.

Imran Balouch
  • 2,170
  • 1
  • 18
  • 37
  • 2
    If you seriously return 8 million rows, than you're doing it awfully wrong. It's a tremendous waste of computing power, because I will never believe you, that you really need to display all those rows. Ever heard of a thing called "paging"? – walther Jun 26 '12 at 10:14
  • Lolz, i just checked that i wrote it 10 times higher, so I have modified it to 8 hundred thousands. :) Yeh I have heard about paging and we will be showing only 25 records in front of user from those 8 hundred thousands. – Imran Balouch Jun 26 '12 at 12:24
  • 8M , *00K, same thing... WAY too much data. if you are only displaying 25 rows at a time, query the database for 25 rows. – Jason Meckley Jun 26 '12 at 12:25
  • there are a hell lot of joins to retrieve the data and even for 25 rows, the Stored procedure is taking like 8-10 seconds. DB guys has optimized the queries and indexes but have brought the time to 8-10 secs. – Imran Balouch Jun 26 '12 at 12:29
  • Querying 25 rows of data from sql server CAN'T take 10 seconds! Hire a new db guy, because this is seriously insane. You've got some horrible stuff going on in there. – walther Jun 26 '12 at 13:19
  • Btw, paging doesn't mean "let's go query 800 000 rows and filter it on a client"!!! It means the filtering should have been already done in the sql query and using the client just as a display device, NOT responsible for paging itself. – walther Jun 26 '12 at 13:21

4 Answers4

0

You can use an HttpModule to spin off a background thread and cache the data. Here's a blog post that shows how to do just what you are suggesting.

Kevin Babcock
  • 10,187
  • 19
  • 69
  • 89
0

You can easily do it by creating A thread in Golbal.asx (Application_Start Event) and that thread does the work for you. Also there are some HTTPModule based implementation and some Cache based implementation for the task which you want to achieve.

But I don't recommend any of above because they all stops working when Application pool recycles due to IDLE timeout (or any other reason). Here are the details of all those problems:-

http://haacked.com/archive/2011/10/16/the-dangers-of-implementing-recurring-background-tasks-in-asp-net.aspx

What I recommend is :- 1- create a windows service 2- create a simple console application and add it in scheduled tasks in windows 3- Create a job in database which will execute after certain frequency.

For details please have a look at:-

http://zeeshanumardotnet.blogspot.com/2010/01/how-to-send-periodictimely-emails.html

Zeeshan Umar
  • 502
  • 2
  • 6
  • 12
0

No background processing is going to help here. Hire a new db guy and be sure he actually understands paging of data. There's no way that querying 25 rows of data could take 10seconds, that's simply insane.

walther
  • 13,466
  • 5
  • 41
  • 67
0

I have implemented it and a another question of mine is showing its implementation. ASP.NET Background thread Performance Guidance

Community
  • 1
  • 1
Imran Balouch
  • 2,170
  • 1
  • 18
  • 37