My Experience.. We post around 1000 orders a day - relatively small (just a few lines each).
It was taking Approx 11 seconds an order to post (Using Batch Post)
But that translastes into 3 hours of continuous posting.
By optimizing Tables 32 & 5802 (Item Ledger Entry & Value Entry) the speed has increased to 4 seconds an order, which is well acceptable to us (Old system don't ask). It's a dramatic difference, what is that a 60+% increase. \:D/
Just figured I'd pass it on to those using Native database (I don't know if these two tables effect SQL the way it does Native) feel free to add in any tips that helped speed up your system. 8)
for comparision purposes:
Table No. Table Name No. of Records Record Size Size (KB)
32 Item Ledger Entry 6381131 824 5133896
5802 Value Entry 6698127 812 5308480
Comments
I ask because Native uses the 'free' space for the snapshots that allow it's transaction consistency to work but SQL uses the log file for that purpose. AFAIK SQL only uses free space when it needs to allocate new pages for a table (insert, reindex etc etc) so I don't see why a low 'free' space should be a problem. (OTOH extending the SQL DB is expensive so it should happen when the system is otherwise idle)
TVision Technology Ltd
Correct this is only valid for native. Free space is critical also in SQL but for very different reasons as you point out.
But this thread is about native not SQL. :whistle:
TVision Technology Ltd
Well I had three _and_ a red bull and it still hasn't helped me much, BUT I know Harry and his system, so I had better background 8)
Harry has the system everyone should have; it works and it fits his requirements. \:D/
http://www.BiloBeauty.com
http://www.autismspeaks.org
That's what I would have expected I know you run your system properly, which is why I really am surprised that it made such a big difference. :-k
I have a customer with 20000 open invoices. Posting is slow.
Once I post a payment to close them all (each month)
the speed increases dramatically. It didn't have to do with optimizing those tables afterall.
Is this normal? It doesn't have any applies-to filled in.
The only solution I see now is to have them pay weekly so it doesn't build up so high!
http://www.BiloBeauty.com
http://www.autismspeaks.org