I am finding the performance of lookup tables to be much slower than I expected since the data sets we use here are relatively small in business processes. We don't necessarily expect SQL Server performance, but I would like to see improved performance in the hundreds and thousands of rows.

Once you get to 100 rows, simple queries can take up to 1 minute.

For example a table that holds contact information with 5 columns has just over 100 rows

A rule that clears contacts based on contact name, now takes 56 seconds to finish

The rule just has a WHERE clause with 1 column for the customer name. It does not matter how many rows are deleted (0+) the required wait time is the same.

The worst case scenario so far is where we want to group by customer and filter by type on a table with 700 rows and just a handful of numeric columns. This rule is called as a lookup rule on a web form and it takes so long that the browser asks you if you want to keep waiting for it. I would expect a computer to chew up this data in no time, but it takes ages. We are not pulling more data than we need here, we do need all the data for the form, once grouped and filtered.