Alexandre Benson Smith wrote:

> Todd E. Brasseur wrote:
>
> >Hello,
> >
> >We have one table in our application that gets alot of hits. We are
> >having problems with corrupted indexes in this table.
> >
> >
> Todd,
>
> What FB version, and wich SO are you using ?

Firebird-1.0.0.796-Win32.exe

>
>
> Corrupted indices, or corrupted DB in general, should not occurr, even
> if that table are getting smashed by a lot of activity. I think the
> cause of corruption is not the amount of update (or insert or delete) on
> a given table.

It is strange:

What will happen is a 'ghost' record will appear. If I do a:

Select * from taxpassoff where Propid = 1220000000

I will get four records (All showing that they have AssessmentYear = 2004)

If I change the select to:

Select * from taxpassoff where Propid = 1220000000 and AssessmentYear = 2004

I only get 3 records. AssessmentYear and PropID are both INTEGERS.

If I change the select to:

Select * from taxpassoff where Propid = 1220000000 and
cast(AssessmentYear as INTEGER) = 2004

I will see all four records.

This table may have 120,000 records in it and every once in a while
there will be 1 to 4 records that have done this.

Another thing that has happened to this table a couple of times is that
we have a Primary Key defined. But when we backup and restore the
database we get an error saying the Primary Key has been violated. When
I look in the database I can find a record that violates the Primary Key.

Our database consists of approx. 150 tables. There over 500 stored
procedures and over 472 triggers. We have the database and application
running in approx. 70 locations of various sizes. We love firebird and
find it very good. The only real problem we have is corruption in this
one table occasionally. It has probably happened about 10 times in a
few of the sites over the past 3 years or so.

>
>
> >My question is:
> >
> >Is it more efficient to delete records and then insert new records or to
> >update existing records multiple times?
> >
> >
> >For example: (primary 1 and primary 2 make up the primary key on the
> table)
> >
> >Primary1 Primary2 Value
> >1 1 10
> >1 2 50
> >1 3 60
> >
> >needs to be changed to:
> >
> >1 1 20
> >1 2 60
> >
> >There is a fairly complex calculation that is coded in a stored
> >procedure to come up with the values. The number of records can vary.
> >
> >Is it better to:
> >
> >1) Delete the three records
> >2) Insert the two records
> >
> >Or to:
> >
> >1) Update all records to value of 0
> >2) Calculate the values and change existing records (or insert new
> >records if need be)
> >3) Delete any records that still have a value of 0
> >4) This method will typically have alot of updates and not very many
> >inserts and deletes.
> >
> >
> >I hope this makes sense. In actuality, the data is more complex, but I
> >feel the example shows what we are after.
> >
> >Any information regarding the best course of action would be appreciated.
> >
> >
>
> If you are updating the same record multiple times will be slower than
> insert a record with the final value, but if you insert the value with
> an intermediate value and then continues to update the same record, this
> case should be slow. I think if you delete the record and then insert a
> record with the final value will be slower than update the value with
> the final value.

The problem we have is that the record may need to be inserted and then
updated multiple times. In our old application (Clipper and DBFs) we
did this calculation in an array in memory. When the numbers were final
we updated the table. We are trying to use the table as the array. One
idea we had was to use a temporary table where we would do the inserts
and updates and then move the final records into the main table. Our
thinking was that we could do all inserts into the temporary table and
then summarize the data before updating the main table. We were
thinking that the main table wouldn't get corrupted as often because we
wouldn't be hitting it as often, especially with multiple updates in the
same transaction.

Thanks for the info. Now you have me puzzled regarding what could be
causing the corruption.

Todd

>
>
> >Todd
> >
> >
> I think you are trying the wrong approach to solve your corruption
> problem.
>
> see you !
>
> --
>
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda.
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br
>
>
> ------------------------------------------------------------------------
> Yahoo! Groups Links
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>

[Non-text portions of this message have been removed]