Re: [firebird-support] Help in optimizing big data query
Hi, Thank you for the explanation, will optimize index, i've tried to add index on TANGGAL_CETAK, and it really work faster and as expected, showing index read as much as needed only, not all record. On Tue, Jun 18, 2019 at 11:33 AM liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > > > Hi > > If you have wher clause i that ordered query, > Think how you can do this on the paper. You have rows ordered you read > first and interesting is not null, you must skip it and go to next. And if > you must skip e.g 30 000 000 to read 1 000 000 then you see what is going > on. Always your indexes should satisfy your where clause. Above sample show > something called selectivity. You must maximize selectivity of index used.. > E.g. if you have only value true and false in the field then if your half > of the records are true and other half false then index can only slowdown > query execution. If you have 1000 records with true and 1000 with false > index can speed up query looking for true and slowdown query looking for > false. > > Sumarize, index slowdown common value. > > Regards, > Karol Bieniaszewski > > >
Re: [firebird-support] Help in optimizing big data query
HiIf you have wher clause i that ordered query,Think how you can do this on the paper. You have rows ordered you read first and interesting is not null, you must skip it and go to next. And if you must skip e.g 30 000 000 to read 1 000 000 then you see what is going on. Always your indexes should satisfy your where clause. Above sample show something called selectivity. You must maximize selectivity of index used. E.g. if you have only value true and false in the field then if your half of the records are true and other half false then index can only slowdown query execution. If you have 1000 records with true and 1000 with false index can speed up query looking for true and slowdown query looking for false.Sumarize, index slowdown common value.Regards,Karol Bieniaszewski null
Re: [firebird-support] Help in optimizing big data query
Hi, The plan is using ORDER, it read through index, in my system, i need to load 1 millions of records and i only expect the server to read 1 millions of records only, not all the data in the table. I already use high speed m2 SSD Samsung EVO 860 series, it still need a lot of time to load. The problem is why firebird need to read all data in the table to give me 1 million of data I've been investigating more on this, i've tried to use this query : SELECT TBAT_ITM.NOMOR, ASCII_CHAR(13) || ASCII_CHAR(10) FROM TBAT_ITM WHERE TBAT_ITM.TANGGAL_CETAK IS NULL ORDER BY TBAT_ITM.NOMOR ROWS 1 TO 1000 This should be return 1000 records only, it reads all the records too and return only 1000 record, i think the problem is with the TANGGAL_CETAK not being indexed, i did not create index on it, but should i need to create an index on all column needed in the where clause? i've tried this query too SELECT TBAT_ITM.NOMOR, ASCII_CHAR(13) || ASCII_CHAR(10) FROM TBAT_ITM WHERE TBAT_ITM.KODE_MESIN IS NULL ROWS 1 TO 1000 i've added index on KODE_MESIN, and this query result 1000 index read, so i see that i need to add index on every field where needed by the where clause, any suggestion for best practise or something i need to consider How is firebird handle index? When new data inserted in a table with an index, should i still need to do reindex when a lot new data inserted, let say 50 millions of data inserted over time? Thank you On Mon, Jun 17, 2019 at 9:35 PM liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > > > If the plan include ORDER not SORT then it read data throught index. > But if you are asking server about 1 000 000 records, what do you expect > more from the server to do? > And you use 2048 page buffers then all data is retrived from your HDD. To > speed up you must: > 1. Change design to retrive limited number of fecords e.g. 1000. > 2. Increase page buffers to speedup second and next queries. > 3. Invest in faster HDD like m2 3500 MB/s > > > Regards, > Karol Bieniaszewski > > >
Re: [firebird-support] Help in optimizing big data query
If the plan include ORDER not SORT then it read data throught index.But if you are asking server about 1 000 000 records, what do you expect more from the server to do?And you use 2048 page buffers then all data is retrived from your HDD. To speed up you must:1. Change design to retrive limited number of fecords e.g. 1000.2. Increase page buffers to speedup second and next queries.3. Invest in faster HDD like m2 3500 MB/sRegards,Karol Bieniaszewski null
Re: [firebird-support] Help in optimizing big data query
Hi everyone, I now have about 55 million data on this table, the structure is on the first thread, i'v added index on NOMOR, the query is : SELECT TBAT_ITM.NOMOR FROM TBAT_ITM ORDER BY TBAT_ITM.NOMOR ROWS 1 TO 100 -- QUERY PERFORMANCE -- Prepare : 94 ms Execute : 00:40:53 Avg fetch time: 0 ms --- MEMORY Current : 54.89 MB Max : 57.19 MB Buffers : 2048 -- TABLE OPERATIONS --- Table name: TBAT_ITM Idx reads : 56549278 Non-idx reads : 0 Updates : 0 Deletes : 0 Inserts : 0 I expect the query is using NOMOR as the index and of course don't have to read all the record just to order them, but the query plan is using ORDER and it read all 55 millions records to get 1 millions records? Is this as design? Thank you On Tue, Apr 23, 2019 at 11:45 AM Iwan Cahyadi Sugeng < iwan.c.sug...@gmail.com> wrote: > Hi, > > The data is repeated and most records are empty value, it will be filled > after a process. But will try to add index on it > > Thank you > > On Tue, Apr 23, 2019 at 11:35 AM liviuslivius liviusliv...@poczta.onet.pl > [firebird-support] wrote: > >> >> >> Hi >> >> As you can see, your query do not use any index for filter records. You >> got indexed reads only for order by. The obvius question is what about >> creating index on e.g KODE_MESIN. >> >> Regards, >> Karol Bieniaszewski >> >> >> >
Re: [firebird-support] Help in optimizing big data query
Hi, The data is repeated and most records are empty value, it will be filled after a process. But will try to add index on it Thank you On Tue, Apr 23, 2019 at 11:35 AM liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > > > Hi > > As you can see, your query do not use any index for filter records. You > got indexed reads only for order by. The obvius question is what about > creating index on e.g KODE_MESIN. > > Regards, > Karol Bieniaszewski > > >
Re: [firebird-support] Help in optimizing big data query
HiAs you can see, your query do not use any index for filter records. You got indexed reads only for order by. The obvius question is what about creating index on e.g KODE_MESIN. Regards,Karol Bieniaszewski null
[firebird-support] Help in optimizing big data query
Hi, I'm using Firebird 2.5.8 win 64, i have 33 millions data on one table Here is the DML : CREATE TABLE TBAT_ITM ( ID_TBAT_ITM ID NOT NULL, ID_TBAT ID, KODE_MESIN KODE, NOMOR ID, TANGGAL_CETAK WAKTU, REVISION REVISI, CREATED_BY ID, CREATED_DATE WAKTU, MODIFIED_BY ID, MODIFIED_DATE WAKTU); ALTER TABLE TBAT_ITM ADD CONSTRAINT PK_TBAT_ITM PRIMARY KEY (ID_TBAT_ITM); ALTER TABLE TBAT_ITM ADD CONSTRAINT TBAT_TBAT_ITM FOREIGN KEY (ID_TBAT) REFERENCES TBAT(ID_TBAT) ON UPDATE CASCADE; CREATE INDEX IDX_TBAT_ITM_1_FK ON TBAT_ITM(ID_TBAT); CREATE UNIQUE INDEX IDX_TBAT_ITM_3 ON TBAT_ITM(NOMOR); CREATE UNIQUE INDEX IDX_TBAT_ITM_PK ON TBAT_ITM(ID_TBAT_ITM); I need to do a simple query to retrieve data from that table with this query: SELECT TBAT_ITM.NOMOR, TBAT_ITM.TANGGAL_CETAK, TBAT_ITM.KODE_MESIN FROM TBAT_ITM WHERE TBAT_ITM.TANGGAL_CETAK IS NULL AND TBAT_ITM.KODE_MESIN = 'DeviceC' ORDER BY TBAT_ITM.ID_TBAT_ITM; PLAN The Firebird Select : Plan: PLAN (TBAT_ITM ORDER IDX_TBAT_ITM_PK) Adapted plan: PLAN (TBAT_ITM ORDER IDX_TBAT_ITM_PK) and here is the performance analysis -- QUERY PERFORMANCE -- Prepare : 0 ms Execute : 0 ms Avg fetch time: 00:03:10 --- MEMORY Current : 30.32 MB Max : 30.62 MB Buffers : 2048 -- TABLE OPERATIONS --- Table name: TBAT_ITM Idx reads : 31025673 Non-idx reads : 0 Updates : 0 Deletes : 0 Inserts : 0 So the problem is that event though it use index read, but i expect not to read all record because i already set the where clause which should be limit the data return. The data will go even bigger up to 300 millions record, so if firebird still need to read all 300 millions data, then it will be a problem in the near future. I Also already do a full reindex: RDB$INDEX_NAME RDB$STATISTICS IDX_TBAT_ITM_3 3.22580646638926E-8 So can anyone give me a performance suggestion? Thanks
