Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria -- best practices (All versions)

    We chose to roll our own application to track Help Desk trouble tickets. The database is in SQL Server 2000. The original plan was to cobble together a quick Access front end with linked tables as a stop-gap measure until we could develop a web-based GUI. Being the shoe maker
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Criteria -- best practices (All versions)

    I wonder what the speed would be like if you had a special table devoted to the IN values, then join your other table to this table.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Criteria -- best practices (All versions)

    Actually, it looks like what you experienced was the fact that in SQL, NOT is much slower because it has to look at the entire recordset before it can return a list, while EQUALS can use the indexes to return the values that apply. The same applies to IN and NOT IN. In is quite fast, while NOT IN is slow. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> It's harder to prove a negative.

    If I read your post correctly, you would be returning only the items that are Assigned/In Process. Does that include Open as a pre-condition? What about any that haven't gotten to 2 yet? Using < 4 would probably give you better performance that <> 4.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Criteria -- best practices (All versions)

    It should be faster if it would filter using a join rather than a WHERE condition.
    Charlotte

Posting Permissions

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