运行此查询时遇到问题。在我的个人电脑上，我把mysql升级到了5.7，做了一些更好的日志记录，这个查询运行得很好。当我在5.5上把它投入生产时，它可以工作，但只是有时。
在生产环境中使用heidisql（i7 3.4ghz-16gb ram）可以正常工作，但需要50秒才能运行，但在我的双6核64gb ram上，在使用相同数据的pc上只需要2.6秒）。使用我的c#应用程序，我得到一个错误“执行命令时遇到致命错误”。你知道这两种情况有什么不同吗？一种情况有效，另一种情况无效？执行时间有关系吗？
我对其他20个查询使用相同的c#query执行方法，所有这些查询都工作得很好，但由于某些原因，这一个有问题。
查询：
SELECT p.patnum AS reference_id,
p.guarantor AS guarantor_reference_id,
p.bal_0_30 AS balance_0_30,
p.bal_31_60 AS balance_31_60,
p.bal_61_90 AS balance_61_90,
p.balover90 AS balance_over_90,
p.baltotal AS account_balance,
pending_writeoff,
claims_estimated_amount,
patient_portion_due AS amount_due_from_patient,
Sum(procfee) AS lifetime_value
FROM patient AS p
LEFT JOIN (SELECT patient.patnum,
patient.guarantor,
patient.bal_0_30,
patient.bal_31_60,
patient.bal_61_90,
patient.balover90,
guarAging.baltotal,
guarAging.inswoest
AS
pending_writeoff,
guarAging.inspayest
AS
claims_estimated_amount,
guarAging.baltotal - guarAging.inspayest -
guarAging.inswoest
AS
patient_portion_due
FROM (SELECT tSums.patnum,
Round(CASE
WHEN tSums.totalcredits >=
tSums.chargesover90
THEN 0
ELSE tSums.chargesover90 -
tSums.totalcredits
end, 2) Balover90,
Round(CASE
WHEN tSums.totalcredits <=
tSums.chargesover90
THEN
tSums.charges_61_90
WHEN tSums.chargesover90 +
tSums.charges_61_90
<=
tSums.totalcredits
THEN 0
ELSE tSums.chargesover90 +
tSums.charges_61_90
-
tSums.totalcredits
end, 2) Bal_61_90,
Round(CASE
WHEN tSums.totalcredits <
tSums.chargesover90 +
tSums.charges_61_90 THEN
tSums.charges_31_60
WHEN tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 <=
tSums.totalcredits THEN 0
ELSE tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 -
tSums.totalcredits
end, 2) Bal_31_60,
Round(CASE
WHEN tSums.totalcredits <
tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 THEN
tSums.charges_0_30
WHEN tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 +
tSums.charges_0_30 <=
tSums.totalcredits
THEN 0
ELSE tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 +
tSums.charges_0_30 -
tSums.totalcredits
end, 2) Bal_0_30,
Round(tSums.baltotal, 2) BalTotal,
Round(tSums.inswoest, 2) InsWoEst,
Round(tSums.inspayest, 2) InsPayEst,
Round(tSums.payplandue, 2) PayPlanDue
FROM (SELECT p.guarantor PatNum,
Sum(CASE
WHEN trans.tranamount > 0
AND trans.trandate <
'2018-01-11'
THEN
trans.tranamount
ELSE 0
end) ChargesOver90,
Sum(CASE
WHEN trans.tranamount > 0
AND trans.trandate <
'2018-01-12'
AND trans.trandate >=
'2018-02-10'
THEN
trans.tranamount
ELSE 0
end) Charges_61_90,
Sum(CASE
WHEN trans.tranamount > 0
AND trans.trandate <
'2018-02-11'
AND trans.trandate >=
'2018-03-12'
THEN
trans.tranamount
ELSE 0
end) Charges_31_60,
Sum(CASE
WHEN trans.tranamount > 0
AND trans.trandate <=
'2018-03-13'
AND trans.trandate >=
'2018-04-12'
THEN
trans.tranamount
ELSE 0
end) Charges_0_30,
-Sum(CASE
WHEN trans.tranamount < 0
AND trans.trandate <=
'2018-04-12'
THEN
trans.tranamount
ELSE 0
end) TotalCredits,
Sum(CASE
WHEN trans.tranamount != 0 THEN
trans.tranamount
ELSE 0
end) BalTotal,
Sum(trans.inswoest) InsWoEst,
Sum(trans.inspayest) InsPayEst,
Sum(trans.payplanamount) PayPlanDue
FROM (SELECT 'Proc'
TranType,
pl.patnum,
pl.procdate
TranDate,
pl.procfee * (
pl.unitqty + pl.baseunits )
TranAmount,
0
PayPlanAmount
,
0
InsWoEst,
0
InsPayEst
FROM procedurelog pl
WHERE pl.procstatus = 2
AND pl.procfee != 0
UNION ALL
SELECT 'Claimproc' TranType,
cp.patnum,
cp.datecp TranDate,
( CASE
WHEN cp. status != 0 THEN
( CASE
WHEN cp.payplannum = 0 THEN
-
cp.inspayamt
ELSE 0
end )
-
cp.writeoff
ELSE 0
end ) TranAmount,
( CASE
WHEN cp.payplannum != 0
AND cp. status IN ( 1,
4, 5 )
THEN -
cp.inspayamt
ELSE 0
end ) PayPlanAmount,
( CASE
WHEN cp. status = 0 THEN
cp.writeoff
ELSE 0
end ) InsWoEst,
( CASE
WHEN cp. status = 0 THEN
cp.inspayest
ELSE 0
end ) InsPayEst
FROM claimproc cp
WHERE cp. status IN ( 0, 1, 4, 5, 7 )
HAVING tranamount != 0
OR payplanamount != 0
OR inswoest != 0
OR inspayest != 0
UNION ALL
SELECT 'Adj' TranType,
a.patnum,
a.adjdate TranDate,
a.adjamt TranAmount,
0 PayPlanAmount,
0 InsWoEst,
0 InsPayEst
FROM adjustment a
WHERE a.adjamt != 0
UNION ALL
SELECT 'PatPay' TranType,
ps.patnum,
ps.datepay TranDate,
( CASE
WHEN ps.payplannum = 0 THEN
-
ps.splitamt
ELSE 0
end ) TranAmount,
( CASE
WHEN ps.payplannum != 0
THEN -
ps.splitamt
ELSE 0
end ) PayPlanAmount,
0 InsWoEst,
0 InsPayEst
FROM paysplit ps
WHERE ps.splitamt != 0
UNION ALL
SELECT 'PPCharge'
TranType,
ppc.guarantor
PatNum,
ppc.chargedate
TranDate,
0
TranAmount,
Coalesce (ppc.principal +
ppc.interest, 0)
PayPlanAmount
,
0
InsWoEst,
0
InsPayEst
FROM payplancharge ppc
WHERE ppc.chargedate <= '2018-04-22'
AND ppc.chargetype = 0
AND Coalesce (ppc.principal +
ppc.interest, 0)
!= 0
UNION ALL
SELECT 'PPComplete' TranType,
pp.patnum,
pp.payplandate TranDate,
-pp.completedamt TranAmount,
0 PayPlanAmount,
0 InsWoEst,
0 InsPayEst
FROM payplan pp
WHERE pp.completedamt != 0) trans
RIGHT JOIN patient p
ON p.patnum = trans.patnum
GROUP BY p.guarantor
ORDER BY NULL) tSums) guarAging
INNER JOIN patient
ON patient.patnum = guarAging.patnum
LEFT JOIN (SELECT patnum,
claimstatus,
claimfee,
inspayest,
writeoff
FROM claim) AS c
ON c.patnum = patient.patnum
GROUP BY patient.patnum) AS results
ON results.patnum = p.patnum
LEFT JOIN procedurelog AS plog
ON plog.patnum = p.patnum
AND plog.procstatus = 2
GROUP BY p.patnum执行：
try
{
using (var mySqlConnection = new MySqlConnection(connBuilder.ConnectionString))
{
mySqlConnection.Open();
using (var cmd = new MySqlCommand(strQuery, mySqlConnection))
{
using (var reader = cmd.ExecuteReader()) <= FAILS HERE
{值得一提的是，有一次我确实看到了错误“读取数据时出现致命错误”，并添加了这个错误，但另一个错误仍然在发生：
MySqlCommand timeoutAdjust = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", mySqlConnection); // Setting tiimeout on mysqlServer
timeoutAdjust.ExecuteNonQuery();
暂无答案！
目前还没有任何答案，快来回答吧！