Criteria — best practices (All versions)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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. 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.

Reply | Quote

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. 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.

Reply | Quote