Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Location
    Perth, Western Australia
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Null conditions (Access 97)

    Does anyone know if it is a know Access performance issue when a query has a condition "Is Null or 0". I have found that this type of condition runs painfully slow. Unfortunately I'm linking to another database over which I have no control. That database design included a default of 0 for a particular field; sometines the user has deleted it and sometimes not, leading to inconsistent data - yuk!
    Any ideas?

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

    Re: Null conditions (Access 97)

    Is this field indexed? If so, is it indexed to "ignore Nulls"? If that is the case, then when you specifiy "Is NULL", Access would not use the index and have to read every record in the table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Oct 2001
    Location
    Perth, Western Australia
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null conditions (Access 97)

    Thanks, Mark but it seems the field is not indexed at all. Does this mean that Access has to do a table scan of every row? and is that why it runs so slow?

    Regards
    Dean

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

    Re: Null conditions (Access 97)

    you wrote:
    >>but it seems the field is not indexed at all. Does this mean that Access has to do a table scan of every row? and is that why it runs so slow?<<

    Pretty much so, unless there is a selection critieria on another field that happens to be indexed. Access can then use that index to reduce the overall number of reads. If your original field is frequently used as a selection criteria or a report grouping/sorting field, then try to index it.
    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
  •