Thank you.
In meantime I discovered that the issue is not the 64 bit operative system but the access version.
The database is done in access 2007, If I run it with access 2010 - 64 bit machine it is ok and it take one minute (10 seconds more), but with access 2013 not: take more than 5 minute to make the same elaboration.
I uploaded the file called dispogaro01tes.mcc
I'm really not an expert of Access and to be honest, I fear a little bit send my "code" to this forum full of very Expert person, but I like Access very much it and at the end, is producing the result I need.
Frigo1
Frigo2
Frigo3
Calc
I delete all the unnecessary data keeping just 2 varieties to make a real test.
(in reality the varieties are more than 100 ).
background:
I work for an young plants company that is producing cuttings (Poinsettia, Pelargonium, Carnation, other ) from mother plants.
Some of these young plants (Pot carnation or carnation ), can be stocked for a certain period in the cool store (4 weeks).
Every week cuttings are collected from the greenhouses in plastic bags and moved inside the cool store to be stocked.
The goal of this tool it is calculating the residual availability to be shown to sales force after the collection of some orders.
Unfortunately the fact that can be stocked in cool store make the calculation of weekly availability extremely complicated.
(for no cool store product is very easy jut Availability – Orders in each week).
Data is available as in table frigo1:
| WWFRIGO01 | |||||||||||||
| SMDS03PIN | TBTP01PIN | TBTP01cod | DBMA01PIN | DBMA01cod | DBMA01des | Gr | SubGr | SMDS03IAN | SMDS03ISE | SMDS03FAN | SMDS03FSE | SommaDiSMDS03QDS | SommaDiSMDS03QUS |
| 10418 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 25 | 2014 | 29 | 9349 | 4517 |
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 30 | 9349 | 2350 |
| 3071 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 27 | 2014 | 31 | 9349 | 9349 |
Where the variety 2035 has 3 records
Quantity of 9349 entered in week 25-2014 that is good up to week 29 -2014
Quantity of 9349 Enter in week 26-2014 good up to 30 -2014
-
Quantity of 9349 Entered in week 27 good up to 31-2014
we need to know the residual availability after that some order are collected
Each time that we have one order for one delivery week, we need to engage the bags starting from the oldest one (but still valid) .
This mean that if we have one order of 9349 delivery week 30, we should start to engage the one numbered as 20687 (second record in the example) because oldest respect the one numbered 3071 that also we could take for this week.
(the first record is excluded because too old in week 30 when we need).
To calculate the residual availability I exploded the table wwwfrigo1 in table wwfrigo02 (function frigo1)
Where I have one record for each week (in wwwfrigo1 I have one record that can be used in a range of week)
| WWFRIGO02 | ||||||||||||||||
| SMDS03PIN | TBTP01PIN | TBTP01cod | DBMA01PIN | DBMA01cod | DBMA01des | Gr | SubGr | ANNOiniz | settimanainiz | ANNO | settimana | Q | Qinit | Qres | flag | processati |
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 30 | 9349 | 6999 | ok | ||
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 26 | 9349 | 6999 | ok | ||
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 27 | 9349 | 6999 | ok | ||
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 28 | 9349 | 6999 | ok | ||
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 29 | 9349 | 6999 | ok | ||
In this way it is easier for me query and engage the bags against the order starting from the older one.
(unfortunately I did not find the way to query directly the table wwwfrigo01 to take the oldest bag for one delivery week)
This operation is performed using the function frigo2 and at the end I produce a table wwwfrgo05 with two column: potential availability and residual availability after that some order engaged starting from oldest bags.
The problem now is that the residual availability it is not really telling me how much order I can enter in one week without going in overbooking.
The quantity I see as residual availability underestimate the real possibility to collect orders because the engagement of bags can deeply change depending on order that we insert.
In one week I can see 0 because all the bags of this week are engaged following the criteria to take the oldest bags but in reality I can still place order without creating overbooking due to the rearrangement of use of the bags after that I introduced my additional order.
This is the reason why there is function frigo3:
This function try to insert in each week (Keeping all existing order) a quantity and stop when overbooking is created
This is repeated for each week and the result is table wwwfrigo10 , Where in column dispo2 I have the maximum quantity is possible insert without increase overbooking.
It look like this function is very slow in Access 2013 (64 bit) but acceptable in Access 2007 (32 bit)
Valentino-
There could be many reasons, but seeing your code might help you solve the problem.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Jun 2, 2014, at 3:57 PM, Valentino Avvisati valentino.avvisati@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John,I created table importing data from As400 using ODBC I series . I notice now that almost all numeric fields were imported as Decimal fields very often with precision 28. In reality the data inside these fields can be integer or long integer (like the fields that indicate the year or the week for example).I used the table like they were imported and in addition, when I read and process the data using vba, I did not declare correctly all variables …leaving Accees to manage them .
What I will try now is convert decimal fields in integer fields and I will declare the variables in correct way in order to optimize the use of the memoryIf after this still I have the problem, I will post the code
Could be that at the end, back to this problem, there is my bad programming that do not come out in 32 machine but come out with 64 Bit machine that run software 32 bit
Thank you
Valentino
2014-05-31 22:23 GMT+02:00 John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>:
Valentino-
The two 64 bit machines should be faster.You would need to post a copy of your code to see if we can spot any problems that would be slowing it down.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On May 31, 2014, at 4:46 PM, Valentino Avvisati valentino.avvisati@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:Hello John-Thank you for the answerI'm not really an expert but an "heavy" user of Access that is learning by doing, from this group and your books …I used my laptop (windows 7 32 bit when I built the application ) and windows XP where I run some scheduled process that produce some reportIn both where ok,
After I moved the application to two machine 64 bit:One is virtual machine with Intel xeon CPU E5620 2.40GHZRam 2 GB (effectively not too much)The other was a Real machine with windows 7 64 bit intel core I7 3,30 GHz8 Gb ram, much more powerful of the machine where I do not have issue that is anWindows Xp Dell AMD Athlon 64 x dual Core processor 4200+2,20 GHz 33 GB ram
Mainly the procedure make d Do loop cycle on a table to take one key code after I use this code to create query using db.CreateQueryDefThe procedure create open and close many time a queryIt look like this is very slow in 64 bit environment it is very slowIt is also true that I did not declare all variable, I'm not an expert and probably I also made some mistake that decrease the performance and use a lot of resources, but up to this moment I did not have any problem and I do not have problem in 32 bit.
2014-05-31 9:02 GMT+02:00 John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>:
Valentino-
When you run a 32-bit application on 64-bit Windows, there's extra overhead having to "thunk" all the 32-bit address to actual 64-bit and back, but it shouldn't be that noticeable. What are the other characteristics of the two machines? How much memory on each? How fast is the processor? Access is particularly sensitive - especially when running complex queries - to the amount of memory available. In earlier environments (Win 98), you could often double the speed of Access simply by adding more memory.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On May 31, 2014, at 8:28 AM, 'valentino avvisati' valentino.avvisati@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:Good morning,
I have built an application in microsoft access 2007 that is doing some elaboration (repetitive query and successive elaboration on data extracted)
The database is divided I two file, (the second file just stock some tables ad Is located in same directory of main file mainly because , during the elaboration, the file increase very fast ii dimension)
The procedure run In around 30 minutes in windows xp 32 bit environment and also under windows 7 32 bit. This is ok for me.
the same file moved to windows 8.1 office 2013 (or also windows 7) but 64 bit machine , take so long time that I always stop before end (4-5 time more)
I'm not sure if the problem is really the 32 bit environment or due to the fact that I do not follow all the rules to be efficient, but it look like is appearing only when I move to 64 bit machine
Somebody knows why this occur and possible solution?
Thank you to all (really very useful group)
Valentino Avvisati
Posted by: Valentino Avvisati <valentino.avvisati@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar