Re: Cannot delete if related many issue
sounds like 4D is not using the index/relation, is possibly doing a sequential search. load cache limit of records, unload, load next cache limit repeat. I would still look at circular relations that could be the part of the issue; as I recall 4D changed the way it handles following relations somewhere in v15/16 file a bug report. might be hard to create a test database though. Chip On Mon, 9 Jul 2018 11:52:16 +0200, Arnaud de Montard via 4D_Tech wrote: > >> Le 7 juil. 2018 à 17:52, Chip Scheide via 4D_Tech >> <4d_tech@lists.4d.com> a écrit : >> >> Arnaud, >> a couple of other thoughts >> - are any/all of the related tables very large in record count size? >> this might be a cache thing... >> maybe 4D is 'thrashing' loading indexes etc, then having to throw >> them away for the next table(s) and then loading them again. >> -- you might be able to see/test this by watching/adjusting the >> cache, and/or watching disk usage. >> - alternatively, maybe for some (unexplainable) reason 4D is NOT >> using the index(es) and/or relation directly and doing a sequential >> search on each related table. >> -- you might be able to test this by using test data which has a >> small number of records in the related tables, and then add more and >> more seeing if the deletion slows down (linearly?). > > Side 1 table is 2.7 billion records, side N the same, ~10 records > without sons to delete on side 1, worked during years. As it became > suddenly very slow with a cache like sawtooth wave, I agree it looks > like a threshold effect in deletion control. > > When I remove deletion control, control there is no sons (query) and > delete by myself (delete selection), it's instantaneous. It shows > that the problem occurs when 4D is checking there are no sons. > > -- > Arnaud > > > ** > 4D Internet Users Group (4D iNUG) > FAQ: http://lists.4d.com/faqnug.html > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** --- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Cannot delete if related many issue
> Le 7 juil. 2018 à 17:52, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > Arnaud, > a couple of other thoughts > - are any/all of the related tables very large in record count size? this > might be a cache thing... > maybe 4D is 'thrashing' loading indexes etc, then having to throw them away > for the next table(s) and then loading them again. > -- you might be able to see/test this by watching/adjusting the cache, and/or > watching disk usage. > - alternatively, maybe for some (unexplainable) reason 4D is NOT using the > index(es) and/or relation directly and doing a sequential search on each > related table. > -- you might be able to test this by using test data which has a small number > of records in the related tables, and then add more and more seeing if the > deletion slows down (linearly?). Side 1 table is 2.7 billion records, side N the same, ~10 records without sons to delete on side 1, worked during years. As it became suddenly very slow with a cache like sawtooth wave, I agree it looks like a threshold effect in deletion control. When I remove deletion control, control there is no sons (query) and delete by myself (delete selection), it's instantaneous. It shows that the problem occurs when 4D is checking there are no sons. -- Arnaud ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Cannot delete if related many issue
Arnaud, a couple of other thoughts - are any/all of the related tables very large in record count size? this might be a cache thing... maybe 4D is 'thrashing' loading indexes etc, then having to throw them away for the next table(s) and then loading them again. -- you might be able to see/test this by watching/adjusting the cache, and/or watching disk usage. - alternatively, maybe for some (unexplainable) reason 4D is NOT using the index(es) and/or relation directly and doing a sequential search on each related table. -- you might be able to test this by using test data which has a small number of records in the related tables, and then add more and more seeing if the deletion slows down (linearly?). Chip > Arnaud, > I use Delete Related records a fair amount usually without any issues. > However, as John suggests, the sorts of records that may be deleted this > way are usually limited to linking records, prefs and things like that. > > It can be frustrating when a record fails to delete for no known reason > only to find, eventually, Deletion Control was to blame because some record > had a relation to some other record that was locked. I don't recall the > error message when that happens but I think it doesn't necessarily tell you > where the record that prevented deletion is. > > On Sat, Jul 7, 2018 at 4:20 AM Arnaud de Montard via 4D_Tech < > 4d_tech@lists.4d.com> wrote: > >> ... >> > > The deleted records had no "sons". Searching if there are sons with >> QUERY(sons;foreign key=primary key) takes a few milliseconds. MSC says >> everything's OK. 4D v14r5. >> > Do any of the 'sons' records have empty foreign key fields? Hell is other people Jean-Paul Sartre ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Cannot delete if related many issue
Arnaud, I don't use deletion control either... however, maybe there is a circular relation* and 4D iterates through this relational circle either a fixed (large) number of times, or after some (large) number of iterations through the circle 4D finally finds that there are no related children records? * just in case :)(-> = relation, letter = table) A -> B -> C -> D -> A > Arnaud, > I use Delete Related records a fair amount usually without any issues. > However, as John suggests, the sorts of records that may be deleted this > way are usually limited to linking records, prefs and things like that. > > It can be frustrating when a record fails to delete for no known reason > only to find, eventually, Deletion Control was to blame because some record > had a relation to some other record that was locked. I don't recall the > error message when that happens but I think it doesn't necessarily tell you > where the record that prevented deletion is. > > On Sat, Jul 7, 2018 at 4:20 AM Arnaud de Montard via 4D_Tech < > 4d_tech@lists.4d.com> wrote: > >> ... >> > > The deleted records had no "sons". Searching if there are sons with >> QUERY(sons;foreign key=primary key) takes a few milliseconds. MSC says >> everything's OK. 4D v14r5. >> > Do any of the 'sons' records have empty foreign key fields? Hell is other people Jean-Paul Sartre ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Cannot delete if related many issue
Arnaud, I use Delete Related records a fair amount usually without any issues. However, as John suggests, the sorts of records that may be deleted this way are usually limited to linking records, prefs and things like that. It can be frustrating when a record fails to delete for no known reason only to find, eventually, Deletion Control was to blame because some record had a relation to some other record that was locked. I don't recall the error message when that happens but I think it doesn't necessarily tell you where the record that prevented deletion is. On Sat, Jul 7, 2018 at 4:20 AM Arnaud de Montard via 4D_Tech < 4d_tech@lists.4d.com> wrote: > ... > The deleted records had no "sons". Searching if there are sons with > QUERY(sons;foreign key=primary key) takes a few milliseconds. MSC says > everything's OK. 4D v14r5. > Do any of the 'sons' records have empty foreign key fields? -- Kirk Brooks San Francisco, CA === *We go vote - they go home* ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Cannot delete if related many issue
Arnaud de Montard: Since 4D v2.0.10 I have not used it (probably not in 4D way back then). When we used to permit actual deletions but we handled all record deletions in our code especially for related records that also needed to go. Now we do not permit actual deletions - we just flag records as deleted. Therefore I cannot comment on the feature other than what is in the manual. Jody > On Jul 7, 2018, at 5:20 AM, Arnaud de Montard via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > >> Le 5 juil. 2018 à 13:12, Arnaud de Montard via 4D_Tech >> <4d_tech@lists.4d.com> a écrit : >> >> I have a database in which Deletion Control is set as in the object for a >> lot of relations. Today, deleting 10 records in a table having ~2 billion >> records took an incredible time, cache as shark teeth, unable to connect and >> so on. Finally we had to kill the server. After setting Deletion Control to >> "none", DELETE SELECTION becomes instantaneous. The deleted records had no >> "sons". Searching if there are sons with QUERY(sons;foreign key=primary key) >> takes a few milliseconds. MSC says everything's OK. 4D v14r5. >> >> Does someone have noticed issues with Deletion Control as "Cannot delete if >> related many"? > > No answer… > No one uses deletion control? > > -- > Arnaud de Montard ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Cannot delete if related many issue
> Le 5 juil. 2018 à 13:12, Arnaud de Montard via 4D_Tech <4d_tech@lists.4d.com> > a écrit : > > I have a database in which Deletion Control is set as in the object for a lot > of relations. Today, deleting 10 records in a table having ~2 billion records > took an incredible time, cache as shark teeth, unable to connect and so on. > Finally we had to kill the server. After setting Deletion Control to "none", > DELETE SELECTION becomes instantaneous. The deleted records had no "sons". > Searching if there are sons with QUERY(sons;foreign key=primary key) takes a > few milliseconds. MSC says everything's OK. 4D v14r5. > > Does someone have noticed issues with Deletion Control as "Cannot delete if > related many"? No answer… No one uses deletion control? -- Arnaud de Montard ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Cannot delete if related many issue
I have a database in which Deletion Control is set as in the object for a lot of relations. Today, deleting 10 records in a table having ~2 billion records took an incredible time, cache as shark teeth, unable to connect and so on. Finally we had to kill the server. After setting Deletion Control to "none", DELETE SELECTION becomes instantaneous. The deleted records had no "sons". Searching if there are sons with QUERY(sons;foreign key=primary key) takes a few milliseconds. MSC says everything's OK. 4D v14r5. Does someone have noticed issues with Deletion Control as "Cannot delete if related many"? -- Arnaud de Montard ** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
