| Subject | Re: [firebird-support] Help in optimizing big data query |
|---|---|
| Author | Iwan Cahyadi Sugeng |
| Post date | 2019-06-18T00:26:21Z |
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
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
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 liviuslivius@... [firebird-support] <firebird-support@yahoogroups.com> 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/sRegards,Karol Bieniaszewski