[firebird-support] RE: Insert into Large Table is slowly!
Re: [firebird-support] RE: Insert into Large Table is slowly!
On Sat, Sep 7, 2013 at 11:07 AM, jym...@gmail.com wrote: The page isze is 16KB. I'm not run gstat. I will try do it. At the same time, you should get some monitoring statistics - reads/writes/marks/fetches. And some system stats as well - memory, page faults, disk writes, etc. Good luck, Ann
[firebird-support] RE: Insert into Large Table is slowly!
RES: [firebird-support] RE: Insert into Large Table is slowly!
Do you use Classic or SuperServer? Version? How many cached pages? If you use Classic try increasing page buffers to 2000 and try again. Let me know the results, thanks. De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Em nome de chmere...@gmail.com Enviada em: quinta-feira, 5 de setembro de 2013 11:58 Para: firebird-support@yahoogroups.com Assunto: [firebird-support] RE: Insert into Large Table is slowly! --- In firebird-support@yahoogroups.com, firebird-support@yahoogroups.com wrote: 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. CREATE GLOBAL TEMPORARY TableTemp ( f_MeterID Integer, ) ON COMMIT DELETE ROWS; ALTE R INDEX idx_Table1 INACTIVE; 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) USE GLOBAL TEMPORARY 4. commit transaction ALTER INDEX idx_Table1 ACTIVE but now, insert data become very slowly. What should I do? thanks! jimmy Regards.
[firebird-support] RE: Insert into Large Table is slowly!
[firebird-support] RE: Insert into Large Table is slowly!
Re: [firebird-support] RE: Insert into Large Table is slowly!
Em 5/9/2013 13:18, jym...@gmail.com escreveu: Thanks, I will try use GLOBAL TEMPORARY and INACTIVE index. The insert time will be faster without the index, but you will spend a lot of time when you reactivate it... And any query that will run during the insert time will not use the indice, so, it will be very slow to do a full table scan in a multi million record table Take into account that you will reactivate the index 24 times a day
Re: [firebird-support] RE: Insert into Large Table is slowly!
GTT? Em 05/09/2013 13:35, jym...@gmail.com escreveu: Maybe I've missed something about temporary tables, but it looks to me as if you're doing two inserts and a delete per record.  Why not just put the records into the real table to start with? Ann Harrison is right. I just use GTT resolve a question at last week. ≧▽≦ jimmy --- In firebird-support@yahoogroups.com, aharrison@... wrote: On Thu, Sep 5, 2013 at 3:50 AM, jym013@... wrote: 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. Maybe I've missed something about temporary tables, but it looks to me as if you're doing two inserts and a delete per record.  Why not just put the records into the real table to start with? Good luck, Ann -- Atenciosamente, Hugo Eyng
