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

42 wrote:
> 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
> 
> 
> 
> 
Just a quick question:

Could a recalculation of the summary field value be the
source of the excess time needed?

Can you remove the summary field from the layout you
use during this new record creation?

I have noticed that in my FMPro 5.5 Win2k WinXP database.

Opie

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

In article <MPG.1d7bd1a1f9304c70989ccc@shawnews.vf.shawcable.net>, 
nospam@nospam.com 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

Dave,
Excellent checkup and explanations - while a bit sad for FM7 -. Il will 
help me (us) in debugging.
Remi-Noel

"42" <nospam@nospam.com> a �crit dans le message de news: 
MPG.1d7bfbfbaee21bcd989ccd@shawnews.vf.shawcable.net...
> In article <MPG.1d7bd1a1f9304c70989ccc@shawnews.vf.shawcable.net>,
> nospam@nospam.com 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
>
> 

In article <4312a874$0$31015$626a14ce@news.free.fr>,
 "Remi-Noel Menegaux" <rnmenegaux@free.fr> wrote:

> Dave,
> Excellent checkup and explanations - while a bit sad for FM7 -. Il will 
> help me (us) in debugging.
> Remi-Noel
> 
> "42" <nospam@nospam.com> a �crit dans le message de news: 
> MPG.1d7bfbfbaee21bcd989ccd@shawnews.vf.shawcable.net...
> > In article <MPG.1d7bd1a1f9304c70989ccc@shawnews.vf.shawcable.net>,
> > nospam@nospam.com 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
> >
> > 

I would also add that when you make a new record in a layout that has 
some calculations on related records, and summary fields, it can take a 
while to do the calculations if there are a lot of records in the 
argument of the calculations. If you do not need those calculations and 
summary fields in the layout where you make the new records, you may 
well find that it speeds up considerably if you do it in a layout that 
does not have those fields.

Bill Collins

-- 
For email, remove invalid.

In article <bbcollins-C67589.09583529082005@news1.east.earthlink.net>, 
bbcollins@invalid.earthlink.net says...

> I would also add that when you make a new record in a layout that has 
> some calculations on related records, and summary fields, it can take a 
> while to do the calculations if there are a lot of records in the 
> argument of the calculations. If you do not need those calculations and 
> summary fields in the layout where you make the new records, you may 
> well find that it speeds up considerably if you do it in a layout that 
> does not have those fields.
> 
> Bill Collins

True. However that would be a general performance tuning issue that 
would have been slow in FM6 too.