index filter dbsum corruption

[MikeC]

Hi,

A question regarding the use of a filtered index. I have read many times on the board that the number of indices, especially those that are filtered, should be limited as when you have an errant one it can corrupt them and cause problems....and that filtered indices are especially prone to corruption.

Given the above, is there anything I should be aware of in using a filtered index that can cause problems? Or perhaps another way in which to add a filter to dbsum() as that is the reason behind using the filtered index. Tablesum() is too slow over a network / mapped drive and so is why I chose dbsum().

[Finian Lennon]

Re: index filter dbsum corruption

Mike:

The problems with filtered indexes are real. If the table you are referring to is a component of any form used for data entry, I wouldn't allow filtered indexes on that table.

On the other hand, a temp table, with filtered indexes defined to match your dbsum expressions, will work well. Assuming the temp table is populated via an append, the filtered indexes are no more fragile than any others and you have a great way to get the results you need. It's true that it involves more work but it pays back with reliability.

I've used a variant of this method for years to generate monthly statements in our application. The DBSum method is much faster than tablesum, even taking into account the pre-processing time for appends etc.

[MikeC]

Re: index filter dbsum corruption

Thanks Finian,

It so happens that the table in question is the parent table of a set. The filtered index is on a single field of the parent table that, once data is entered, it generally would remain. It is a simple filter (although whether the simplicity matters or not I don't know)--

.NOT. (CONTAINSI(ACCOUNTNAME,"Total"))

[csda1]

Re: index filter dbsum corruption

Hi Mike,

I've talked about this before, and will reiterate it again. If you think filtered indexes are potentially a problem (and I can't say that I've seen this in my apps), the solution is to eliminate the filter portion and create a more complex index expression like this for your example:

IF(CONTAINSI(ACCOUNTNAME,"Total"),"0","1")

All of your desireded records will have an index starting with "0", which can be used in links, dbcount() etc. with the right coding.

[MikeC]

Re: index filter dbsum corruption

Ira,
Thanks.

This gives me a possible alternate now in case, for some reason, the index does corrupt.

I am not entirely certain of how to use this though and while this is still fresh, I am wondering if a bit more insight could be given for say the following...

I want to sum a specific field in the parent table.
The records have groupings---say group1 and group2
I only want the group2 records to be summed.

Currently the Group_Index does this with the aforementioned filter on the index by eliminating any record that has "Total" in a different parent field's value (this value will always correspond to the grouping field which happens to be a child table field).

t.parent_field=dbsum("parent_table","Group_Index",var->indexed_field,"summed_parent_field")

So again, currently the dbsum creates the sum of the summed_parent_field over the variable indexed_field, but only when that particular record does NOT contain the word "Total" in the parent field that corrresponds to the Child grouping field.

Clear as mud I see while trying to write this!!! It actually is very straight-forward, but explaining it in writing is extremely difficult.



Even if the above is not understood, maybe just a little more explanation regarding how to use the index result in your example in order to limit the records.

---I have a feeling that maybe due to my using a different field in the index filter, I may not be able to use your suggested alternative??

If this is something that cannot be easily explained, I will simply wait until I need it I guess...I just wanted to be prepared is all.

[csda1]

Re: index filter dbsum corruption

Hi Mike,

For the example I gave you, you can use something like this to sum each group within the entire index

Group0=dbsum("parent_table","Group_Index","0","summed_parent_field")
Group1=dbsum("parent_table","Group_Index","1","summed_parent_field")

If you use the index expression with more conditions, you can create different groups within the entire index that can be each be summed or totaled individually.

[MikeC]

Re: index filter dbsum corruption

Super! Thanks once again Ira....much appreciated. :)

[johnkoh]

Re: index filter dbsum corruption

Mike,

dim vSovid as c ="08-1884-F"
dim vInvDate as d={10/15/2010}

vtest= tablesum("invoice_itms","Sov_Id="+quote(vSovId)+" .and. link_invdate<"+s_quote(vInvDate),"Current_work")
ui_msg_box("test","tablesum is    "+vtest)

vdb = dbsum("invoice_itms","Sov_date","Sov_Id="+quote(vSovId)+" .and. link_invdate<"+s_quote(vInvDate),"current_work")
ui_msg_box("test 2","dbsum is  "+vdb)

vNodt = dbsum("invoice_itms","Sov_Id",var->vSovId,"current_work")
ui_msg_box("test 3","No date dbsum is  "+vNodt)

vSmp = dbsum("invoice_itms","Sov_date",var->vSovId + cdate(var->vInvDate),"current_work")
ui_msg_box("test 4","dbsum is  "+vSmp)

The "tablesum()" is working and it is what I want but too slow so I want to use the "Dbsum()" but I could not get the right way to write it.

I think, If I set the Index as Sov_date = Sov_Id + cdate(Invoice_date) at Index Builder, the last test (vSmp) code should be work but it does not.

Do you have any idea what is correct coding?

[Stan Mathews]

Re: index filter dbsum corruption

Sov_date = Sov_Id + cdate(Invoice_date)

if Sov_id is a character field that is not fully filled in all records then you need to trim it.

Sov_date = trim(Sov_Id) + cdate(Invoice_date)

[MikeC]

Re: index filter dbsum corruption

What Stan said.....and I also trim the index definition as well. That way you will always be comparing apples to apples. Another remote possible is that some functions seem to have a hard time with multiple variables and/or parsing an expression and by using only one variable in the function by setting it to the others has cured this oddity for me -- ie; additional_variable = var->vSovId + cdate(var->vInvDate)....an eval() should be able to be used you would think in this rare case but I have not successfully been able to use eval() in a function so had to resort to the above. Sometimes it seems that to cure Alpha's confusion you have to make things very simple at times in expressions.

[johnkoh]

Re: index filter dbsum corruption

Stan,

You are right. After changed the Index, works well
Thank you

if Sov_id is a character field that is not fully filled in all records then you need to trim it.