How to improve the performance of query?

1.It takes a long time (>100ms) to get result when i use JDBC driver.
2.Trace method calling path, and output the time cost for each node in the path.
3.The method ’ com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan() ’ costs a lot of time.
4.PhysicalPlan be created a new one each time, why not cached it ?

@lewis Can you share your query profile, we will have a look and suggest you if there is any room for improvement. Multiple factors can cause query performance slow.

Did you try reflections for query acceleration?

Thanks
@Venugopal_Menda

– Create tables

CREATE TABLE card_info
(
card_id INT AUTO_INCREMENT PRIMARY KEY,
bank_code VARCHAR(20) NULL
);

CREATE TABLE card_bill
(
bill_Id INT AUTO_INCREMENT PRIMARY KEY,
new_charges FLOAT NULL,
new_balance FLOAT NULL,
credit_limit FLOAT NULL,
last_month VARCHAR(20) NULL,
card_Id INT NULL
);

–Init data for test.

INSERT INTO card_info (bank_code) VALUES (‘001’);
INSERT INTO card_info (bank_code) VALUES (‘001’);
INSERT INTO card_info (bank_code) VALUES (‘002’);
INSERT INTO card_info (bank_code) VALUES (‘005’);

INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (100, 100, 1000, ‘1’, 1);
INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (200, 100, 1000, ‘2’, 1);
INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (300, 100, 1000, ‘3’, 3);
INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (400, 100, 2000, ‘4’, 2);
INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (500, 100, 2000, ‘5’, 2);
INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (600, 100, 2000, ‘6’, 3);
INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (700, 100, 2000, ‘7’, 4);
INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (800, 100, 5000, ‘8’, 4);
INSERT INTO card_bill (new_charges, new_balance, credit_limit, last_month, card_Id) VALUES (100, 100, 1000, ‘9’, 1);

–The SQL

SELECT avg(limit_usage_2) AS var_userbill_079
FROM
(
SELECT
sum(month_charges) /sum(max_credit_limit) AS limit_usage_1,
sum(month_balance) /sum(max_credit_limit) AS limit_usage_2
FROM (
SELECT
sum(t2.new_charges) AS month_charges ,
sum(t2.new_balance) AS month_balance ,
max(t2.credit_limit) AS max_credit_limit,
t1.bank_code,
t2.last_month
FROM card_info t1 INNER JOIN card_bill t2
ON t1.card_id = t2.card_id
WHERE last_month<=2
AND t2.credit_limit>0
AND t2.new_charges>0
GROUP BY t1.bank_code,
t2.last_month
) t GROUP BY last_month) tt

The query profile you cant view this file profile-01.zip (8.2 KB)

RT (AVG) : > 70 ms


I tried to create data reflections:

The query profile you cant view this file profile-02.zip (13.6 KB)

RT (AVG) : > 600 ms , slower than before …

Thanks