Optimizing Your Application - Part 2

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnis

  • As an application designer I should not have to concern myself with caching data for the purpose of improving performance.

    If caching data I need to write code that determines when the cache needs to be updated.

    As an application developer I am more concerned about developing business logic that helps users perform there jobs with greater efficiency and ease.

    Providing good database performance should be the domain of the developers of the DBMS and the database administrators.


  • You're not concerned with performance? I bet your users are and if they don't get reasonable performance then they can't work with "greater efficiency and ease" can they?

    Eliminating server round trips is especially important in stateless environments because new connections often need to be made to the server and this is particularly expensive. If you're using .NET, an easier item to cache though would be a dataset or datatable (Preferably typed).

    That said, Keith does make a good point. Caching should not be done unless you actually need to optimize performance. In this economy, throwing hardware at it is not the solution and developers and DBA's need to work together on optimizing DB operations. That is why Microsoft made SQL Server easier to manage than many other DBMS's, so they can spend more time with developers designing databases - thatis where the performance benedits come in; up front.


    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • Using a HashTable might not be a good idea. HashTable orders its items by internal hashcode, so if you're expecting items to be in a particular order (e.g. either sorted or in the order that you put them into the collection), you might be in for a nasty surprise.

    Why not just use a DataSet instead?

  • Bryant, I did not say that I was not concerned with performance!


    I think caching is only useful if you have data that is relatively static. I think that other solutions should be consideration before deciding to cache anything.


  • I like hashtables (and use them when I need quick 1-1 lookups), but for UI elements or other sequential-access type things, why not just keep the dataset/datatable around as long as you need it?

    If you don't need really speedy data access and the dataset you have is small, you can even do a select/filter on the dataset to narrow the set you have without going back to the database.  OTOH, I found in a set of 10K records that it was much faster to ask the database to query and narrow for me than it was to use the dataset.

    -Thor Johnson

  • No doubt you can increase performance of the application by eliminating round-trips and re-querying. I have much experience with this type of application and I have applied this scenario not only with small amount of data but large amount of data. But think twice weather the data should be cached or not because it depend on various things such as application type, data type (how often data is modified) and memory.


    Why I used HashTable for this is: it is a dictionary type collection that is optimized for fast retrieval. I do not think that DataSet is really applicable for this operation.




  • The Dataset class is designed to cache data.  Why create another structure to hold multiple datasets each with one table, when you can create one dataset with multiple tables? 


Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply