Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Ignore Nulls (Access (All))

    This may be insignificant, but some us dinosaurs still remember when you had to do everything possible to improve disk performance (not so much an issue now), and old habits are hard to break.

    Specifically, this is about the IgnoreNulls property on indexes. I generally keep it set to false, but I'm wondering if it should be set to True sometimes. Access Help mentions setting this to True if there are alot of null entries in a table as it will reduce the index size. This generally is a good thing, and usually will help performance.

    However, I'm thinking that it should NOT be set to True if you often use "Is Null" as a selection criteria, or if you are often sorting on this field and expect Nulls to be grouped together. It seems that in either of those situations, Access couldn't use the available index (if IgnoreNulls was True), and would have to look at all records. (I'm assuming, or perhaps hoping, that if you use "Is Not Null", then Access would be smart enough to use the Index).

    Any thoughts on this?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ignore Nulls (Access (All))

    We would probably need to have someone who knows how Access is programmed internally to give a definitive answer. My guess would be that if there is a difference, it would only be noticeable when working with large tables (hundreds of thousands of records or more).

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Ignore Nulls (Access (All))

    >>it would only be noticeable when working with large tables (hundreds of thousands of records or more).<<

    That was my guess, but I was curious if anyone had any more knowledge. Like I said, old habits are hard to break. I remember a time when we timed records processing in # of seconds per record! So shaving a tenth of a second here and there was big news!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •