My biggest table tends to only have around 40K records. This is the main table that most supporting tables revolve around. I did a compact on the database earlier in the day (This is my dev database) so there should be almost on fragmentation. The relate many is run on the table primary key which is a UUID. It used to be an INT back in the day but things got really ugly after about 32K records. All the related tables are indexed with a btree index on the relationship field. I don’t store any pictures in the database instead I just hold a reference to the photos (File pathname).
I have briefly seen it flashing sequential searching dialog box which made me think I had the indexes wrong. I was hoping there way a way like in mysql to explain slow queries to tell my how or what why the query was being slow. The annoying part is takes up to 10 seconds to load a case and its supporting data for display in a field. In a remote connection you can make coffee from raw beans before it loads the record completely. ----------------------------------------------------------------------- MacCafe 4405 Talmadge St. Toledo, OH 43623 Eric Naujock - ACSA 10.2, 10.3, 10.4 Apple - ACTC 10.5, 10.6, 10.7, 10.8, 10.9, 10.10 http://www.mac-cafe.com email: e <mailto:e...@mac-cafe.com>r...@mac-cafe.com <mailto:e...@mac-cafe.com> AOL IM: erlic > On Jun 28, 2017, at 8:55 PM, John DeSoi via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > Do any of the tables have a large number records? I have noticed recently > that large tables (e.g. 20 million records) are very slow with DISTINCT > VALUES even on indexed fields with a relatively small number values. For > example on a big table and selection of 14,000 records DISTINCT VALUES only > takes about 25 ms. But if the selection is 15,000 or a little more it > suddenly jumps to about 3500 ms. > > 4D reports the table is not fragmented. I have not tried compacting or > rebuilding indexes yet. You might give this a try to see if it makes a > difference. > > I don't use RELATE MANY, but recently verified the same problem with DISTINCT > VALUES impacts RELATE ONE SELECTION. 4D's built in progress dialog pops up > with a message such as "computing distinct values". > > > John DeSoi, Ph.D. > > >> On Jun 28, 2017, at 6:59 PM, Eric Naujock via 4D_Tech <4d_tech@lists.4d.com> >> wrote: >> >> I have been trying to do some performance tuning an optimizing on one of my >> 4D databases and am finding that one function RELATE MANY is taking over >> 1790 ms. At lease are there any 4d Tools that are available to help analyze >> the case of tis timeout and resolve the issue. I have tried adjusting the >> indexes on the related records but that does not seem to help. > > ********************************************************************** > 4D Internet Users Group (4D iNUG) > FAQ: http://lists.4d.com/faqnug.html > Archive: http://lists.4d.com/archives.html > Options: http://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ********************************************************************** ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **********************************************************************
