Please check you followed all standard recommendations summarized on this page: https://www.gridgain.com/docs/latest/perf-troubleshooting-guide/sql-tuning
Pay attention to the "Basic Considerations: GridGain vs RDBMS" section. - Denis On Fri, Feb 21, 2020 at 3:45 AM breathem <vasilylaktio...@gmail.com> wrote: > Hello, > We have two tables LD (8 000 000 rows) and DRUGS (130 000 rows). > Following query is executed ~7 minutes that is significantly longer then in > RDBMS (~1,5 sec): > select d.drug_id, d.drug_name, ld.price > from drugs d > left outer join ld on d.drug_id = ld.drug_id and ld.org_id = 264; > > Explain for query: > SELECT > D__Z0.DRUG_ID AS __C0_0, > D__Z0.DRUG_NAME AS __C0_1, > __Z1.PRICE AS __C0_2 > FROM PUBLIC.DRUGS D__Z0 > /* PUBLIC.IDX_DRUG_ID_NAME */ > LEFT OUTER JOIN PUBLIC.LD __Z1 > /* PUBLIC.IDX_ORG_MEDP_DRUG: ORG_ID = 264 > AND DRUG_ID = D__Z0.DRUG_ID > */ > ON (__Z1.ORG_ID = 264) > AND (D__Z0.DRUG_ID = __Z1.DRUG_ID) > SELECT > __C0_0 AS DRUG_ID, > __C0_1 AS DRUG_NAME, > __C0_2 AS PRICE > FROM PUBLIC.__T0 > /* PUBLIC."merge_scan" */ > > Indexes on table LD: IDX_ORG_MEDP_DRUGS(ORG_ID, MEDP_ID, DRUG_ID), > IDX_DRUG_ID(DRUG_ID) > Indexes on table DRUGS: IDX_DRUG_ID_NAME(DRUG_ID, DRUG_NAME) > > We try to force IDX_DRUG_ID: > select d.drug_id, d.drug_name, ld.price > from drugs d > left outer join ld use index (idx_drug_id) on d.drug_id = ld.drug_id and > ld.org_id = 264; > > This query is executed 8 sec. > > Explain for query: > SELECT > D__Z0.DRUG_ID AS __C0_0, > D__Z0.DRUG_NAME AS __C0_1, > __Z1.PRICE AS __C0_2 > FROM PUBLIC.DRUGS D__Z0 > /* PUBLIC.IDX_DRUG_ID_NAME */ > LEFT OUTER JOIN PUBLIC.LD __Z1 USE INDEX (IDX_DRUG_ID) > /* PUBLIC.IDX_DRUG_ID: DRUG_ID = D__Z0.DRUG_ID */ > ON (__Z1.ORG_ID = 264) > AND (D__Z0.DRUG_ID = __Z1.DRUG_ID) > SELECT > __C0_0 AS DRUG_ID, > __C0_1 AS DRUG_NAME, > __C0_2 AS PRICE > FROM PUBLIC.__T0 > /* PUBLIC."merge_scan" */ > > How to speed up query? > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
