Simple Invoices - Forum

I get faster pages to load after insert in .htaccess this gzip compression lines:


SetOutputFilter DEFLATE


Will it work with you?

Nope. Problem is not the page load time but the database retrieval time/processing time. While loading I can see mysqld.exe using 100% of my CPU on Task Manager. That would not be a problem if it were for just a few seconds, though.

http://www.techiecorner.com/45/turn-on-mysql-query-cache-to-speed-up-mysql-query-performance/

thought i would offer that idea, cache seems like it might be a big help, have you tried migrating the install/db to a external server to see if it could be an issue with your local server?

oh yeah and i forget if xampp uses a my.ini or whatever but you might need to do it from the command line when launching mysqld.exe by using --query-cache-size=20M (i think)

Thanks JohnMC, I'll look into the cache thing for my local server and see if it helps. But I am planning to upload SI to my web hosting account after I add all my invoices (so I can access SI from anywhere) and thus I might not have access to change any settings at all (or maybe they already have a good cache).

But what I would really like to know is why this is so slow. As I said before, the list of invoices in the Customers page loads rather quickly. Is there any chance that SI is loading all the invoices every time but outputting only 15 to the Flexigrid XML? That would be a rather slow way to deal with this.

Okay, here's what I found so far. I haven't tested the cache idea from JohnMC, but I managed to find the actual SQL query that generates the invoice list for use in the Flexigrid XML. Quite a query - no wonder it runs slowly, even when running it via phpMyAdmin. Contrary to what I was thinking, the query does not retrieve all the invoices all the time.

A quick Google search for'mysql performance led me to a page describing the wonders of InnoDB and how it was useful when your queries use a lot of LEFT JOIN. I checked my database and SI was using MyISAM instead of InnoDB. So, I went ahead and converted all the tables to InnoDB. In order to do that, I had to drop 'domain_id' as primary key in almost all tables since InnoDB only allows one field as primary key. I'm not sure if this will cause any problems, but as of now Simple Invoices is FLYING. The full invoices list now loads in just a few seconds instead of 90.

Does anyone else have a big database so we can compare the results?

Wow, that is some VERY useful information, im really glad you found this, i wonder if having a second primary key was apart of the issue in the first place, i think that 99% of us dont use the domain option anyway, let us know if you have any issues, and id love to hear what justin thinks of this.

my database isnt very large but i might try to test it if i can find a good way to time it.

on a different speed related issue, that i found and totally forgot about, I found that using the built in auth system was creating huge issues and switching to htaccess user/pwds has helped ALOT, i thought it had to do with zend at first, but now i just dont know.

oh and the compression has made a huge improvement for me, however it wouldnt do anything on a local host, but my "budget" web server responds like a champ ever since i enabled compression. if you have cpannel on your web server you normally have the option, or you can use the htaccess method

JohnMC, thanks for your comments, I'll try the HTACCESS thing and the GZIP compression.

On a related note, I'm almost done importing my old invoices. The database now is at 5831 items and 1365 invoices. The SI interface has slowed down just a tiny bit as far as I can tell.

Browsing through my old invoicing system's database, I found out that it stores the invoices' total values as a field in the master invoices table (whereas Simple Invoices use LEFT JOINs to calculate it for all the invoices it has to show), much like a cache. Maybe SI could borrow this idea?

hey guys

thanks for all this great info

re invoices - slow
- to make this fast we in myisam we need to add indexes to the various tables
- this has NOT been done so far
-- has been done for the payments page
-- if any one is interested in reviewing the current sql and indexes would be great!!!!

cheers

justin

Justin,
Simply changing from MyISAM to InnoDB provided a huge performance boost without changing anything else at all. So, why not go ahead and make it the default engine for the SI tables? Or, I guess, the other way to speed things up without switching to InnoDB would be to add redundant data to the tables (ie, adding a TOTAL_VALUE column to the INVOICES table, so that SI does not need to use a lot of LEFT JOINs to put together the information).

I'm not a programmer, though. I just happen to know some things about SQL and PHP, just enough to write small things and make changes.

I think the best solution would be to introduce more indexes on the currently using InnoDB tables. I am not an SQL expert otherwise I would have done this already.

thanks guys

unfortunately need to use myisam so can have composite primary keys

think will be bset to add TOTAL etc.. into si invoices table and redo the SQL

plus add some indexes

will add this to our todo list

does anyone have a large sample dataset - if not if someone could create on would be great
- 100s of invoices, products, customers, billers, payments etc..

cheers

justin

Hi, well i've set SI up for 1600 customers, 1600 invoices a year, 2 items per invoice and i can tell you that the page invoices takes a 4minute loading. That's too much so ive changed the query and dropped the calculations. All that is showing now is actions, id, company, customer and date and it's showing within 2 seconds now....
I've had already changed to Innodb but that wasn't enough.

Why are you calculating in a query and not in php?

I love SI, but waiting 4 minutes for a page to show up isnt working for me, deleting the calculations in the query helps but i really hope you can change it....

Alex

I got the sql of Alex which proves to be a lot faster, but after removing the calculations from the sql. But it requires that things are done differently. The sql is as follows, will try to see what needs to be done to get the original functionality.

case "pdo_mysql":
default:
$sql ="
SELECT
iv.id,
iv.index_id as index_id,
b.name AS biller,
c.name AS customer,

iv.type_id As type_id,
pf.pref_description AS preference,
pf.status AS status,
(SELECT CONCAT(pf.pref_inv_wording,' ',iv.index_id)) as index_name
FROM " . TB_PREFIX . "invoices iv
LEFT JOIN " . TB_PREFIX . "biller b ON b.id = iv.biller_id
LEFT JOIN " . TB_PREFIX . "customers c ON c.id = iv.customer_id
LEFT JOIN " . TB_PREFIX . "preferences pf ON pf.pref_id = iv.preference_id
$where
GROUP BY
iv.id
$sql_having
ORDER BY
$sort $dir
$limit";
break;

Hi. We've been using SI for some years now. I recently updated to the latest SI and got this slowness problem. 3-4 minutes wait time to just display the first screen of the invoices page. Around 1400 invoices now. Other sections of SI run fine.

Trouble-shooting this problem before looking it up here, I noticed 100% cpu on the mysqld process, so I knew right off the bat that the major problem was an SQL query.

It shows from the discussion that Justin has no intention to change from MyISAM to InnoDB, but something has to be done as SI really becomes unusable when you're waiting an unbearable amount of time for the invoices page to come back. We started to think if this can't be fixed we may have to look for something else.

So I set about trying the second option, to change the SQL query with the above in ./include/class/invoice.php

Just this change took the page load time from 3-4 minutes to around 2 seconds. Although we now don't have the "Total, Owing, Aging" columns populated, who cares, it's much more important to have the speed of being able to invoice quickly as we do about 30-50 invoices per month.

So I'm just letting you know, if you have any updates to the SQL above or any news, people are interested.

We don't know enough about the MySQL MyISAM to InnoDB conversion of how to do it, so without major web searches and time invested in performing this work, the easier option was the SQL query change above and we're now happy again.

Best regards.

Michael.

Imho, it has nothing to do with MyISAM to InnoDB. InnoDB is transaction aware which could make things even slower. But I haven't tested this. I go for Alex his option and do more in PHP. But there's always the time issue. I don't have much of it :-(

I had problems with speed too. It turned out that adding some indexes solved it.
I also changed engine to InnoDB because i needed to be able to use transactions for a customized extension. The problem now is that i can't upgrade SI because of the composite keys. Applying patches gives me this:

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a keyDude, what happened to your query?:

ALTER TABLE `si_invoices` DROP PRIMARY KEY, ADD PRIMARY KEY(`domain_id`,`id` );

It's patch 210.

Hi Justin and others!

I have been following this particular issue with great interest. We are in the same position - simple invoices, great as it is has become almost unusable. In fact - not almost, it really has become unusable. Especially on the first day of the month when we create around 100 invoices. What should take us 2 hours takes us 2 days simply because of this issue. On top of it our hosting company has started complaining that we bring their server to a standstill when we generate invoices.

We really love this software and the only big gripe really is this issue. I would respectfully say that one should almost halt all other development and focus on this problem. I am not sure if this is as big a problem for other users as it is for us, but judging by the comments in this thread certainly for some it is. I would happily help out but I am hopelessly unqualified to do so. From what I have read and googled, adding indexes at the very least improves it dramatically. I have no idea how difficult or time consuming this is though...

Just my 2 cents!! Thanks for all the effort and for sharing this otherwise great product

Cheers

Theo

Hi, I'm bumping this because I also am seeing a similar slowdown. Has there been any work on this since the last release, or should I apply the patch above by aducom? I'm using the latest version.

Let me be the nth person to say, this software is AMAZING and the nth to say, yeah, use server/PHP and not SQL for everything :-)

whoops error

@Poncho: Can you tell us where you added indexes on the db? tnx

I added indexes on si_invoices.date, si_invoices.customer_id, si_invoices.biller_id, and si_invoice_items.total. The main invoice view is MUCH faster to load now; went from about 3 seconds to generate down to about 250ms. This is just from adding indexes, I did not strip any calculations out of the grid (or otherwise optimise the queries, which pretty clearly could probably use some work).

Tnx, Added the indexes too and indeed it's better.

@jrssystems,ydmlog, can you guys explain how to add indexes and include them in the TPL file? or, basically, the steps (and/or SQL queries) you took to do this? i'm pretty familiar with phpmyadmin, i'm just not sure about the process. thanks much.

If you're using phpmyadmin, click your database, then click the si_invoices table from the left pane, then click the Structure tab at the top, then click "Go" next to "Create an index on [ 1 ] columns". Then type "date" in "Index Name", set "Index type:" to "INDEX", and set "Field" to "date [datetime]". (Leave the Size at its default value). Click save. Now go do the same steps for the other columns I listed in each of their tables.

You won't need to restart mysql or anything; once you've added the indexes the effect will be pretty much immediate.

If you'd prefer to run SQL commands directly in the mysql console, enter the console and:

USE yourdatabasename;
CREATE INDEX date ON si_invoice (date) USING BTREE;
CREATE INDEX customer_id ON si_invoice (customer_id) USING BTREE;
CREATE INDEX biller_id ON si_invoice (biller_id) USING BTREE;
CREATE INDEX total ON si_invoice_items (total) USING BTREE;

Note that if you already have any of these indexes present for one reason or another, you'll get a (harmless) error notifying you of that fact. Also note: this will write-lock your tables and maybe read-lock them as well, depending on your version of MySQL, until the index rebuild is complete. That said... it's INSANELY unlikely that you've managed to accumulate enough data in SI to make for any significant amount of time building indices on these columns; I've got about a thousand invoices in my database and my indices built literally before I could go back into the browser tab to my app and see if they'd helped. I do have a dedicated server, though, so your mileage may vary somewhat if you're using SI on a cheap shared host.

jrsystemsnet, thanks for the tip. I've added the indices to my database but it is still slow (takes about 6-7 seconds to load the main invoice screen; it's rather fast, but not as fast as I wanted it to be). I'm running SI on Xampp and I have 1,683 invoices, 7,426 invoice items and 1,726 payments.

Yeah, it's still dog slow. Still, 6-7 seconds beats the heck out of 90 seconds - 4 minutes, right? =)

Beyond here, the queries really need to be optimized. Which I don't have the time for right now, especially since my server can spit out the page in 250ms or so. It's not quite itchy enough for me to scratch yet.

Another thing that helps is having APC installed (which I do, on my server). SI unfortunately does not directly make use of any PHP/MySQL cache applications, but php-apc still provides a noticeable benefit to performance even with scripts not written for it.

If you can figure out how to install it on your platform, you might want to give it a shot. (On Debian or Ubuntu Linux, "apt-get install php-apc" is pretty much the whole procedure.)

@jrsystemsnet: sure, 7 seconds is way better than 4 minutes, but let me tell you: before adding the indices I simply changed my tables to InnoDB, and since then the invoice screen is coming up after those 7 seconds. That is, the indices did nothing to speed things up.

Anyway I agree with you: queries need to be optimized. For instance: I have no idea why SI should be calculating invoice totals on-the-fly. I mean, why not just store invoice values (paid, total, etc) in the invoice tables itself, and then add a refresh option to be used just in case something goes wrong.

@denisgomes: because storing sums in a cell is a violation of first normal form, and because it really shouldn't be even close to necessary with the volume of data an SI install will be working with.

the problem here is that the queries themselves are extremely inefficient, and that the original database is in place with NO indices whatsoever. (For the record, your conversion to InnoDB meant that you gained almost all of the benefit of a declared index - like the indexes I was declaring - automatically, particularly since, again, we're talking about RIDICULOUSLY small amounts of actual data.)

Doing a SELECT SUM across a paltry few thousand rows should be so fast nobody would ever notice it. I work with MySQL databases in the hundreds of gigabytes that return results faster. SI's queries are just plain B-A-A-A-D-ly written.

Is it possible for someone in the know (such as Jrssystemsnet :-) to rewrite those queries? I would be happy to contribute to some form of bounty. As mentioned before, it has become unworkable for us - I literally spent two days invoicing people on the 1st of March because of this. It takes up to 4 minutes to load the invoicing page, indexing made no difference whatsoever for me unfortunately. It has become quite urgent for us to have this resolved and it really is the only thing that is not working for us.

But as I said - to such an extent that we have to stop using it...

hi guys

can someone send me a very large dataset so i can review this - you can strip our the names, products etc..

re bounty
- thanks - please post or email me the amount you guys would contribute to getting a real fix
- the more i can raise the better & sooner the fix will be

cheers

justin

I've got the SQL, if somebody else wants to tie it into the codebase:

select 
	si_invoices.id, si_invoices.date, si_biller.name as biller, si_customers.name as customer, 
	(select sum(total) from si_invoice_items where si_invoice_items.invoice_id=si_invoices.id group by si_invoice_items.invoice_id)
	as inv_total, 

	(select coalesce ((select sum(ac_amount) from si_payment where si_payment.ac_inv_id=si_invoices.id group by si_payment.ac_inv_id),0))
	as payments, 

	(
		(select sum(total) from si_invoice_items where si_invoice_items.invoice_id=si_invoices.id group by si_invoice_items.invoice_id)
		- 
		(select coalesce
			(
				(select sum(ac_amount) from si_payment where si_payment.ac_inv_id=si_invoices.id group by si_payment.ac_inv_id)
		  		,0
			)
		)
	) 
	as owing,

	IF(
		((
			(select sum(total) from si_invoice_items where si_invoice_items.invoice_id=si_invoices.id group by si_invoice_items.invoice_id)
			- 
			(select coalesce
				(
					(select sum(ac_amount) from si_payment where si_payment.ac_inv_id=si_invoices.id group by si_payment.ac_inv_id)
			  		,0
				)
			)
		) > 0),
		(DATEDIFF(CURDATE(),si_invoices.date)),
		0
	)
	as days_due

from 
     (si_invoices, si_biller, si_customers) 

where 
     si_biller.id=si_invoices.biller_id and si_customers.id=si_invoices.customer_id 

order by 
     owing DESC limit 15;

This executes in < 30 ms on my server, as compared to 300ms or so for the query that SI is actually using. All you need to change is the ORDER BY at the end to sort by a different field, and add a LIMIT (if desired) to only output the first few rows for display in the flexgrid.

Conversions of NULL to zero are handled in the query, and days_due is zero unless there is an amount left unpaid on the invoice.

Sample output using ORDER BY owing DESC limit 5:

+------+---------------------+----------------------+-------------+------------+----------+---------+----------+
| id   | date                | biller               | customer    | inv_total  | payments | owing   | days_due |
+------+---------------------+----------------------+-------------+------------+----------+---------+----------+
| 1017 | 2011-03-02 00:00:00 | JRS System Solutions | D[redacted] | 2150.00    |     0.00 | 2150.00 |        2 |
| 1015 | 2011-03-01 00:00:00 | JRS System Solutions | Z[redacted] | 2000.00    |     0.00 | 2000.00 |        3 |
|  965 | 2011-01-01 00:00:00 | JRS System Solutions | Z[redacted] | 2000.00    |     0.00 | 2000.00 |       62 |
|  986 | 2011-01-19 00:00:00 | JRS System Solutions | Z[redacted] | 2000.00    |     0.00 | 2000.00 |       44 |
| 1003 | 2011-02-20 00:00:00 | JRS System Solutions | B[redacted] | 1250.00    |     0.00 | 1250.00 |       12 |
+------+---------------------+----------------------+-------------+------------+----------+---------+----------+
5 rows in set (0.03 sec)

Sample output using ORDER BY days_due DESC limit 5:

+------+---------------------+----------------------+-------------+------------+----------+---------+----------+
| id   | date                | biller               | customer    | inv_total  | payments | owing   | days_due |
+------+---------------------+----------------------+-------------+------------+----------+---------+----------+
|  630 | 2009-05-12 00:00:00 | JRS System Solutions | R[redacted] | 1579.00    |  1360.00 | 219.00  |      661 |
|  651 | 2009-06-01 00:00:00 | JRS System Solutions | R[redacted] |  880.00    |     0.00 | 880.00  |      641 |
|  683 | 2009-08-18 00:00:00 | JRS System Solutions | R[redacted] |   75.00    |     0.00 |  75.00  |      563 |
|  690 | 2009-08-28 00:00:00 | JRS System Solutions | R[redacted] |   75.00    |     0.00 |  75.00  |      553 |
|  813 | 2010-05-01 00:00:00 | JRS System Solutions | R[redacted] |  140.00    |     0.00 | 140.00  |      307 |
+------+---------------------+----------------------+-------------+------------+----------+---------+----------+
5 rows in set (0.04 sec)

Note that these queries are executing in 40 ms or less - and that I'm deliberately giving examples of sorting by calculated aggregate fields from subqueries. My tables are still MyISAM, though they do have indices on the columns I noted in the posts above (which shouldn't interfere with any future SI upgrades). These execution times are roughly an order of magnitude better than what I'm seeing from the performance on the actual flexgrid in use.

This is against a database with, currently, 388 invoices, 369 payments, and 552 invoice items in it.

@denisgomes: want to try running this query manually against your larger database? I'd be REALLY surprised if it takes more than 70ms or so to execute, assuming you're running on a dedicated (or at least relatively lightly loaded) server.

OK, I created a test database with random data. It's got 5000 invoices in it, 52097 invoice items, 100 customers, 5 billers, and 9830 payments.

Sample output of my query, ordered by days_due DESC limit 15:

+------+---------------------+----------+-------------+-----------+----------+---------+----------+
| id   | date                | biller   | customer    | inv_total | payments | owing   | days_due |
+------+---------------------+----------+-------------+-----------+----------+---------+----------+
| 2056 | 2006-03-01 00:00:00 | biller 0 | customer 51 |   2380.00 |     0.00 | 2380.00 |     1829 |
| 1898 | 2006-03-02 00:00:00 | biller 1 | customer 4  |   1608.00 |  1583.00 |   25.00 |     1828 |
| 2702 | 2006-03-02 00:00:00 | biller 2 | customer 34 |    507.00 |    53.00 |  454.00 |     1828 |
| 3663 | 2006-03-02 00:00:00 | biller 2 | customer 10 |   2215.00 |  1869.00 |  346.00 |     1828 |
| 2950 | 2006-03-02 00:00:00 | biller 3 | customer 87 |   3377.00 |     0.00 | 3377.00 |     1828 |
|  534 | 2006-03-02 00:00:00 | biller 2 | customer 96 |   4380.00 |  2891.00 | 1489.00 |     1828 |
| 1040 | 2006-03-03 00:00:00 | biller 1 | customer 46 |   1356.00 |  1206.00 |  150.00 |     1827 |
| 1323 | 2006-03-03 00:00:00 | biller 1 | customer 67 |    376.00 |   368.00 |    8.00 |     1827 |
| 4720 | 2006-03-03 00:00:00 | biller 0 | customer 45 |    776.00 |   741.00 |   35.00 |     1827 |
| 2959 | 2006-03-04 00:00:00 | biller 3 | customer 63 |   2475.00 |  2448.00 |   27.00 |     1826 |
| 1622 | 2006-03-04 00:00:00 | biller 1 | customer 10 |   2304.00 |  1979.00 |  325.00 |     1826 |
| 3597 | 2006-03-05 00:00:00 | biller 0 | customer 5  |   4918.00 |  4752.00 |  166.00 |     1825 |
| 1410 | 2006-03-05 00:00:00 | biller 2 | customer 84 |    896.00 |   893.00 |    3.00 |     1825 |
|  981 | 2006-03-06 00:00:00 | biller 3 | customer 15 |    200.00 |   115.00 |   85.00 |     1824 |
|  472 | 2006-03-06 00:00:00 | biller 3 | customer 44 |   1266.00 |     0.00 | 1266.00 |     1824 |
+------+---------------------+----------+-------------+-----------+----------+---------+----------+
15 rows in set (0.58 sec)

I believe that's a considerably larger database than anybody here is actually using, and it's still returning the grid in roughly half a second. (The execution time is the same if you order it by the other worst case, owing - I'll spare you the paste of the output.)

Ultimately, of course, this isn't going to cut it either - eventually, if they use it long enough, people are going to /have/ to have a way of archiving old data so that it isn't considered for the queries for the interface and reports by default. Still, this is a metric ton better than what we've got now. Somebody just needs to patch the code to actually use it. I'm hoping somebody else will do that part, now that the SQL for it is posted here. =)

To demonstrate the importance of proper indexing, I reverted my tables to SI standard (ie, dropped the indices that I'd added, documented above) and ran the flexgrid query against the 5,000 invoice test database:

15 rows in set (17 min 21.24 sec)

17 min 21 sec (without indexes) vs 0.58 sec (with indexes). So if you're still on the fence: yes, you REALLY want to add indexes to your tables as outlined above.

Finally, I generated a MUCH larger test database with 50,000 invoices, 523689 invoice items, and 97955 payments. Execution time for my flexgrid query, with my added indexes: 5.88 seconds.

Note that this is against the ENTIRE database - the LIMIT 15 doesn't help when you could potentially want to ORDER BY aggregate fields from si_invoice_items; you can't figure out how to order them without reading in every single row anyway. On the other hand, if you artificially limit the scope of the query - say, by dropping in a WHERE clause to only look at the last 1,000 invoices - you drop your execution time back to around 10ms.

@jrssystemsnet: i'm on a road trip right now. I'll try your query as soon as I get back. I'll also post speed results of the original query vs your query. Thanks for your contribution.

jrssystems
you're great - i added the indices and noticed a small shift, but unfortunately i'm not expert enough to add the sql to the codebase. here's hoping somebody will!!!

I've been following this thread with great interest. I'll notify Justin as he should do this.

Just installed php-apc and indeed noticed speed up's, tnx didn't know this.
And PS, on Debian or Ubuntu Linux, the right command is "apt-get install php-apc"

Kind regards

d'oh - sorry about the typo, there, ydmlog; thanks for the correction =)

Good to see some movement here. I am the one who suggested a bounty - it all depends on how many people are willing to contribute. I am happy to throw U$20 into the hat. If not enough people are forthcoming, I will consider increasing it.

@jrsystemsnet: just came back home and tested your suggestions. I think there may be something wrong with my installation. Here's the situation so far:

1,688 invoices, 1,730 payments, 7,433 items
Database has the indices you suggested
Your query on MyISAM: (took too long - I just canceled it)
Your query on InnoDB: 40 secs (returned 15 records)

Rather quick, but not as quick as your test with the larger database. I'm on a local server right now (XAMPP).

@denisgomes: Yeah, something certainly sounds screwy. The server I'm running on is nothing particularly special - leased box with an AMD X3 processor, 2GB RAM, generic 7200rpm SATA drive (250GB WD, I think?) And you're taking almost ten times as long to return from a query on a database almost 50 times smaller than my larger test db.

"xampp" covers a lot of territory. Are you talking about the Windows apache/mysql/perl/php package? That might be a large part of the problem, if so. Windows doesn't really make a good server platform.

Yeah, it is Apache+MySQL+PHP. I was using a shared web hosting before to run SI, it was not anything faster than this, but it wasn't slower either.

You're kinda screwed both ways there. Shared hosting platforms tend to be pretty resource-starved, and AMP under windows tends to be kind of dubious by comparison with hosting on linux.

If you can afford to dedicate a machine to the purpose of running SI, I'd definitely recommend going Debian (or Ubuntu, if you prefer) rather than continuing with the Windows route.

One caveat here is that it's certainly POSSIBLE for AMP to run better on Windows than what you're seeing - but in addition to the necessity of porting to the Windows platform, you're then at the mercy of package managers that also aren't serving as large (or as serious) of an audience. "Easy mode" here really is just to go ahead and run Linux.

I think the same. IMO xamp is good for development and some basic testing, but not for production environments. Install a linux box running apache,mysql,php and you'll be so much better off.

Kind regards

Just a side note: I've uploaded my DB to my shared hosting server (Linux + MySQL). jrssystemsnet's query ran on 15 seconds vs. 40 seconds on my own machine. I can't help but think that there must be something else in place so as to achieve that half-second performance...

@denisgomes: perhaps you're disk-bound and I'm not - I've got plenty of RAM available, enough so as to keep the majority of the tables cached on the filesystem level. I also have the query cache setting on my mysql installation itself tuned up fairly high (100MB if I recall correctly).

I am revisiting this thread in the hope of getting some finality on this. I am losing the fight at our company with regards to no switching to freshbooks. I prefer keeping it with simple invoices but the people actually working with it are giving up because of the time it takes to generate invoices. We've compared with Freshbooks - What took 4 hours of work with simpleinvoices, took 30 minutes with Freshbooks. The only reason was that after an invoice is generated we can go and make coffee before the list of invoices appears and we can make the next one. It is also causing things to be forgotten as typically while waiting for the page to load people do something else and get sidetracked.

I know too little about databases to understand all that has been said here. Is this a thing that can actually be fixed? I am more than happy to pay someone to do this if necessary, seeing that Freshbooks will cost us 20 bucks a month. If it can be fixed, can this be done in the very short term (like in this week) or am I dreaming and must relent?

Thanks for any input.
Theo

@mlungu,
you might want to try first myclientbase.com and see if suit your needs.
It's fast and is also growing at a faster pace than SI.

Hi Beipink and others

Thanks for pointing me in that direction. We've made the decision to give that a go and so far all is well.

Granted the database is not nearly as large yet on myclientbase but so far it is chalk and cheese in speed. I am very sad to leave simpleinvoices behind as I like it and have always had great help from Justin. However, business decisions have to be made.

Thanks for developing simpleinvoices, it has served us well.

Cheers

Theo

It sure looks well. I'm not sure about 'at a faster pace' though (from the wiki):

"The project is and has always been actively developed by a single developer, but it is always open to the suggestions of the community and it implements reasonably suggested features and enhancements that the community provides."

It's a one man show,

there are several active developers here, but agreed, we haven't got all the time in the world.