search

fisharebest / webtrees

Online genealogy
https://webtrees.net
GNU General Public License v3.0
456 stars 298 forks source link

Performance - Statistics - Month of first marriage #2296

Closed melizaa closed 4 years ago

melizaa commented 5 years ago

The chart runs a long time on my big Gedcom in the test systems. Is there a way to shorten the time?

magicsunday commented 5 years ago

How many individuals does your tree contain?

melizaa commented 5 years ago

A little less than 30,000 individuals and some 11,500 families.

magicsunday commented 5 years ago

Occurs the problem also in the current stable version 1.7 of webtrees?

fisharebest commented 5 years ago

The SQL query is taking about 45 seconds. The entire request took 55 seconds.

I guess that if the server was busier, the request would take more than 60 seconds (the timeout limit), and the chart will never load.

Since the chart only cares for dates with Gregorian months (JAN ... DEC), we could add an extra condition to the query WHERE d_month IN ('JAN', 'FEB', 'MAR', ... 'DEC).

This reduces the query time to 30 seconds for your tree.

FYI, on my own site, with approx 1/3 the number of individuals, the query runs in approx 0.5 seconds.

I think the most likely cause is the indexes. They are all poorly chosen. For example, they do not include the tree - which affects you because you have many trees on your site.

This table needs a lot of work. In addition to the indexes, we need to store the leap-year status of each date. Currently, we calculate it using MySQL, but we now need to support other databases. But most importantly, I'd like to extend it into a "fact/event" table.

fisharebest commented 5 years ago

I notice that the SQL query is sorting the results - which is not necessary for the chart.

I have removed this.

fisharebest commented 5 years ago

Actually - looking at the code, I'm not sure that it is actually finding the first marriage. There's no grouping on the dates table...

The lack of primary key on this table makes it very ineffecient to do such a group/join...

fisharebest commented 5 years ago

Oops - the code was sorting in SQL, and then selecting the first row in PHP.

Reverted....

melizaa commented 5 years ago

Rico,

My live site uses a 1.7 GitHub version. It takes also pretty long, and I do not see that it is running.