Filtered Indexes

[Jeff Fried]

I know the distinction between a filtered index and a query, but for reasons of speed, a filtered index is coneptually a better approach since the index already exists and does not need to be rebuilt each time. The problem is, it appears filtered indexes get corrupted or lose individual indexes very easily.

Take the case of the order entry app where we want to have a sales rep view only his information. He is known by the user_id and password when he enters the system. There are also indexes for each sales rep that are selected upon entering the form based on a condition (i.e., select A's filtered index if the user is A, etc.).

Does anybody know why this has turned out to be unreliable? It works in general, but then the index gets lost from time to time. I don't mean the CDX disappears but rather, if there were 5 indexes in there and two were filtered by sales rep, those two disappeared and left only the 3 unfiltered ones.

If this really is not the best way to keep a user only looking at his data within a table there is a field for each rep, what would be a better way? Sure you can always create a query on the fly, but isn't that going to slow things down and suffer the additional detriment of not being updated as new records are added?

Thank you if you're the guru coming to my rescue.

Jeff

[Stan Mathews]

RE: Filtered Indexes

No guru here but a query which can use an index is much faster than those that do not. IOW, with an index on a date field, defining an query which selects a subset by date range, particular or start and end, is very fast.

I've never seen a need for a filtered index.

[Jim Chapman]

RE: Filtered Indexes

Hello Jeff,

Filtered indexes have always been problematic. I agree with Stan, but if you want to wring the last bit of speed out of these routines, define a range. This has the same effect as a filtered index and the same speed without the problems. Be aware however that Alpha has depreciated ranges.

Good luck,
Jim

[Jeff Fried]

RE: Filtered Indexes

Thanks Jim and Stan for your POV's. It's unfortunate that filtered indexes cannot be a reliable way to filter by a certain value. In this case, the value is not stictly a standalone field, such as sales rep. It's a part of a field, as in the following filter in the index: LEFT(ACCT,1)="B", meaning the sales rep is identified by the first character of a certain field. It may be that going the function route also contributes to the problematic nature of these filtered indexes.

The way I'm using them is to have, in the entry to the form, the two indexes with filters LEFT(ACCT,1)="B" and LEFT(ACCT,1)="D", then select the "B" one if USER_NAME()="Daryl" and the "D" one if USER_NAME()="John". I wonder if creating a field dedicated to the sales rep would make a difference, so the filter expressions could be more like REP="B". Of course I could try this out, or attempt a range approach, or attempt a query approach, but it would be easier if I could just have the filtered index work reliably since there's much rework to do by making the change (such as popups that are filtered on the key field).

Thanks for your input.

Jeff