Knance
asked on
ASP.net And SQL Preformance Tuning
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!
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!
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER
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/dropd
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. :)
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/dropd
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. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
:) 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.
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.
Thanks a bunch, and an extra thanks for activating my free account :).
Finally got enough points :)
Good luck with you're application.
Finally got enough points :)
Good luck with you're application.
-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...