Re: Slow Commit New Record - Looking for suggestions
- From: "Remi-Noel Menegaux" <rnmenegaux@xxxxxxx>
- Date: Mon, 29 Aug 2005 08:17:23 +0200
Dave,
Excellent checkup and explanations - while a bit sad for FM7 -. Il will
help me (us) in debugging.
Remi-Noel
"42" <nospam@xxxxxxxxxx> a écrit dans le message de news:
MPG.1d7bfbfbaee21bcd989ccd@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
> In article <MPG.1d7bd1a1f9304c70989ccc@xxxxxxxxxxxxxxxxxxxxxxxxx>,
> nospam@xxxxxxxxxx says...
>> Still trying to get a converted system to run acceptably:
>>
>> I'm stymied on this issue:
>>
>> This is the main orders table of a complex FM6-> FM7 converted
>> solution.
>> Longer term we will rewrite to take advantage of FM7 architecutre.
>> Short
>> term... get it running fast enough to be usable.
>>
>> Issue: Creating a new record takes upwards of 20 seconds.
>>
>> This simple script takes 20 seconds to run:
>>
>> Commit Records
>> New Record
>> Commit Records
>>
>> Debugging shows that it hangs on the 2nd commit records for 20
>> seconds.
>>
>> The table is 146 fields:
>>
>> 1 summary field
>> 26 globals
>> 45 unstored calcs
>> 6 stored calcs
>> 68 regular fields
>>
>> About 3/4s of the regular fields & stored calcs are indexed.
>>
>> There are:
>> 0 lookups
>> 1 auto enter (the serial number)
>>
>> In FM5/6 it took around 1-2 seconds to run the entire "new order"
>> script, which did a series of validations, created the record, and
>> initialized around 20 fields. I need to get it back to that.
>>
>> Any suggestions for things to try?
>>
>> regards,
>> dave
>
>
>
> Followup... I found the issue after literally hacking the database to
> pieces.
>
> 1) Blew out all the relationships (40 or so).
> No change.
>
> 2) Blew out all the globals, and calculcuations (stored and unstored).
> No change.
>
> 3) I tried just using the empty clone...
> Progress: Instantaneous new records.
>
> At this point my working theory was the size and number of indexes, or
> perhaps corruption therof. (There were 53 indexed fields, nearly
> 200,000
> records)
>
> 4) I cloned, recovered the clone, and imported to the recovered clone
> to
> see if that solved it.
> Nope. Scratch corrupted indices.
>
> 5) Blew out all the indexes save the one on the primary key.
> No change. Scratch too many indices.
>
> At this point the database was pretty lean. Down to 50 something
> regular
> fields with no validation/autoenter/lookup/or another other optional
> settings, except for the pkey which was still set to: indexed,
> autoenter
> serial, unique, prohibit modification.
>
> 6) I did the only thing that was left. I removed the options on the
> pkey
> field.
> Bingo: instant new records.
>
> Playing with it a little more led to the followign discovery:
> The issue causing all the slowdown was the validation constraint:
> unique.
>
> Removing that one checkbox in the original unhacked to shreds file
> boosted performance back to acceptable.
>
> I hope my trial helps someone.
>
> ---------
>
> I guess the only question I have is **Why**!! It goes without saying
> that this was not a performance killer in FM5/6... what happened in 7?
>
> Given that a find on a particular serial, or a goto related records on
> a
> self join is instantaneous its incomprehensible that it would take 20
> seconds for FM to determine that the field is unique.
>
> -regards,
> Dave
>
>
.
- Follow-Ups:
- Prev by Date: Re: Help w/FM Server 7 for Windows
- Next by Date: Re: Importing XML
- Previous by thread: Re: Slow Commit New Record - Looking for suggestions
- Next by thread: Re: Slow Commit New Record - Looking for suggestions
- Index(es):
Relevant Pages
|
