Viewing/creating an invoice takes long to load - 7.

drhoo

drhoo

New Member
I'm currently on 7.1.2, PHP: 8.4 (have tried 8.5) and my php.ini is all optimised for maximum speed.
When I click on an invoice for a client or trying to create a single invoice, there's a noticeable lag (around 6-8 secs) compared to version 6.
Any ideas?
 
I wouldn't recommend fiddling with your database until CE team confirms - I manage my own server, but I'm nowhere as knowledgeable as Clientexct developers.

UPDATE – SOLVED: Slow Create/Edit Invoice Pages

I've managed to significantly improve the loading speed of the Create/Edit Invoice pages in ClientExec 7.2.2.

Environment: PHP 8.5, MySQL 8.4.13, cPanel/WHM.

After looking into MySQL's slow query log, I discovered that ClientExec was repeatedly querying the invoiceentry table (approximately 408,000 records) without an index on billingtypeid.

Each query was scanning the entire table, contributing to invoice pages taking around 14–15 seconds to load.

Solution:

After backing up the database (just in case), I added the missing index using:

SET SESSION sql_mode =
REPLACE(
REPLACE(@@SESSION.sql_mode, 'NO_ZERO_IN_DATE,', ''),
'NO_ZERO_DATE,',
''
);

ALTER TABLE invoiceentry
ADD INDEX idx_invoiceentry_billingtypeid (billingtypeid);
The temporary SQL mode adjustment was necessary for compatibility with the existing database schema.

Results:

  • Estimated rows examined per query reduced from 407,977 to 304.
  • Invoice page loading improved significantly.
  • No ClientExec PHP modifications.
  • No existing records changed.
  • No permanent MySQL configuration changes.
Hopefully, this helps anyone experiencing similar invoice performance issues.

Note: Check your existing indexes and take a database backup before applying this change and wait until Matt comments
:)
 
Last edited:
I've saved most of the logs, here's one:

Code: 
=== MYSQL SLOW QUERY RESULTS ===

=== MYSQL SLOW QUERY RESULTS ===

[root@neon neonusr]# 

[root@neon neonusr]# tail -n 100 /var/lib/mysql/neon-slow.log

/usr/sbin/mysqld, Version: 8.4.13 (MySQL Community Server - GPL). started with:

Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock

Time                 Id Command    Argument

# Time: 2026-10-28T09:29:09.287676Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.733832  Lock_time: 0.000002 Rows_sent: 1  Rows_examined: 407977

use nico_ce711-live;

SET timestamp=1793179748;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '11';

# Time: 2026-10-28T09:29:10.022558Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.734105  Lock_time: 0.000001 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179749;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '3';

# Time: 2026-10-28T09:29:10.755883Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.732329  Lock_time: 0.000001 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179750;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '14';

# Time: 2026-10-28T09:29:11.497691Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.740895  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179750;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '8';

# Time: 2026-10-28T09:29:12.272177Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.773620  Lock_time: 0.000001 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179751;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '15';

# Time: 2026-10-28T09:29:13.007976Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.735110  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179752;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '1';

# Time: 2026-10-28T09:29:13.750571Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.741828  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179753;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '4';

# Time: 2026-10-28T09:29:14.475799Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.724305  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179753;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '2';

# Time: 2026-10-28T09:29:15.201091Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.724354  Lock_time: 0.000001 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179754;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '12';

# Time: 2026-10-28T09:29:15.938622Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.736657  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179755;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '5';

# Time: 2026-10-28T09:29:16.679873Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.740503  Lock_time: 0.000001 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179755;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '6';

# Time: 2026-10-28T09:29:17.422880Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.742204  Lock_time: 0.000001 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179756;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '13';

# Time: 2026-10-28T09:29:18.161190Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.737415  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179757;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '10';

# Time: 2026-10-28T09:29:18.916144Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.754278  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179758;

SELECT count( billingtypeid ) FROM invoiceentry WHERE billingtypeid = '9';

# Time: 2026-10-28T09:29:20.310976Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 1.391717  Lock_time: 0.000001 Rows_sent: 385374  Rows_examined: 793351

SET timestamp=1793179758;

SELECT b.id, b.customerid, b.taxamount, b.invoiceid, UNIX_TIMESTAMP(b.date) AS tsdate, b.date, b.period_start, b.period_end, b.price, b.quantity, b.description, b.detail, b.billingtypeid, b.is_prorating, b.paymentterm, b.appliestoid, b.taxable, (b.billingtypeid = -3) AS isdiscount, recurring, recurringappliesto FROM invoiceentry b WHERE b.invoiceid = '0'  ORDER BY id asc;

# Time: 2026-10-28T09:29:21.659838Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 1.348766  Lock_time: 0.000001 Rows_sent: 385374  Rows_examined: 793351

SET timestamp=1793179760;

SELECT b.id, b.customerid, b.taxamount, b.invoiceid, UNIX_TIMESTAMP(b.date) AS tsdate, b.date, b.period_start, b.period_end, b.price, b.quantity, b.description, b.detail, b.billingtypeid, b.is_prorating, b.paymentterm, b.appliestoid, b.taxable, (b.billingtypeid = -3) AS isdiscount, recurring, recurringappliesto FROM invoiceentry b WHERE b.invoiceid = '0'  ORDER BY id asc;

# Time: 2026-10-28T09:29:22.424623Z

# User@Host: nico_ce702[nico_ce702] @ localhost []  Id: 29359

# Query_time: 0.759941  Lock_time: 0.000002 Rows_sent: 1  Rows_examined: 407977

SET timestamp=1793179761;

SELECT SUM(b.price) FROM invoiceentry b WHERE b.invoiceid = '0';
 
You must log in or register to reply here.
Back
Top