how to speed up looking at an invoice

I have IN installed on a windows server with 5 years of invoices, 27000 line items, takes 3 to 4 min (no joke) to load an existing invoice

any ideas as to what I can do to speed this up. love the software and love to be able to look at history on invoices, we look up old invoices for customers to see what we charged them last time. so I need to keep the history.

This sounds like a configuration issue. Our production box is orders of magnitude larger that this and we always have sub second queries on the invoice/invoice_items table.

Without knowing your exact setup, the first thing I would suggest is more memory for the query_cache for your DB, and enable slow logging on PHP and MySQL to see where the bottle neck actually is.

Do all invoices take that long to load, or do invoices with less line items load quicker?

Also, how many clients to you have in your account?

yes, all invoices take that long, I am converting my data to the json format that IN wants and will try to import it that way and see what happens.

4100 clients, 27,557 invoices and quotes, 58,538 line items

The method used to import the data shouldn’t have an impact on the app’s performance.

Also, the JSON format is designed to transfer data from one Invoice Ninja install another. It isn’t meant to be used to upload data from a third party source.

JSON seems to be the only way to get payment information in to the system via the import.
unless I am missing something.

frank

Hmmm… that’s a good point.

We’ll look into supporting the payment details through the CSV import.

I am importing to mysql tables products, clients, invoices, invoice_items, payments and updating the public_id.

all goes in without errors, but only products show in Invoice Ninja.

can anyone tell me what fields have to have values for the clients to show?
I have tried setting the created_at and the updated_at but that did not seem to work.

first record example:
id, user_id, account_id, currency_id, created_at, updated_at, deleted_at, name, address1, address2, city, state, postal_code, country_id, work_phone, private_notes, balance, paid_to_date, last_login, website, industry_id, size_id, is_deleted, payment_terms, public_id, custom_value1, custom_value2, vat_number, id_number, language_id, invoice_number_counter, quote_number_counter, public_notes, credit_number_counter, task_rate, shipping_address1, shipping_address2, shipping_city, shipping_state, shipping_postal_code, shipping_country_id, show_tasks_in_portal, send_reminders, custom_messages
‘1863’, ‘1’, ‘1’, NULL, NULL, NULL, NULL, ‘1ST BUSINESS SOLUTIONS, INC.’, ‘1021 2ND AVE SW’, NULL, ‘ONALASKA’, ‘WI’, ‘54650’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ‘0’, NULL, ‘1863’, NULL, NULL, NULL, ‘1863’, NULL, ‘1’, ‘1’, NULL, ‘1’, ‘0.0000’, NULL, NULL, NULL, NULL, NULL, NULL, ‘0’, ‘1’, NULL

thank you
frank

I’d strongly advise against this approach, the app caches a lot of data (for example the client balances) which will most likely end up incorrect.

One problem I see is the invitations table is missing.

Also the contacts table

they are just empty at the moment, have to do one table at a time, I will update the balances and paid_to_date and amount in the next steps.

frank

Ok, good luck!

#CLEANED IT UP A LITTLE

delete from payments;
delete from invoice_items;
delete from invoices;
delete from clients;
delete from products;

ALTER TABLE ninj646.products CHANGE COLUMN public_id public_id INT(10) NULL ;
##import products
insert into products (product_key, cost, notes,tax_rate1,tax_rate2, account_id, user_id )
SELECT distinct trim(ucase(c4data)) as product_key, ‘0’ as cost, ‘’ as notes,
‘8.25’ as tax_rate1, ‘0’ as tax_rate2, ‘1’ as account_id, ‘1’ as user_id
FROM ninj646.ices_inv inner join ninj646.ices_det on ices_det.invno = ices_inv.INVNUMBER
where invno >= ‘21715’ and INVPRINTED = ‘T’ and INVVOIDED = ‘F’ and
INVDELETED = ‘F’ and ISESTIMATE = ‘F’ and c4data is not null;
update products set public_id = id;
ALTER TABLE ninj646.products CHANGE COLUMN public_id public_id INT(10) UNSIGNED NOT NULL ;

clients or customers

##list clients
#select distinct inv.BTCOMPANY as name from ices_inv as inv where inv.INVDATE >= ‘20140101’ and inv.INVPRINTED = ‘T’ and inv.INVVOIDED = ‘F’ and inv.INVDELETED = ‘F’ and inv.ISESTIMATE = ‘F’ and inv.btcompany is not null order by inv.btcompany, inv.invdate desc;

ALTER TABLE ninj646.clients CHANGE COLUMN public_id public_id INT(10) NULL ;
##import clients
insert into clients (user_id, account_id, name, address1, address2, city, state,
postal_code,work_phone, private_notes,is_deleted )
SELECT ‘1’ as user_id, ‘1’ as account_id, trim(ucase(c1.btcompany)) as name,
c1.BTADDRESS1 as address1, c1.BTADDRESS2 as address2, c1.BTCITY as city,
c1.BTSTATE as state, c1.btzip as postal_code, ‘’ as work_phone, ‘’ as private_phone,
‘0’ as is_deleted
FROM i_inv AS c1
JOIN ( SELECT btcompany, MAX(invnumber) AS invnumber
FROM i_inv GROUP BY btcompany ) AS c2 USING (btcompany, invnumber)
where c1.INVDATE >= ‘20140101’ and c1.INVPRINTED = ‘T’ and c1.INVVOIDED = ‘F’ and
c1.INVDELETED = ‘F’ and c1.ISESTIMATE = ‘F’ and
c1.btcompany is not null order by c1.btcompany, c1.invdate desc;
update clients set public_id = id, id_number = id ;
ALTER TABLE ninj646.clients CHANGE COLUMN public_id public_id INT(10) UNSIGNED NOT NULL ;

ALTER TABLE ninj646.invoices CHANGE COLUMN public_id public_id INT(10) NULL ;
##import invoices
insert into invoices (client_id, user_id, account_id,invoice_status_id, invoice_number,
po_number, invoice_date, terms, public_notes,frequency_id,tax_name1,tax_rate1,
amount,balance, custom_text_value1, custom_text_value2, tax_rate2)
select clients.id as client_id, ‘1’ as user_id, ‘1’ as account_id,
‘5’ as invoice_status_id, INVNUMBER as invoice_number,
if(isnull(gicol1con),‘NA’,gicol1con) as po_number,
concat(substring(invdate,1,4),’-’,substring(invdate,5,2),’-’,substring(invdate,7,2)) as invoice_date,
if(isnull(gicol2con),’’,gicol2con) as terms,
if(isnull(gicol6con) ,’’,gicol6con) as public_notes,
‘0’ as frequency_id,‘TAX’ as tax_name1,‘8.25’ as tax_rate1,
‘0’ as amount,‘0’ as balance,
if(
concat(
if(isnull(stcompany),’’,concat(stcompany,’\r\n’)),
if(isnull(staddress1),’’, concat(staddress1,’\r\n’)),
if(isnull(staddress2),’’, concat(staddress2,’\r\n’)),
if(isnull(stcity),’’, concat(stcity,’,’)),
if(isnull(ststate),’’, concat(ststate,’ ‘)),
if(isnull(stzip),’’, concat(stzip,’’))
) is null,
concat(
if(isnull(btcompany),’’,concat(btcompany,’\r\n’)),
if(isnull(btaddress1),’’, concat(btaddress1,’\r\n’)),
if(isnull(btaddress2),’’, concat(btaddress2,’\r\n’)),
if(isnull(btcity),’’, concat(btcity,’,’)),
if(isnull(btstate),’’, concat(btstate,’ ‘)),
if(isnull(btzip),’’, concat(btzip,’’))),
concat(
if(isnull(stcompany),’’,concat(stcompany,’\r\n’)),
if(isnull(staddress1),’’, concat(staddress1,’\r\n’)),
if(isnull(staddress2),’’, concat(staddress2,’\r\n’)),
if(isnull(stcity),’’, concat(stcity,’,’)),
if(isnull(ststate),’’, concat(ststate,’ ‘)),
if(isnull(stzip),’’, concat(stzip,’’)))
) as custom_text_value1,
‘’ as custom_text_value2,‘0’ as tax_rate2
from i_inv as c1
inner join clients on clients.name = trim(ucase(BTCOMPANY))
where c1.INVDATE >= ‘20140101’ and c1.INVPRINTED = ‘T’ and
c1.INVVOIDED = ‘F’ and c1.INVDELETED = ‘F’ and
c1.ISESTIMATE = ‘F’ and c1.btcompany is not null
order by c1.btcompany, c1.invdate desc;
update invoices set public_id = id;
ALTER TABLE ninj646.invoices CHANGE COLUMN public_id public_id INT(10) UNSIGNED NOT NULL ;

ALTER TABLE ninj646.invoice_items CHANGE COLUMN public_id public_id INT(10) NULL ;
##import invoice_items
insert into invoice_items (account_id, user_id, invoice_id, product_key, notes, cost,
qty,tax_name1, tax_rate1 , tax_rate2, discount)
select ‘1’ as account_id, ‘1’ as user_id, id as invoice_id, trim(ucase(c4data)) as product_key,
‘’ as notes, c6data as cost, c5data as qty,‘TAX’ as tax_name1,
if(c8data=‘T’,‘8.25’,‘0’) as tax_rate1 , ‘0’ as tax_rate2, ‘0’ as discount
from ices_det
inner join invoices on invoice_number = invno
where invoice_number is not null and c4data is not null;
update invoice_items set public_id = id;
ALTER TABLE ninj646.invoice_items CHANGE COLUMN public_id public_id INT(10) UNSIGNED NOT NULL ;

ALTER TABLE ninj646.payments CHANGE COLUMN public_id public_id INT(10) NULL ;
##import payments
insert into payments (invoice_id, account_id, client_id, user_id, payment_type_id,
amount, payment_date, refunded) select invoices.id as invoice_id,
‘1’ as account_id, invoices.client_id as client_id, ‘1’ as user_id,
‘16’ as payment_type_id,cashamount as amount,
concat(substring(invdate,1,4),’-’,substring(invdate,5,2),’-’,substring(invdate,7,2)) as payment_date,
‘0’ as refunded from e_pay inner join invoices on invoice_number = invnumber
where invoice_number is not null;
update payments set public_id = id;
ALTER TABLE ninj646.payments CHANGE COLUMN public_id public_id INT(10) UNSIGNED NOT NULL ;

##update invoices amount and balance
#select amount from payments
##update customers payed_to_date and balance
#select amount from payments

Does the invitations and contact tables have to have entries ?

Yes, if there are no contacts it would explain why you aren’t seeing any clients.

Each client needs to have a primary contact.

Thank You, Thank You!!!

that fixed the the clients.
will do the fix and check the other tables

Ok have all my contacts in and the clients show up ( 32 char unique string ) as the contact_key. all payments show up
I created an invitations record for each invoice, contact using a (32 char unique string ) as the invitation_key
and mapped the correct contact_id, invoice_id, account_id, user_id.

did not work,

think this is my last problem.

any ideas?
does the invitation_key need to match the contact_key? did not look like it did.
frank

Hard to say…

I’d suggest using the debug bar to copy the query used in the app for the invoice list to try to see what’s missing.

Thank you for all of your help by the way!

Most seems good, but loading of an invoice for a client takes a long time even though the debug bar shows that the sqls ran in under 14 sec.

thinking it may have to do with the fields in the clients table invoice_number_counter, quote_number_counter and credit_number_counter but not sure.

currently all set to 1

could this cause the slow load of invoices, everything else loads fast and looks good.
I do have the paid_to_date, balance of the clients set as well as the invoices amount, balance, due_date and the created_at and updated_at in all tables.

frank

added:
my browser tells me a script is taking too long what do i what to do ‘stop’, ‘wait’ if I click stop, it brings it right up.

Is /invoices/create also slow to load?