ASP.net And SQL Preformance Tuning
Posted on 2010-08-23
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!