Access table still updating

One of our apps updates a table of several hundred million records. For loop approach for the update was calculated to take 53.7 years to complete!

access table still updating-24

How do you get Access to store the result of a calculation?

For example, if you have fields named Quantity and Unit Price, how do you get Access to write Quantity * Unit Price to another field called Amount? " Calculated columns are part of life on a spreadsheet, but do not belong in a database table.

Never store a value that is dependent on other fields: it's a basic rule of normalization.

Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.

There are circumstances where storing a calculated result makes sense - typically where you need the flexibility to store a different value sometimes.

Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is

Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is [[

Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is $0 instead of 10%, so you must store this as a field in the table.We institued the Insert into a dummy table append with nologging, and were able to complete the "update" in under 30 minutes.With nologging, if the system aborts, you simply re-run the 'update' again, as you have the original data in the main table.Yeah ,of course it'll recompile itself when it called next time. There is no logical column to do partition., I guess the insert into a new table will take considerable time with 27 mil records.. November 12, 2002 - am UTC wait 10 days so that you are deleting 30 million records from a 60 million record table and then this will be much more efficient. 3 million records on an indexed table will take considerable time.There is a chance that INSERT /* append */ select Tom, Recently I had conducted a interview in which one the dba mentioned that they had a table that might conatin 10 million records or might be 1 million. The cause could be in either the VBA code or behind the code for Form event properties (e.g. You turn it off, do something, then turn it back on.

||

Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is $0 instead of 10%, so you must store this as a field in the table.

We institued the Insert into a dummy table append with nologging, and were able to complete the "update" in under 30 minutes.

With nologging, if the system aborts, you simply re-run the 'update' again, as you have the original data in the main table.

Yeah ,of course it'll recompile itself when it called next time. There is no logical column to do partition., I guess the insert into a new table will take considerable time with 27 mil records.. November 12, 2002 - am UTC wait 10 days so that you are deleting 30 million records from a 60 million record table and then this will be much more efficient. 3 million records on an indexed table will take considerable time.

There is a chance that INSERT /* append */ select Tom, Recently I had conducted a interview in which one the dba mentioned that they had a table that might conatin 10 million records or might be 1 million.

The cause could be in either the VBA code or behind the code for Form event properties (e.g. You turn it off, do something, then turn it back on.

||

Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is $0 instead of 10%, so you must store this as a field in the table.

We institued the Insert into a dummy table append with nologging, and were able to complete the "update" in under 30 minutes.

With nologging, if the system aborts, you simply re-run the 'update' again, as you have the original data in the main table.

Yeah ,of course it'll recompile itself when it called next time. There is no logical column to do partition., I guess the insert into a new table will take considerable time with 27 mil records.. November 12, 2002 - am UTC wait 10 days so that you are deleting 30 million records from a 60 million record table and then this will be much more efficient. 3 million records on an indexed table will take considerable time.

]] instead of 10%, so you must store this as a field in the table.

We institued the Insert into a dummy table append with nologging, and were able to complete the "update" in under 30 minutes.

With nologging, if the system aborts, you simply re-run the 'update' again, as you have the original data in the main table.

Yeah ,of course it'll recompile itself when it called next time. There is no logical column to do partition., I guess the insert into a new table will take considerable time with 27 mil records.. November 12, 2002 - am UTC wait 10 days so that you are deleting 30 million records from a 60 million record table and then this will be much more efficient. 3 million records on an indexed table will take considerable time.

There is a chance that INSERT /* append */ select Tom, Recently I had conducted a interview in which one the dba mentioned that they had a table that might conatin 10 million records or might be 1 million.

The cause could be in either the VBA code or behind the code for Form event properties (e.g. You turn it off, do something, then turn it back on.

instead of 10%, so you must store this as a field in the table.We institued the Insert into a dummy table append with nologging, and were able to complete the "update" in under 30 minutes.With nologging, if the system aborts, you simply re-run the 'update' again, as you have the original data in the main table.Yeah ,of course it'll recompile itself when it called next time. There is no logical column to do partition., I guess the insert into a new table will take considerable time with 27 mil records.. November 12, 2002 - am UTC wait 10 days so that you are deleting 30 million records from a 60 million record table and then this will be much more efficient. 3 million records on an indexed table will take considerable time.There is a chance that INSERT /* append */ select Tom, Recently I had conducted a interview in which one the dba mentioned that they had a table that might conatin 10 million records or might be 1 million. The cause could be in either the VBA code or behind the code for Form event properties (e.g. You turn it off, do something, then turn it back on.

Tags: , ,