Work by 24 h

Ostry

Hello every one.



Our company works on Nav by 24 h/day. All scripts (inventory costing, inventory posting, actualization of dimensions) takes about 2 hours, at normal work. If I logoff all users it takes half hour. Of course when I do scripts nobody can’t work. It is normal in nav? I can’t stop company for two hours. I’m writing about 3-6 concurrent users and 20 GB database. The same is when some one make a big report. Nobody can’t work. Can I make scripts and post invoices? I thought about mirroring or replication. It is possible to do scripts and reports on second database? Maybe some one have idea how can I solve this problem

jlandeen

Have you taken a look at using the Targit/Business Analytics granule that is available with Navision? This works on top of SQL Analysis services and could be used to offload some of the reporting from your Navision database - so that your transactional database is focused on transactions only.

nunomaia

Many of those batch jobs you are referring, locks tables during running, meaning that no user can use those tables. Has jlandeen said, you can have a second database to make reporting and analysis but you can’t solve the problem of costing batch reports. You could try to run reports more frequently or make automatic costing adjust during invoice posting. This can also lead to performance problems during posting.

Waldo

You'll have to do some development, I guess. It's unacceptable to run (for example) inventory costing for 2 hours ... . An idea could be to run x number of times the inventory costing .. one item at a time. Other processes could be run in the same way.

My first feeling is that all should work in one database ... . Just try to keep transaction as small as possible.

And may be ... try to use a very well tuned sql database.

cnicola

Waldo wrote:

You'll have to do some development, I guess. It's unacceptable to run (for example) inventory costing for 2 hours ... .

Hi Waldo,

Perhaps I should introduce you to my database where the Adjust Cost runs for about 5-6 days straight And trust me it is a well tuned SQL db.
Sometimes it can run a long time if you have a weird way of doing things.
To be fair we haven't run adjust cost for a month since it was never finishing in a weekend but still.
So what I did was change the routine to run by item and I am doing a batch every night.

Waldo

cnicola wrote:

Waldo wrote:

You'll have to do some development, I guess. It's unacceptable to run (for example) inventory costing for 2 hours ... .

Hi Waldo,

Perhaps I should introduce you to my database where the Adjust Cost runs for about 5-6 days straight And trust me it is a well tuned SQL db.
Sometimes it can run a long time if you have a weird way of doing things.
To be fair we haven't run adjust cost for a month since it was never finishing in a weekend but still.
So what I did was change the routine to run by item and I am doing a batch every night.

Yes, I know, I was just pointing to the fact that in his production database (he works 24/7, so I assume that he does a lot in Item LE and Value Entry), it's unacceptable to lock tables for 2 hours ... .

It is quite a simple customization to do it item-per-item, and it might be a solution for him (and for you ).

ara3n

here is the modification to filter by item no.

http://www.mibuso.com/forum/viewtopic.php?t=18943&highlight=

Waldo

I didn't know it was already on the forum somewhere.
Nice job, Rashed. =D>

cnicola

I knew Rashed's answer was out there. Sadly though I am using 3.60 objects and the code is slightly different there.

Miklos_Hollender

I think the simple approach for batch jobs is to have these things run when transactions are posted - this slows every transaction down a bit, but never halts the system:

- set analysis views to update automatically
- use automatic cost posting
- use automatic inventory adjustment - I believe it's available from 4.01 or so (not sure, it could be that from just 5.0 which wasn't released for Central/Eastern Europe, unfortunately)

kine

cnicola wrote:

I knew Rashed's answer was out there. Sadly though I am using 3.60 objects and the code is slightly different there.

And one problem is, that many tools to optimize running are available just in newer versions... :?

Ostry

Thx for your answers.


Why I think about second database. Becouse I have about 100 items, but I have 90% selling and buying on 4 items. The rest is not important.
When I did how Miklos Hollender told one invoice post for 2 min.

Ofcourse I did optymalization on database. I thought about BA but we have BE version and before buy BA we must upgrade to AM.
I think I have only one problem locking tables when scripts working. Maby I can make some temoprtary tables? Is it posible?

jlandeen

Just one quick point on business analytics...they run on Analysis Services of SQL 2000 or higher. There's nothing preventing you from building your own custom solution. If you've got the IT resources & know how it may even be better to do that they try & work with the canned solution provided by Navision. As long as your database is already on SQL you're good to go.

Waldo

kine wrote:

cnicola wrote:

I knew Rashed's answer was out there. Sadly though I am using 3.60 objects and the code is slightly different there.

And one problem is, that many tools to optimize running are available just in newer versions... :?

That's one of the reasons why one should upgrade .

p.willemse6

keep in mind that 2 database also require 2 licenses!

anuragatish

why the adjust cost batch job this much time????

we have a DB size of 65gb and item card with more than 36000 items.

and we finish our batch job in less than an hour. and we run it every night. \:D/

we worte a small CU , that runs to find out only those items used in current dates transaction. we see in ILE and VLE for the item's involved in todays transaction. and then the effective number of items come down substantially.