| If you read the internet and all of the websites | | | | which then in turn needs to be looped through to |
| dedicated to Asp.Net you will inevitably read about | | | | obtain your data. So instead of writing your stored |
| the wonders of the DataGrid, DataList, and Repeater | | | | procedure like the following which brings back 1 result |
| controls. While each of these has its place, if you are | | | | set:Select Title, Body, AuthorFrom ArticlesWhere |
| only displaying data there is a much faster and more | | | | ArtID = 215We can write it using a set based |
| efficient means to do so.Let's say you have a page | | | | command like this.Create Procedure |
| that displays articles based on a query string. Take | | | | mysp_GetArticle@Title varchar(200) Output,@Body |
| my article pages for instance. Each article is stored in | | | | varchar(8000) Output,@Author varchar(500) |
| a database and displayed on the page based on the | | | | OutputAsSelect @Title = Title, @Body = Body, |
| unique id of the article as stored in the database.A | | | | @Author = AuthorFrom ArticlesWhere ArtID = |
| normal asp page execution procedure goes | | | | 215GOThe above query will return only the three |
| something like this. The code queries the database | | | | parameters called for and not a result or record set |
| based on the Article I.D. and then brings back that | | | | so you don't have to then walk through the returned |
| information to the page where you display it in the | | | | record set that has only 1 result in it anyway. This |
| fashion that you would like. This is a fairly straight | | | | second little process of work decreases your |
| forward approach with asp and is done all the time.So | | | | performance so you should avoid it whenever |
| how do we speed up our asp.net pages?Number 1: | | | | possible. Combine this technique with the asp.net |
| Use Asp.Net Caching!This is a no-brainer, and I won't | | | | cache.Number 4: Use Classes and ArrayLists as |
| go into the brilliance or details of asp.net caching here | | | | opposed to returning an SqlDataReader.Create a class |
| because at the time of this writing Google has | | | | and then if there are more than one set of results |
| 2,780,000 articles on the topic. Basically instead of | | | | store those results into individual instantiations of that |
| querying the database each time the page is loaded | | | | class. Finally store each of those classes into an |
| you only query the database once and load that | | | | ArrayList. You can then store only that ArrayList into |
| result into the system cache. Subsequent calls to load | | | | the asp.net cache. So instead of getting the results |
| the page retrieve the data from the cache as | | | | back from a SqlDataReader when loading your page |
| opposed to the database which gives you an instant | | | | you get them from the ArrayList which is stored in |
| and considerable performance boost. You can then | | | | the cache. Nice huh?Finally... you want to incorporate |
| set the cache for how long the cache should store | | | | all of these techniques into your final results which |
| the information as well as many other features. If | | | | would be performed in the following manner and |
| you are not using the cache, you should be | | | | sequence.On the first time the page loads, query the |
| whenever possible!Number 2: If possible, do NOT use | | | | database and return all of your data storing it into |
| the standard Asp.Net controls.That's right. The | | | | individual classes. Then store each of those classes |
| standard asp.net controls are designed for rapid | | | | into an ArrayList. If you only have one single result |
| development and not page performance. They allow | | | | you may store only the class into the cache. Then |
| you to design pages that grab and display data very | | | | take your ArrayList and store it into the cache.Next |
| quickly but their actual performance suffers because | | | | create a Web Custom Control and pass the cached |
| of the extra overhead which is there for ease and | | | | ArrayList to the custom control and loop out your |
| speed of development time and not page execution | | | | data using the HtmlTextWriter which is very fast. |
| speed.Instead, create either a User Control or even | | | | Remember each subsequent call to load the page will |
| better yet a Web Custom Control which is by far | | | | be called from the cache which stores your ArraList |
| the fastest performance wise and really quite easy | | | | of classes or your single class.Certainly it takes a |
| to create and use.Number 3: Use an SqlDataReader | | | | significant amount of additional coding to do it in this |
| or even better yet use a set based command for | | | | fashion, especially when you take proper error |
| Sql Server data retrieval and simply execute that one | | | | handling into consideration, but if you follow this |
| command against the database.An asp.net | | | | approach your pages will be screeching fast, you will |
| SqlDataReader is a fast forward only datareader that | | | | immediately notice the difference, and your asp.net |
| closes the connection after it reads the last set of | | | | pages will execute in the proper sequence - Data |
| results. Now for my article pages we are only | | | | handling in the Page_Load function and the html |
| returning 1 particular result. In this case we would opt | | | | display in the Page_Render function.Further, you will |
| for the set based command. If you had more than 1 | | | | be glad you did and so will your visitors.Happy |
| result returned, in your table of contents for | | | | Programming!John Belthoff is an avid web developer |
| instance, you would use the SqlDataReader because | | | | who writes about Asp.Net in his spare time. He owns |
| you are returning multiple sets of results.Set based | | | | a Windows Asp.Net, Asp Web Hosting Company |
| commands are stored procedures that bring back | | | | where you can contact him about hosting your |
| data through parameters as opposed to a result set | | | | website/blog or just to learn more. |