Solved

ASP.net And SQL Preformance Tuning

Posted on 2010-08-23
8
495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 10

Expert Comment

by:eguilherme
ID: 33500659
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
ID: 33501056
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
ID: 33501421
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 28

Expert Comment

by:sybe
ID: 33501762
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
 

Author Comment

by:Knance
ID: 33502110
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
ID: 33502174
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
ID: 33502300
:) 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
ID: 33503920
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 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