"Lookup" tables in data cache


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: "Lookup" tables in data cache

  1. #1
    David Kightlinger Guest

    "Lookup" tables in data cache


    We have a Visual basic program that is run against thousands of new records
    coming into our system each day. The program performs hundreds of edits
    on the data to insure quality control for statistically analysis. We use
    hundreds of tables that contain the valid values (primary keys) for each
    data field in the record so that we can dynamically modify these table values
    when federal or state guidelines change. For example; diagnosis codes, service
    codes, etc. The VB program checks each field for these valid values by performing
    a select statement against the table for that field value which is a primary
    key in the table.
    The question is, Can I insure or force SQL Server to "Load" all these "Lookup"
    tables in data cache or some form of virtual disk in memory so that these
    multiple data checks against the tables are accessed in memory (for speed)
    instead of from the tables on disk. In addition, if you or the technical
    staff have any other suggestions as to how to improve on the speed of doing
    table lookups, I would appreciate it. Please realize though that we would
    prefer to keep these values in tables so that when they change it does not
    involve program modifications.
    Any help in this matter is greatly appreciated...Thanks... David
    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: "Lookup" tables in data cache

    If you really are accessing this data all the time, then the natural order
    of things will cause SQL Server to keep most if not all of the pages for
    that table in memory all the time (assuming there is enough memory).
    Basically, SQL Server uses a most recently used model for figuring out what
    to cache. If you're pounding that table all the time, then there's a good
    chance that the pages from that table will naturally be in memory.

    That said, if you really want to force the issue you can "pin" the table
    into memory. This is done using the DBCC PINTABLE() command. It accepts the
    database id and the object id for the table as arguments.

    Keep in mind that, by pinning the contents of that table in memory, your are
    also ensuring that the memory used by that table will not be available for
    other SQL Server caching needs. SQL Server is usually pretty darn smart
    about what the right thing to have in memory is. The primary risk is that a
    particularly large query will wind up pushing some of your desired table's
    pages out of cache. Keep in mine, however, that, even if that happens, it
    was probably so you could get that rather large query done faster and get it
    over with.


    --
    Rob Vieira
    Visit www.ProfessionalSQL.com Today. It's new and improved!

    "David Kightlinger" <DKightlinger@daodas.state.sc.us> wrote in message
    news:3a6d8c79$1@news.devx.com...
    >
    > We have a Visual basic program that is run against thousands of new

    records
    > coming into our system each day. The program performs hundreds of edits
    > on the data to insure quality control for statistically analysis. We use
    > hundreds of tables that contain the valid values (primary keys) for each
    > data field in the record so that we can dynamically modify these table

    values
    > when federal or state guidelines change. For example; diagnosis codes,

    service
    > codes, etc. The VB program checks each field for these valid values by

    performing
    > a select statement against the table for that field value which is a

    primary
    > key in the table.
    > The question is, Can I insure or force SQL Server to "Load" all these

    "Lookup"
    > tables in data cache or some form of virtual disk in memory so that these
    > multiple data checks against the tables are accessed in memory (for speed)
    > instead of from the tables on disk. In addition, if you or the technical
    > staff have any other suggestions as to how to improve on the speed of

    doing
    > table lookups, I would appreciate it. Please realize though that we would
    > prefer to keep these values in tables so that when they change it does not
    > involve program modifications.
    > Any help in this matter is greatly appreciated...Thanks... David



    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center