Speeding Up SO Posting Native**Updated!

Savatage

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

kapamarou

They do affect SQL because they are 2 of the "heaviest" tables in NAV. Optimizing them is crucial but you also need to figure out what you need to change (too many keys and flowfields on these tables). I haven't had a chance to work on an optimization process for these tables on a "large" DB 5.1 or later using VIEWS. Any experiences on this are welcome on this post... Also how do they perform on SQL 2005 v SQL 2008? (I don't expect my old 3.7 heavily customized client upgrading any time soon in order to have some hands on experiments)

David_Singleton

Optimizing shouldn't actually make inserts faster, but can make reads faster. It is possible that you have too many keys or SIFTs on them but they are both under 1k which is good. My guess would be that the speed came becasue you were low on free database space. What percent of free space did you have before and after. You want about 25% free, 20 is OK but if it gets below 10 then the system will really start to crawl.

rdebath

David Singleton wrote:

You want about 25% free, 20 is OK but if it gets below 10 then the system will really start to crawl.

I know this is true for Native but is it also so for SQL?
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)

David_Singleton

rdebath wrote:

David Singleton wrote:

You want about 25% free, 20 is OK but if it gets below 10 then the system will really start to crawl.

I know this is true for Native but is it also so for SQL?
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)

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:

rdebath

David Singleton wrote:

But this thread is about native not SQL. :whistle:

OOps I saw SQL in the first message ... of course I was only half way through my first coffee this morning

David_Singleton

rdebath wrote:

David Singleton wrote:

But this thread is about native not SQL. :whistle:

OOps I saw SQL in the first message ... of course I was only half way through my first coffee this morning

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/

Savatage

I generally keep it at 20% free space with those tables unmodified.

David_Singleton

Savatage wrote:

I generally keep it at 20% free space with those tables unmodified.

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

Savatage

UPDATE: The number of open invoice affects the posting speed.

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!