 | |
Slow Commit New Record - Looking for suggestions
comp.databases.filemaker comp.databases.filemaker
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Slow Commit New Record - Looking for suggestions -
08-28-2005
, 04:50 PM
#2
| |||
| |||
|
|
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
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
#3
| |||
| |||
|
|
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
#4
| |||
| |||
|
|
In article <MPG.1d7bd1a1f9304c70989ccc (AT) shawnews (DOT) vf.shawcable.net>, nospam (AT) nospam (DOT) 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 |
#5
| |||
| |||
|
|
Dave, Excellent checkup and explanations - while a bit sad for FM7 -. Il will help me (us) in debugging. Remi-Noel "42" <nospam (AT) nospam (DOT) com> a écrit dans le message de news: MPG.1d7bfbfbaee21bcd989ccd (AT) shaw....shawcable.net... In article <MPG.1d7bd1a1f9304c70989ccc (AT) shawnews (DOT) vf.shawcable.net>, nospam (AT) nospam (DOT) 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 |
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.
#6
| |||
| |||
|
|
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 |
would have been slow in FM6 too.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.