Solved

ASP.net And SQL Preformance Tuning

Posted on 2010-08-23
8
482 Views
Last Modified: 2012-05-10
I have recently developed a pretty comprehensive Inventory application for my company. Today I loaded up SQL Server Profiler for the first time and started to play around in the application to see what and how many queries are really being ran. One section of my application utilizes a gridview that selects from 5 different tables to pull its information in. I notced that on every postback (paging, sorting, etc) there are my Union queryies ran again. Login, Query, Logout 5 times! This is okay for now and I am on good hardware and a gigabit link between my IIS and SQL...BUT..in terms of scalability and long term preformance I don't think this is the most Ideal solution...especially to grab the same data OVER AND OVER again. Is there a way to utilize the cache so a SQL query only gets executed once every session and then pulls from the cache or something similar? Is this already the 'best' option? Maybe I am underestimating SQL but if there are a few thousand products getting queiried 5x by a handfull of people...my application is going to slow WAY down.

Also I used a new design method that worked really well for me...I don't know that it is recomended though. I included about a dozen user controls (panels with different forms and gridviews/details views) in my default.aspx. From there I control the whole application in one AJAX Update panel and use a hideall funciton that enabled=false and visible=false to every panel..then enable the panel that I want to work with dependant on what button or funciton is being pressed/preformed. ALOT of my panels include sqldatasources and I noticed some of them are running select commands on every postback as well...this is not 'optimal' in my eyes. Any ideas to rectify these issues WITHOUT redesigning my whole application?

Any and all tips/hints/solutions are much appreciated!
0
Comment
Question by:Knance
8 Comments
 
LVL 10

Expert Comment

by:eguilherme
Comment Utility
hmm, well what i suggest you to do is:

-remove all panels,and use usercontrols / placeholders (is it a intranet application ? otherwise having just one page with a updatepanel might be a bad ideia)
-change your sqldatasource to a proper dal layer so you can control better what calls what..


anyway for start you should make the 1st step.. read about usercontrols / placeholders...
0
 

Author Comment

by:Knance
Comment Utility
Yes this is an intranet application. Panels are simpily <DIVs> with a bit more control, I just use them to show/hide sections of the application in a seamless manner. I like the way that this functions as there is never a full reload of the application.  I understand usercontrols quite well and do utilize them in this applicaiton many times.

As far as the Panel VS Placeholder issue I prefer to use Panels so I can utilize CSS with them as they produce the DIV Tag where as the placeholder does not.  

I use SQLDataSource in many applications and love the control they provide me as well as the simplicity. What is the benifit of a "proper dal layer."  I utilize Paging, Sorting, Inserting and Editing in MANY places so to recode/redesign the application to get the same results would be rather cumbersome and time consuming. Unless there are VAST improvments between the two options I don't see this as a viable solution either.

Thank you for the tips but unless there is that much of a difference in the SQl Data Source and a custom Data Access Layer  I dont think these will help me much.
0
 
LVL 3

Assisted Solution

by:Aidiakapi
Aidiakapi earned 500 total points
Comment Utility
It's normal that you validate the user's login data on every page, also on postbacks, that something that u attend to do in most server languages, but to save a lot of queries already I can tell you that a good way to avoid that is by using session data to store stuff.
Session's are quite secure, but what I always do is store a few keys in the session that can be verified against some cookies, this is to avoid people trying to guess the Session ID of someone else.

About the AJAX update panel, well, I wouldn't do that, because that increases the size of a postback a lot. I'd make conditional updatepanels around a few controls to enable AJAX. (Conditional is a setting of the update panel, it forces you to call *UPanel ID*.Update() before it actually updates.)

ASP.Net has a function which is automatically on to check postback data. Which makes postbacks save and secure. They can't really be messed with, so for checking some database stuff I'd at least add: if(!IsPostBack) Methods.CheckLogin(); or something like that.

Another way of keeping the amount of queries to a minimum is by saving commonly grabbed data into a Session. Session's can store any datatype (tough be aware that Anonymous types can't be casted back, and that with private classes you also get issues, which means that you can only use sessions with public classes), and that makes them useful, you can simply store the entire query results there and use them on every page.

You design sounds very complex to me, even so complex that it might slow everything down.
In my opinion you don't use user controls that often, because they mostly don't have anything a good masterpage can't give. There are a few exceptions on this, but I really recommend you to create a really good masterpage and use that to improve speed.

Good luck with speeding it up :)
0
 
LVL 28

Expert Comment

by:sybe
Comment Utility
Move some functionality to the client.

Webforms is a horrible way of creating webpages. Things that can easily be solved on the client, such as hiding/displaying a div, in webforms need a roundtrip to the server.

User Controls are a difficult way to create HTML. It's better to control the HTML directly in stead of using server-side controls.

AJAX update panels are a highly inefficient way to use AJAX. They replace the complete "panel" (all html) in stead of just the data that needs to be replaced.

In always wondered if "hidden" panels would create a database query too, even if the result isn't displayed at all. It sounds like they do. So your code is querying the database for results that are not going to be send to the browser. There must be a more efficient way...

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Knance
Comment Utility
No, the enabled=false makes it so the SQLDataSources dont run the Select statement again. The issue lies within the postbacks of the gridviews here. As I was saying I have one gridview in particular that pulls from 5 tables using a Union Select Query to do so.  On each subsiquent postback the SQLDatasource runs the Connection string, Selects from Table1, Closes Connection, runs connection string selects from table 2, closes conneciton and so on until all 5 are processed.  

The panels and ajax is not the issue here at all really. I am noting and researching Masterpages and some conditional update panels for use in any new projects. The main issue here is the actual number of Queries being ran by the gridview itself. This may be due to the use of an update panel or just may be standard gridview working with a SQL Data source. I was just hoping to reduce the total number of SQL queries in my application.

@Aidiakapi Thank you for the insight on these issues, I would however like to note that I preform custom user authentication via Active Directory on each pageload. It is the Gridview which is authenticating against the SQL DB on each load/postback that is what concerns me.

I think that a Master Page is going to be the route for me on my next design or update of this project. This is my first 'large' scale ASP.net application and coming from a PHP programming background the panels/User Controls seemed very nice and easy to use at design time so I ran with it :).


@Sybe. I don't have a ton of just HTML elements being recreated. 95% of the content in my panels are gridview/detailsview/dropddownlists  so I am not recreating much more than is needed as these are all server side controls bound to datasources.

While normally for hiding/displaying a div I would use a client side method this is not viable in this project for a few reasons. The main reason being that I not only visible=false but also enabled=false so as not to have the objects load every postback and also so the Datasources dont select again and again when its not needed. The webforms are a viable solution here because it requires a roundtrip anyways, be it to go to a whole seperate page or to disable one of the panels it is all the same in this particular design.

Again to anyone following this the issue to me right now isnt the panels/ajax/usercontrols it is the gridview and ammount of SQL Queries being ran. :)
0
 
LVL 3

Accepted Solution

by:
Aidiakapi earned 500 total points
Comment Utility
I never used the GridView control. But this quote from a website seems to me like a solution for your problem:

Paging

It is known that to display data from a given data source you have to use data controls like: GridView, and/or DetailsView controls and to bind them to the data source you want to display data from. You may want to adjust the display layout of some data records and columns within your screen by using the GridView, or the DetailsView paging property and set it to true. This paging technique is called UI paging (User Interface paging). To perform this service, the GridView control requests data from the specified data source, it then filters the elements that will be displayed in the current page and simply ignores the rest. It does this process each time a new page is required ... which is certainly a great waste of resources.

There is another technique for doing the same task, it is typically referred to as data source paging. In this technique you make use of a data source control that implements the DataSourceView class, and set its CanPage property to true, then you feed the GridView control or the DetailsView control from this data source. In this case the data source object queries only for the elements needed in each page. Thus, data source paging is more efficient than UI paging. The only data source class that allows data source paging is ObjectDataSource class.
0
 

Author Comment

by:Knance
Comment Utility
:) Thanks for the reply that helped a lot and got my gears turning. I dont really wish to change all of my sqldatasources into ObjectDatasources as that would be quite the undertaking. I decided to override the SQLDataSource and enable a similar feature. Lucky me I don't have to reinvent the wheel.

http://forums.asp.net/t/1123268.aspx

A lot of the leg work has been done for me it seems :).

Again thanks for the point in the right direction.

I am still open to any other ideas/solutions to anyone else following this.
0
 
LVL 3

Expert Comment

by:Aidiakapi
Comment Utility
Thanks a bunch, and an extra thanks for activating my free account :).
Finally got enough points :)

Good luck with you're application.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now