Performance Issue To Append 3 Millions Records From Access Table To SQL Server Table

[CJ_London]

here we need to update customer details like [Current Outstanding & Ageing] on daily basis.

Without a full description of the story behind the action you are undertaking it is not possible to provide more that general suggestions. So explain how the 3m records are created, what they actually contain, why they are transferred to SQL server and what SQL server does with it once received.

I think we are still all confused about the purpose of 3m records - I can understand if you are a financial, utilities or telecoms type entity you will have these sort of numbers on a customer base, but aging (I presume you mean age of debt) is a calculation so should not be a stored value. And it sounds like you are transferring all records and not just those that have changed since yesterday/last upload. And Current Outstanding would be a number (currency), yet you say 'There are no validation on text' which implies your fields are text, not numeric.

you ask

which method is best for 3 Million Record (1) Append Query (2) Update Query.

The answer is, it depends. As a straight movement of 3m records, Append is probably quicker because although there is the overhead of updating the index, you have the update overhead of identifying which records have changed by comparing the two tables - only real test would be to try it and see. But if you have say a timestamp field in your access table to indicate when last changed, an update query which updates to 50,000 records which have changed since last updated will be faster because you don't have that comparison to make.

Also, what happens to the 3m records in Access (and SQL server for that matter) whilst the upload is going on? Are users still adding/changing records?

Here i just run all query's on ms access table and final result will append to a separate sql table.
...
Here i'm trying to Alter Server Table (Delete Primary Key before appending records And Adding Primary Key post records appended) programmatically but no luck.
...

So have you considered using a make table query on SQL Server?

 

[static]

I have a customer table where 3 million records are there, here we need to update customer details like [Current Outstanding & Ageing] on daily basis.

If i choose option "Update Table using VBA/Query Wizard" then it is not possible to update 3 million records every day because In my process to calculate Ageing & Outstanding there are multiple validations and multiple query's.

Here i just run all query's on ms access table and final result will append to a separate sql table.

Why do you need to store calculated values?

 

[arjun5381]

Without a full description of the story behind the action you are undertaking it is not possible to provide more that general suggestions. So explain how the 3m records are created, what they actually contain, why they are transferred to SQL server and what SQL server does with it once received.

I think we are still all confused about the purpose of 3m records - I can understand if you are a financial, utilities or telecoms type entity you will have these sort of numbers on a customer base, but aging (I presume you mean age of debt) is a calculation so should not be a stored value. And it sounds like you are transferring all records and not just those that have changed since yesterday/last upload. And Current Outstanding would be a number (currency), yet you say 'There are no validation on text' which implies your fields are text, not numeric.

I am working with Telecom Industry, we are service provide and we have 0.6 millions of customers and the have multiple Accounts ~ 3 Millions.

I have a master table where customer details are available and static like customer Account Number, Customer Name, Billing Address, Product Type, Segment, Contact Person Name etc.

But here some details are dynamic basis of daily usage of service Like (Billed Amount + Unbilled Amount) = Current Outstanding Amount And Age of Last Invoice.

Here i want to update Current Outstanding Amount and Age of Last Invoice on daily basis of all 3 Million Records.

Here i know 2 methods to update these 2 information's (1) Update Records (2) Append These Records to a new table and link through Account Number (Because Account Number Is Primary Key of both Table)

I am not using timestamp on any table.

Also, what happens to the 3m records in Access (and SQL server for that matter) whilst the upload is going on? Are users still adding/changing records?

when we are updating backed table on that time we block all users and they are not able to use application.

So have you considered using a make table query on SQL Server?

I already a created a Table on SQL, here i just Truncate & Append records.

 

[arjun5381]

Why do you need to store calculated values?

because these values are change on daily basis, if customer is paid some/all amount of there Invoice OR customer using our services then these information will not constant. Please read my new comment there i just explained my working process.

 

[static]

You should be updating the records not deleting them and recreating them.

Create a stored procedure and kick it off at night when the users have gone home.

 

[CJ_London]

I've done a lot of work with telecoms

Because of volumes, (I presume you are only talking about PAYM, not PAYG) billing is typically done daily (or perhaps 2/3 day cycles depending on the billing system) to spread the load based on the customer requirement - but a customer is typically only billed once a month, so because your billed amount and age of invoice remains static from one bill to the next, you are only talking about the unbilled amount.

You have not mention billed receipts - most customers will be paying by direct debit on a specific day after the date of the bill.

Ergo approx. 1/30th of you records will change on a daily basis for billing and another 1/30th for billing receipts - so you should be looking at a basis for handling 200,000 records on a daily basis, not 3m as far as these elements are concerned.

Unbilled amount is another matter however, that will change on a daily basis for most accounts. That almost certainly comes from another system (might be SQL Server so perhaps can be gathered separately by SQL Server) but from my experience is often an unproven amount - it may be subsequently cancelled, might have discounts applied which cannot be applied until it is billed, etc. At best it is an estimate which tends to being optimistic. Either way I would suggest it should be stored in a separate table and a view created in SQL server to combine with the monthly static data.

already a created a Table on SQL, here i just Truncate & Append records.

make tables are faster than append queries because there is no indexing overhead - try it and see, it will be less than 1 1/2 hours.

You also haven't explained what SQL server does with the data once it has it