Hi Harry,
The process can be summarized as follows :
while select custtable notexist join accountstatement
find balancerecord for customer in accountstament for current period
(current period = blank period)
store balancerecord in oldaccountstatement
calculate balance for current period
find new customerstatus in definitiontable based on balance
set new status to customer and update custtable
update blank period to closed period (e.g blank -> 200512)
insert new balancerecord(s) for customer in accountstatement for open
period (period = blank)
the accountstatement table is a new table which consists of all
transactions made to the customer (sales orders, deliveries, invoices,
payments) so we can retrieve the complete history of the customer in
this table. (at this moment the table is about 2.000.000 records, but
once everything is live, we will look at figures between 20&30.000.000
records)
We see this behaviour also in other processes which are more complex
to describe. This one is a fairly simple proces and is easy to simulate.
In your reaction I make up you have encountered such behaviour
already. So, I'm very curious !
Regards,
Stefan
--- In Axapta-Knowledge-Village@yahoogroups.com, "Harry Deshpande"
<[EMAIL PROTECTED]> wrote:
>
>
>
> Hi
>
> From which tables are u getting the information from? Do you do any
> updates on these tables like mark a record that it has been updated?
>
> What are the tables into which you are inserting the data?
>
> It would be helpful if you could write in detailed description.
>
> The problem you have is not unusual and yes, it can be solved.
>
> Regards
>
> Harry
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights. Use of included script samples are subject to the terms
> specified at http://www.microsoft.com/info/cpyright.htm";
> The code provided (if any) may be written from the point of view of a
> third party and the programming style/development standards may suit
> such a point of view.
>
> -----Original Message-----
> From: Axapta-Knowledge-Village@yahoogroups.com
> [mailto:[EMAIL PROTECTED] On Behalf Of Stefan
> Capiau
> Sent: 20 December 2005 12:43
> To: Axapta-Knowledge-Village@yahoogroups.com
> Subject: [Axapta-Knowledge-Village] Performance
>
> Hi all,
>
> Each month we are running a procedure which is looking at all customer
> and their transactions. We do updates and inserts in the selected
> tables.
>
> The database contains at this moment +300.000 customers and over
> 2.000.000 transactions on the customers.
>
> The problem we are facing at this moment is that the job is taking
> more and more time (exponential) as the customer database grows.
>
> dividing the job into parts of e.g. 25.000 customers gives the
> following results :
>
> The first 25.000 customers are processed in less than 3-4 minutes
> The last 25.000 customers are processed in over 2 hours !!!
>
> We are using an SQL2000 server.
>
> At this moment the complete process is taking over 12 hours to
> complete wheras it should be done withing 30-45 minutes (if we look at
> the first group of customers)
>
> We already figured out that this is client independed. If we run 20
> batches of customers on 20 clients, the first clients takes a couple
> of minutes whereas the last takes over 2 hours.
>
> Is this sounding familiar to somebody ?
>
>
> Stefan
>
>
>
>
>
>
>
> Sharing the knowledge on Axapta.
> Yahoo! Groups Links
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
For $25, 15 Afghan women can learn to read. Your gift can make a difference.
http://us.click.yahoo.com/rQ8GtB/SdGMAA/cosFAA/kGEolB/TM
--------------------------------------------------------------------~->
Sharing the knowledge on Axapta.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/Axapta-Knowledge-Village/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
