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