java.sql.ResultSet:next() is so slowly
28 views
Skip to first unread message
wuchao
Sep 2, 2024, 10:56:21 AM9/2/24
to mybatis-user
Here are the time spent executing my two different SQL query. I would like to know what resources Result.next mainly uses and why it takes so long? And strangely enough, the same code only takes 2 seconds on my local machine and 17 seconds on the server
Press Q or Ctrl+C to abort.
Affect(class count: 1 , method count: 1) cost in 224 ms, listenerId: 11
`---ts=2024-09-02 14:13:12;thread_name=XNIO-1 task-30;id=3926;is_daemon=false;priority=5;TCCL=org.springframework.boot.loader.LaunchedURLClassLoader@5910e440
`---[15557.005788ms] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:handleRowValuesForSimpleResultMap()
+---[0.00% 0.003274ms ] org.apache.ibatis.executor.result.DefaultResultContext:<init>() #350
+---[0.00% 0.00171ms ] org.apache.ibatis.executor.resultset.ResultSetWrapper:getResultSet() #351
+---[0.00% 0.004815ms ] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:skipRows() #352
+---[0.40% min=5.92E-4ms,max=0.036466ms,total=62.097562ms,count=63428] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:shouldProcessMoreRows() #353
+---[0.33% min=4.26E-4ms,max=0.040213ms,total=51.722283ms,count=63428] java.sql.ResultSet:isClosed() #353
+---[88.21% min=0.171797ms,max=126.436353ms,total=13722.935512ms,count=63428] java.sql.ResultSet:next() #353
+---[0.52% min=6.68E-4ms,max=0.928624ms,total=80.784055ms,count=63427] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:resolveDiscriminatedResultMap() #354
+---[6.82% min=0.011916ms,max=84.617008ms,total=1060.704195ms,count=63427] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:getRowValue() #355
`---[0.49% min=7.31E-4ms,max=0.067561ms,total=76.232928ms,count=63427] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:storeObject() #356
`---ts=2024-09-02 14:22:20;thread_name=XNIO-1 task-17;id=3898;is_daemon=false;priority=5;TCCL=org.springframework.boot.loader.LaunchedURLClassLoader@5910e440
`---[6128.853495ms] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:handleRowValuesForSimpleResultMap()
+---[0.00% 0.001043ms ] org.apache.ibatis.executor.result.DefaultResultContext:<init>() #350
+---[0.00% 8.35E-4ms ] org.apache.ibatis.executor.resultset.ResultSetWrapper:getResultSet() #351
+---[0.00% 0.002258ms ] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:skipRows() #352
+---[0.03% min=5.96E-4ms,max=0.004849ms,total=1.545144ms,count=2087] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:shouldProcessMoreRows() #353
+---[0.02% min=3.93E-4ms,max=0.011387ms,total=1.131476ms,count=2087] java.sql.ResultSet:isClosed() #353
+---[99.11% min=0.256972ms,max=454.154663ms,total=6074.424838ms,count=2087] java.sql.ResultSet:next() #353
+---[0.03% min=6.57E-4ms,max=0.008651ms,total=2.105309ms,count=2086] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:resolveDiscriminatedResultMap() #354
+---[0.60% min=0.012963ms,max=0.642083ms,total=36.52344ms,count=2086] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:getRowValue() #355
`---[0.03% min=7.35E-4ms,max=0.009623ms,total=2.007829ms,count=2086] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:storeObject() #356
`---[6128.853495ms] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:handleRowValuesForSimpleResultMap()
+---[0.00% 0.001043ms ] org.apache.ibatis.executor.result.DefaultResultContext:<init>() #350
+---[0.00% 8.35E-4ms ] org.apache.ibatis.executor.resultset.ResultSetWrapper:getResultSet() #351
+---[0.00% 0.002258ms ] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:skipRows() #352
+---[0.03% min=5.96E-4ms,max=0.004849ms,total=1.545144ms,count=2087] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:shouldProcessMoreRows() #353
+---[0.02% min=3.93E-4ms,max=0.011387ms,total=1.131476ms,count=2087] java.sql.ResultSet:isClosed() #353
+---[99.11% min=0.256972ms,max=454.154663ms,total=6074.424838ms,count=2087] java.sql.ResultSet:next() #353
+---[0.03% min=6.57E-4ms,max=0.008651ms,total=2.105309ms,count=2086] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:resolveDiscriminatedResultMap() #354
+---[0.60% min=0.012963ms,max=0.642083ms,total=36.52344ms,count=2086] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:getRowValue() #355
`---[0.03% min=7.35E-4ms,max=0.009623ms,total=2.007829ms,count=2086] org.apache.ibatis.executor.resultset.DefaultResultSetHandler:storeObject() #356
Iwao AVE!
Sep 2, 2024, 12:35:10 PM9/2/24
to mybati...@googlegroups.com
Hello,
java.sql.ResultSet#next() is handled by the driver, not MyBatis.
You should be able to isolate the problem from MyBatis by writing a plain JDBC app that executes the same query.
Class.forName("xxx");
String url = "jdbc:xxx";
String username = "xxx";
String password = "xxx";
try (Connection con = DriverManager.getConnection(url, username, password)) {
try (PreparedStatement stmt = con.prepareStatement("select * from tbl where x = ?")) {
stmt.setString(1, "foo");
try (ResultSet rs = stmt.executeQuery()) {
rs.next();
System.out.println(rs.getString(1));
}
}
}
As you have two environments with different results, you are in the best position to find the key difference that causes the performance issue.
It could be, for example, some kind of network issue.
It’s difficult to investigate a performance issue without reproducing it, but if you provide the JDBC app source, table schema and sample data, someone might notice something.
Regards,
Iwao
wuchao
Sep 2, 2024, 11:51:22 PM9/2/24
to mybatis-user
Thank you for your reply. Although I am not quite sure about the specific reason, I found that upgrading the CPU base frequency of the server can significantly improve the speed. After optimizing the business, the problem has been solved
Peter Cao
Sep 13, 2024, 9:07:34 AM9/13/24
to mybati...@googlegroups.com
Hi Guys,
P.logistics_area,
P.common_area,
P.percentage,
P.total_integral,
P.reserve_integral,
P.project_id,
P.contract_id AS real_contract_id,
P.contract_code,
P.work_hours,
P.department_id,
P.design_phase,
P.project_type,
P.project_code,
P.project_name,
P.plan_start_date,
P.plan_end_date,
P.start_date,
P.end_date,
P.status,
P.project_progress,
P.project_manage_id,
P.project_size,
P.project_integral,
P.project_description,
P.budget,
P.audit_reason,
P.design_type,
P.total_amount,
P.project_level,
P.old_project_discount,
P.department_manager_id,
C.sub_contract_id AS contract_id,
P.plate_id,
P.project_finish_percent,
P.parent_id,
P.total_finish_percent,
P.plate_manager_id,
P.project_grade_type,
P.last_percent,
P.project_flag,
P.is_new_project,
P.sheet_amount AS money_sum,
P.is_lock,
CASE
WHEN P.complaint_integral > 0 THEN 0
ELSE COALESCE(
(
SELECT
MIN(project_performance_id)
FROM
project_performances
WHERE
business_type = 2
AND is_settlement = FALSE
AND project_id = P.project_id
),
0
)
END AS project_performance_id,
(
SELECT
SUM(total_amount)
FROM
project_settlements
WHERE
settlement_status = 2
AND project_id = P.project_id
AND settlement_date BETWEEN :the_start_date AND :the_end_date
) AS sure_integral,
(P.project_integral - (COALESCE(
(
SELECT
SUM(total_amount)
FROM
project_settlements
WHERE
settlement_status = 2
AND project_id = P.project_id
),
0
))) AS left_sure_integral,
CASE
WHEN P.project_integral > 0 THEN ROUND(
(COALESCE(
(
SELECT
SUM(total_amount)
FROM
project_settlements
WHERE
settlement_status = 2
AND project_id = P.project_id
),
0
) * 100 / P.project_integral),
2
)
ELSE 0
END AS progress_percentage,
CASE
WHEN P.project_integral > 0 THEN ROUND(
(COALESCE(
(
SELECT
SUM(total_amount)
FROM
project_settlements
WHERE
settlement_status = 2
AND project_id = P.project_id
AND settlement_date BETWEEN :the_start_date AND :the_end_date
),
0
) * 100 / P.project_integral),
2
)
ELSE 0
END AS project_finish_percent_havetime,
COALESCE(
(
SELECT
SUM(account_integral)
FROM
plate_project_account_records
WHERE
project_id = P.project_id
AND account_date BETWEEN :the_start_date AND :the_end_date
),
0
) AS plate_sure_integral,
(P.reserve_integral - COALESCE(
(
SELECT
SUM(account_integral)
FROM
plate_project_account_records
WHERE
project_id = P.project_id
),
0
)) AS left_plate_sure_integral,
P.review_integral,
(
SELECT
SUM(par.advance_integral)
FROM
project_advance_records par
WHERE
P.project_id = par.project_id
AND FLAG = 1
) AS advance_integral,
CASE
WHEN CC.contract_signing_status = 2 THEN 1
ELSE
CASE
WHEN CC.contract_date IS NULL THEN
CASE
WHEN P.frozen_status IS NULL THEN
CASE
WHEN DATE_PART('day',NOW() - M.project_approval_time) > 120 THEN 2
ELSE 1
END
ELSE P.frozen_status
END
ELSE 1
END
END AS frozen_status,
P.product_line
FROM
projects P
LEFT JOIN sub_contracts C ON P.contract_code = C.sub_contract_code
LEFT JOIN main_projects M ON P.project_code = M.project_code
LEFT JOIN contracts CC ON CC.contract_id = P.contract_id
WHERE
P.project_id = :project_id
AND P.design_phase = :design_phase
AND P.project_name LIKE :project_name
AND P.contract_code = :contract_code
AND P.department_id = :department_id
AND P.plate_id = :project_type
AND P.project_code = :project_code
AND P.start_date = :start_date
AND P.end_date = :end_date
AND P.status = :status
AND P.project_level = :project_level
AND P.project_manage_id = :project_manage_id
AND P.status >= 0
AND P.product_line = :product_line
ORDER BY
P.project_id DESC
I just published a JetBrains plugin https://plugins.jetbrains.com/plugin/25259-mybatis-dao-generator
It can not only generate DAO for signle table according to database schema, but also generate DAO based on any query statement just like this
SELECTP.logistics_area,
P.common_area,
P.percentage,
P.total_integral,
P.reserve_integral,
P.project_id,
P.contract_id AS real_contract_id,
P.contract_code,
P.work_hours,
P.department_id,
P.design_phase,
P.project_type,
P.project_code,
P.project_name,
P.plan_start_date,
P.plan_end_date,
P.start_date,
P.end_date,
P.status,
P.project_progress,
P.project_manage_id,
P.project_size,
P.project_integral,
P.project_description,
P.budget,
P.audit_reason,
P.design_type,
P.total_amount,
P.project_level,
P.old_project_discount,
P.department_manager_id,
C.sub_contract_id AS contract_id,
P.plate_id,
P.project_finish_percent,
P.parent_id,
P.total_finish_percent,
P.plate_manager_id,
P.project_grade_type,
P.last_percent,
P.project_flag,
P.is_new_project,
P.sheet_amount AS money_sum,
P.is_lock,
CASE
WHEN P.complaint_integral > 0 THEN 0
ELSE COALESCE(
(
SELECT
MIN(project_performance_id)
FROM
project_performances
WHERE
business_type = 2
AND is_settlement = FALSE
AND project_id = P.project_id
),
0
)
END AS project_performance_id,
(
SELECT
SUM(total_amount)
FROM
project_settlements
WHERE
settlement_status = 2
AND project_id = P.project_id
AND settlement_date BETWEEN :the_start_date AND :the_end_date
) AS sure_integral,
(P.project_integral - (COALESCE(
(
SELECT
SUM(total_amount)
FROM
project_settlements
WHERE
settlement_status = 2
AND project_id = P.project_id
),
0
))) AS left_sure_integral,
CASE
WHEN P.project_integral > 0 THEN ROUND(
(COALESCE(
(
SELECT
SUM(total_amount)
FROM
project_settlements
WHERE
settlement_status = 2
AND project_id = P.project_id
),
0
) * 100 / P.project_integral),
2
)
ELSE 0
END AS progress_percentage,
CASE
WHEN P.project_integral > 0 THEN ROUND(
(COALESCE(
(
SELECT
SUM(total_amount)
FROM
project_settlements
WHERE
settlement_status = 2
AND project_id = P.project_id
AND settlement_date BETWEEN :the_start_date AND :the_end_date
),
0
) * 100 / P.project_integral),
2
)
ELSE 0
END AS project_finish_percent_havetime,
COALESCE(
(
SELECT
SUM(account_integral)
FROM
plate_project_account_records
WHERE
project_id = P.project_id
AND account_date BETWEEN :the_start_date AND :the_end_date
),
0
) AS plate_sure_integral,
(P.reserve_integral - COALESCE(
(
SELECT
SUM(account_integral)
FROM
plate_project_account_records
WHERE
project_id = P.project_id
),
0
)) AS left_plate_sure_integral,
P.review_integral,
(
SELECT
SUM(par.advance_integral)
FROM
project_advance_records par
WHERE
P.project_id = par.project_id
AND FLAG = 1
) AS advance_integral,
CASE
WHEN CC.contract_signing_status = 2 THEN 1
ELSE
CASE
WHEN CC.contract_date IS NULL THEN
CASE
WHEN P.frozen_status IS NULL THEN
CASE
WHEN DATE_PART('day',NOW() - M.project_approval_time) > 120 THEN 2
ELSE 1
END
ELSE P.frozen_status
END
ELSE 1
END
END AS frozen_status,
P.product_line
FROM
projects P
LEFT JOIN sub_contracts C ON P.contract_code = C.sub_contract_code
LEFT JOIN main_projects M ON P.project_code = M.project_code
LEFT JOIN contracts CC ON CC.contract_id = P.contract_id
WHERE
P.project_id = :project_id
AND P.design_phase = :design_phase
AND P.project_name LIKE :project_name
AND P.contract_code = :contract_code
AND P.department_id = :department_id
AND P.plate_id = :project_type
AND P.project_code = :project_code
AND P.start_date = :start_date
AND P.end_date = :end_date
AND P.status = :status
AND P.project_level = :project_level
AND P.project_manage_id = :project_manage_id
AND P.status >= 0
AND P.product_line = :product_line
ORDER BY
P.project_id DESC
By the way it also can generate master-detail table DAO as well.
Please try it out and give me some feedback.
Best Regards.
Peter
wuchao <g5464...@gmail.com> 于2024年9月3日周二 11:51写道：
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/14379022-1e47-4eb2-9f41-4dbf82d76439n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages