ASPX Load controls from SQLDataSources CONCURRENTLY

views:

answers:

ASPX Load controls from SQLDataSources CONCURRENTLY

I have many controls on my aspx page (DropDownLists) each bound to a SQLDataSource.

They stored proc takes a long time to run. I have been working with the DBAs for weeks to try to speed them up. We got it down to about 1-2 seconds for the slowest ones. However loading eight of these one a page is too long for a user to wait!

So, I would like to know if there is a way to get these controls to load concurrently, rather than consecutivly?

I ran 32 of these slow procs in SSMS and it took 38 seconds. I ran 4 sets of these 32 procs at the same time in SSMS and they took 42-43 seconds. so it went from 1.19 sec per proc to .34 sec per proc. I would like to use these same methidoligoy in my aspx.net web page.

SUMMARY: Can I force the sql data sources to load concurrently rather than consecutivly.

THANKS!

EDIT: Could I maybe call DataBind manually in my C# code rather than having it do it automatically. I could then start a new thred for each call to DataBind... Might this work?

+1 A:

It seems to me that caching might be a better solution here. Also, using multiple result sets in the same procedure.

Joel Coehoorn 2010-06-08 14:43:05

can't cashe because the data being loaded changes. Everytime the user selects an option from a dropdown the page posts and every sql data source for the drop downs calls a proc that looks at the selected value of every other drop down. It's complicated. The short story is that it takes a long time. I'm just wondering if its possable to force .net not to wait for one sql data source to load before it loads the next one...

kralco626 2010-06-08 14:48:34

@kralco626 - That sounds like you could use ViewState to avoid re-calling those stored procedures on every postback. Really, however you slice it the best solution here is to reduce the number of trips to the DB in the first place.

Joel Coehoorn 2010-06-08 14:56:01

@Joel - Agreed. But there is really no way to limit the number of trips to the database without reducing the functionality of the website.

kralco626 2010-06-08 15:10:18

@Joel - And I must call the stored procs because the data returned WILL change EVERY time.

kralco626 2010-06-08 15:14:42

You could use jquery / ajax / httphandlers (or methods marked as [webmethod] on your page) / json for loading dropdown combos information. With this, you could query in parallel and you would have a much more responsive interface.

You will have more development work, but better performance and better control over interface.

Claudio Redi 2010-06-08 14:57:32

aka I could do the work manually and call the database concurrently? I had thought of this and was really hoping not to have to do the extra work. Can I like call databind() in my c# code rather than it doing it automatically? And then open up a new thred for each time I call databind()???

kralco626 2010-06-08 15:11:57

Absolutely you could use multithreading too, extra development work too :-)

Claudio Redi 2010-06-08 15:16:33

Hum maybe I will try that. I could assign the control a SqlDataSource in C# rather than in the aspx and then have a method that calls all the Control.DataSource methods in seperate threds...I'll try and let you know...

kralco626 2010-06-08 15:53:01

+1 A:

You should look at asynchronous pages or async ADO.NET: http://msdn.microsoft.com/en-us/magazine/cc163725.aspx

Cade Roux 2010-06-08 16:15:37

This seems to me to be a great solution to too many users accessing the website at the same time. However, It seems to me that even though the thred is returned to the thred pool while the data is fetched the webpage will not create a new thred and fetch the data for the next control untill the previous data fetch is completed. It allows the thred to be used for other users loading a page, but it does not seem to allow the page to load the data for more than one control on the same page asynchronously... I could be interpreting the article incorrectly though...

kralco626 2010-06-09 17:24:44

Well I read the first 9/10 of the article and not the last 1/10 were it quickly mentioned that: "The primary advantage of RegisterAsyncTask is that it allows asynchronous pages to fire off multiple asynchronous calls and delay rendering until all the calls have completed." I don't know if this is what I want or not, but maybe ill give it all try...

kralco626 2010-06-09 17:30:52

@kralco626 - Yes, you want to fire off all the requests and then handle them as they complete. I haven't tried this in ASP.NET (or automatic databinding in any environment), but saw an extensive demo of it at TechEd in 2007. I currently use async ADO.NET extensively in a WinForms application (completely manual control binding), however, and it's relatively easy to use - and performs great.

Cade Roux 2010-06-10 01:12:35

@CadeRoux - How is the database call itself in your project? Are the database calls slow, so you run them concurrntly to speed them up? Or are the database calls themself fast? My database calls take up to three seconds EACH! Although all this may not be nessisary because I am experimenting with Indexed Views, which may solve the problem on the database end. I'll let everyone know what ends up working.

kralco626 2010-06-14 16:08:05

@kralco626 Some of my calls are instant and some take several minutes (a full system diagnostic SP which populates multiple tabs with various warnings and errors) - my Winforms UI is still responsive because I handle the calls as they complete (not so easy in ASP.NET because of page lifecycle, but at least you'd be able to have all calls running simultaneously). I use SqlCommand.BeginExecuteReader instead of ExecuteReader and the delegates handle the processing upon completion.

Cade Roux 2010-06-14 18:08:06

@kralco626 Also have a look at http://msdn.microsoft.com/en-us/library/yws6262h.aspx

Cade Roux 2010-06-14 18:14:00

@kralco626 I'd just like to add that speeding up the DB is always great, but if you do have a lot of separate requests and you can't make a single SP to return them all quickly, it can often be quicker to run them in parallel, and there is no simple way in straight T-SQL to get separate resultsets running in parallel.

Cade Roux 2010-06-14 18:15:25

Well I found a way to speed up the queries to run almost instantly using indexed views. However, there are more rules than could ever be imagined in creating indexed views. So the proccess is taking longer than expected. If i can get it to work it WILL speed up the querys to run almost instantly (I know this because i tested it with a lookup table rather than a lookup view), however the table I would have to update manual, the view's index gets updated automatically. Anyways I'll take a look at the article that you mentioned.

kralco626 2010-06-15 14:27:54

ansaurus

tags:

views:

answers:

ASPX Load controls from SQLDataSources CONCURRENTLY

related questions