Inventory worksheet taking 1 week to generate

[Microiz]

I have 2000 locations and 3000 items. what can I do to speed it up. I have an IBM X206 Server with 1Gb RAM, 3.0Ghz Processor. Windows 2003 server and Pervasive 9 DB.

Help.

 

[ettienne]

1 week, you're joking right?
PSQL 9 is somewhat slower than previous versions, I did not think that much slower.
Have you installed PSQL SP2?
1GB RAM for a server is low nowadays, I would go with at least 2GB, more if possible. Then you can increase the PSQL cache and speed things up.
The processing speed also depends on the workstation specs and network config, unless you are running the process on the server.
If you are generating worksheets for all locations and all items then I would expect it to take a long time (2000 * 3000 = 6,000,000 records). Can you eliminate some locations or items?

 

[EtienneG]

Wow - 3000 locations. There's a good reason, right?

Sounds like you should be running SQL rather than PSV.

 

[dgillz]

Does ACCPAC not run on MS SQL Server? Pervasive is notoriously slow.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[EtienneG]

Up to a certain level, depending on what you are doing, PSV is actually faster than SQL. Yes, ACCPAC will 'run on' Pervasive, SQL, Oracle, DB2.

 

[ettienne]

Actually you may see a decrease in processing speed if you switch to MS SQL. MS SQL is notoriously slow with database inserts, generating inventory worksheets is inserting records all the way, so that process will certainly slow down with MS SQL.
"Pervasive is notoriously slow." - urban legend, not true.

 

[Microiz]

I have a warehouse that has
columns A to I
rows 1 to 8

items that are received in 50kg as unprocessed.
Then processed into 25kg, 10kg, 5kg, 1kg.
Processed items have 3 states
a) Passed
b) Failed
c) Lab (waiting for lab results)


Each items is represented as
1. unprocessed in 50kg
2. Processed Passed in (25kg, 10kg, 5kg, 1kg)
3. Unprocessed failed in 50kg
4. Processed failed in (25kg, 10kg, 5kg, 1kg)

There are bills of materials for converting from one form to anather.

 

[Microiz]

And each item has 9 grades.

This explains how I got to 3000 items.

Should I do the stock take outside accpac?

Help

 

[ettienne]

3000 items is not a problem, I have had sites with 1.5 million items, and many sites that exceed 3000 items.
It's the number of locations which is really the issue.
Why do you have 2000 locations?
You could write your own stock take routine, but do you really want to?

 

[Microiz]

I have got 19 warehouses each with bays that have been set as locations. the total number of locations is 2000. items in these warehouses do not have defined bays were they sit. an item can be found in a location (bay).

Is the a better way of doing this?

 

[ettienne]

Ouch, that's the worst thing to do is setup bays as locations, nasty. I suspected that was what is going on.
A location in IC should be the wharehouse, not a bay.
If you want to track quantities in bays then you should look at something like Lot Tracking, each bay you track as a lot.
This will reduce your number of IC locations and improve your worksheet generation drastically.
I have used Lot Tracking in this way very sucessfully, and with a couple of custom reports you can get good info out.
With Physical Inventory (and receipts and shipments) you are prompted which Lot (or bay in your case) the transaction relates to.

 

[Microiz]

That is a good suggestion but, the items that I track have lot numbers which also need to be tracked using lot tracking. So a bay cannot be a lot. The item lots that I track can be located on 2 different bays (each bay filled by part of a lot).

 

[ettienne]

That's not a problem either.
I've had the same situation with another company. In Lot Tracking you can format the lot number. I set it up so that the lot number has 2 segments: LOT-BAY (or BAY-LOT).

 

[Microiz]

Sound alright, If I transfer items from one bay to another or from one warehouse (location) to another then I have to edit the lot number.

 

[ettienne]

That is correct, either way you need to do something.