Database insert Performance

[pvuppa1]

Can any one help with some database issues.
I have to deal with huge tables like tableA (70 mil rows )and TableB(45 mil rows.) and they grow on timely basis.
Every month I have to insert from TableB into Table A with some calculations. On an avg. a month's data is around 5 Mil.
The way Im doing right now is creating some staging tables before we insert into tableA. But, I need to come up with something effective and efficient way.

Appreciate ur help

Thanks

-P

 

[rasETL]

pvuppal

In order to help you out, I would need some additional information regarding the System you have.

- Is the Database FOCUS? DB2? Other?
- How much data do you store 12 months, 24 months, no limit
- Is the data made available on-line?
- Reporting requirements

etc.

This would help determine a posssible more efficient approach.

 

[pvuppa1]

rasanders ,
thanks for the reply,
database: MSSQL server 2k
the data we have is from 200212 till date. All I need is to come up with some idea which could improve my data-loading procedure for every month(abt 3mil rows). which takes the data from a table(45 mil rows)and inserts into another table(70Mil rows) after performing all the required calculations.
I hope Im not confusing you.

Appreciate ur help

Thanks
-P

 

[rasETL]

pvuppa1

OK

Then, how long is the current process taking?

Is the current process written with Web FOCUS? If so, have you considered switching to a SQL Server Stored Procedure?

Are the Tables Indexed properly for your process?

Again, if you could provide more information, it would help.

 

[PranasH]

My experience DO NOT USE OLTP DBMS for such task:

Maybe it looks silly, but why not trying "Inteligent Partition" and store data in FOCUS database?

I use such approach to store telephony calls

Loading/Reporting will be extremly fast. XFOCUS will apper soon. If you don't like FOCUS, you can test Sybace IQ - extreamly good DBMS.

Pranas