| Subject | Insert into Large Table is slowly! |
|---|---|
| Author | |
| Post date | 2013-09-05T07:50:50Z |
Hello, every body!
I have a 180GB database. and have a large table in it.
table's DDL is:
Create Table Table1
(
f_MeterID Integer,
f_CMDID smallInt,
f_Time timestamp,
f_Value Numric(12,2),
f_RecTime Timestamp
);
Create Index idx_Table1 on(f_MeterID, f_CMDID, f_Time)
about 50,000 rows be inserted every hour.
about 1.2 millions rows be inserted every day.
about 500 millions rows every year.
I insert data use follow:
1. start a transaction
2. Insert a batch records into temp table TableTemp, It's structure like table1
3. Insert into Table1 select * from TableTemp
Delete Data From TableTemp
(about 30,000 record every tiime)
4. commit transaction
but now, insert data become very slowly.
What should I do?
thanks!
jimmy