sql查询耗时过长问题

本文针对UAT及生产环境中一个列表查询耗时过长的问题进行了深入分析。通过优化索引和重构SQL逻辑,尤其是解决了复杂的子查询导致的效率低下,将查询时间从70秒缩短到5秒,提供了一种有效的解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

问题:UAT及生产环境一个列表查询耗时大概70+s,测试环境由于数据量不够没有产生该问题,查看日志发现sql在分页count总数时耗时过长

分析:

1.索引缺失。

使用explain优化索引后,查询耗时为40s左右。没有彻底解决问题。

2.riskStateCause字段查询为from前子查询,逻辑过于复杂,列表查询在分页count总条数的时候每条数据都要执行该子查询,导致耗时过长。

将riskStateCause字段的查询从主体sql分离,在主sql得出查询结果后进行for循环查询riskStateCause。查询耗时为5s左右,缺点是无法根据riskStateCause字段进行排序了。

总结:

from语句之前的子查询尽量避免或以单表查询为主,否则随着数据量的增加会极大降低查询效率

参考链接:MySQL Explain详解

修改前sql如下:

SELECT
	count( 0 ) 
FROM
	(
	SELECT
		lr.BUSI_ORDER_ID,
		tm.MCOM_ID mcomId,
		tm.MCOM_FNAME mcomFname,
		ts.SCOM_ID scomId,
		ts.SCOM_FNAME scomFname,
		tb.REG_ID brokerId,
		tb.BROKER_CODE brokerCode,
		tb.`NAME` brokerName,
		tc.`NAME` holderName,
		lr.tenant_id tenantId,
		tr.SUPPLIER_ID supplierId,
		supplyier.SUPPLIER_NAME supplierName,
		tl.POLICY_CODE policyCode,
		pay.POLICY_YEAR AS policyYear,
		tp.PROD_CODE prodCode,
		tp.PROD_NAME prodName,
		lr.COVERAGE_PERIOD AS coveragePeriod,
		lr.COVERAGE_PERIOD_VALUE coveragePeriodValue,
		lr.CHARGE_INTERVAL AS chargeInterval,
		lr.CHARGE_PERIOD AS chargePeriod,
		lr.CHARGE_PERIOD_VALUE chargePeriodValue,
		pay.PAY_NUM payNum,
		lr.RISK_STATE AS riskState,
		lr.RISK_STATE AS riskStateTitle,
		tor.COMM_AMT commAmt,
		tor.COMM_RATE commRate,
	CASE
			
			WHEN pay.DUE_TIME IS NOT NULL 
			OR tor.INSERT_TIME IS NOT NULL THEN
				(
				SELECT
				CASE
						
					WHEN
						TO_DAYS( t2.DUE_TIME ) = TO_DAYS( pay.DUE_TIME ) THEN
							em.EDOR_TYPE 
						END 
						FROM
							t_edor_main em
							INNER JOIN t_order_lrisk_log t1 ON em.CHANGE_ID = t1.CHANGE_ID
							INNER JOIN t_order_risk_pay_due_date t2 ON t1.RISK_ID = t2.RISK_ID 
							AND em.TENANT_ID = t1.TENANT_ID 
							AND t1.CHANGED_INDI = 'Y' 
						WHERE
							em.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
							AND lr.RISK_ID = t1.RISK_ID 
							AND em.STATUS = '1003' 
							AND em.INSERT_TIME < DATE_ADD(
							CASE
									
									WHEN t2.DUE_TIME IS NULL THEN
									tor.INSERT_TIME 
									WHEN tor.INSERT_TIME IS NULL THEN
									t2.DUE_TIME ELSE
								IF
									(
										DATE_FORMAT( t2.DUE_TIME, '%Y-%m-%d' ) > DATE_FORMAT( tor.INSERT_TIME, '%Y-%m-%d' ),
										DATE_FORMAT( t2.DUE_TIME, '%Y-%m-%d' ),
										DATE_FORMAT( tor.INSERT_TIME, '%Y-%m-%d' ) 
									) 
								END,
								INTERVAL 1 DAY 
							) 
						ORDER BY
							em.INSERT_TIME DESC,
							t2.DUE_TIME ASC 
							LIMIT 1 
						) 
					END AS riskStateCause,
					lr.CHARGE_STATE AS chargeState,
					lr.END_DATE endDate,
					lr.VALIDATE_DATE validateDate,
					pay.DUE_TIME payDueDate,
					lr.NEXT_PREM payDuePrem,
					IFNULL( tor.PREM, lr.NEXT_PREM ) duePrem,
				CASE
						tor.RENEWAL_STATUS 
						WHEN 2 THEN
						tor.PREM 
						WHEN 3 THEN
						tor.PREM 
					END paidPrem,
	tor.PAID_DATE paidDate,
	tor.PAY_MODE payMode,
	fun_getTenantDictValue ( 100013, 'CD105', tor.PAY_MODE ) payModeTitle,
	IFNULL( tor.RENEWAL_STATUS, '1' ) renewalStatus,
	tor.RENEWAL_STATUS renewalStatusTitle 
FROM
	t_order_lrisk lr
	INNER JOIN t_order_risk_pay_due_date pay ON lr.RISK_ID = pay.RISK_ID 
	AND lr.BUSI_ORDER_ID = pay.BUSI_ORDER_ID
	INNER JOIN t_order tr ON tr.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
	AND tr.MCOM_ID IN ( 100000, 100330, 100331, 2018112312575720686 ) 
	AND tr.MCOM_ID IN ( 100000, 100330, 100331, 2018112312575720686 )
	INNER JOIN t_order_life tl ON tl.BUSI_ORDER_ID = lr.BUSI_ORDER_ID
	INNER JOIN tb_manage_com tm ON tr.MCOM_ID = tm.MCOM_ID
	INNER JOIN t_sale_com ts ON tr.SCOM_ID = ts.SCOM_ID
	INNER JOIN t_broker tb ON tb.REG_ID = tr.SERVICE_BROKER_ID
	INNER JOIN t_tenant_product tp ON tp.PROD_ID = lr.PDT_ID
	INNER JOIN T_TENANT_SUPPLIER supplyier ON tr.SUPPLIER_ID = supplyier.SUPPLIER_ID
	INNER JOIN T_ORDER_HOLDER th ON th.BUSI_ORDER_ID = lr.BUSI_ORDER_ID
	INNER JOIN T_CUSTOMER tc ON tc.CUSTOMER_ID = th.CUSTOMER_ID
	LEFT JOIN t_order_renewal tor ON tor.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
	AND tor.DUE_TIME = pay.DUE_TIME 
	AND tor.PAY_NUM = pay.PAY_NUM 
	AND tor.RISK_ID = lr.RISK_ID 
	AND tor.RENEWAL_STATUS <> '4' 
WHERE
	( lr.RISK_STATE IN ( '1', '4' ) OR ( lr.RISK_STATE = '2' AND lr.RISK_STATE_CAUSE <> '03' ) ) 
	AND ( lr.CHARGE_PERIOD_VALUE > 1 OR ( lr.CHARGE_PERIOD_VALUE = 1 AND lr.COVERAGE_PERIOD_VALUE = 1 AND lr.AUTO_RENEW = '2' ) ) 
	AND lr.TENANT_ID = 100013 
ORDER BY
	mcomId 
	) tmp_count

 

修改后代码:

SELECT
	count( 0 ) 
FROM
	(
	SELECT
		lr.BUSI_ORDER_ID,
		tm.MCOM_ID mcomId,
		tm.MCOM_FNAME mcomFname,
		ts.SCOM_ID scomId,
		ts.SCOM_FNAME scomFname,
		tb.REG_ID brokerId,
		tb.BROKER_CODE brokerCode,
		tb.`NAME` brokerName,
		tc.`NAME` holderName,
		lr.tenant_id tenantId,
		tr.SUPPLIER_ID supplierId,
		supplyier.SUPPLIER_NAME supplierName,
		tl.POLICY_CODE policyCode,
		pay.POLICY_YEAR AS policyYear,
		tp.PROD_CODE prodCode,
		tp.PROD_NAME prodName,
		lr.COVERAGE_PERIOD AS coveragePeriod,
		lr.COVERAGE_PERIOD_VALUE coveragePeriodValue,
		lr.CHARGE_INTERVAL AS chargeInterval,
		lr.CHARGE_PERIOD AS chargePeriod,
		lr.CHARGE_PERIOD_VALUE chargePeriodValue,
		pay.PAY_NUM payNum,
		lr.RISK_STATE AS riskState,
		lr.RISK_STATE AS riskStateTitle,
		tor.COMM_AMT commAmt,
		tor.COMM_RATE commRate,
		lr.CHARGE_STATE AS chargeState,
		lr.END_DATE endDate,
		lr.VALIDATE_DATE validateDate,
		pay.DUE_TIME payDueDate,
		lr.NEXT_PREM payDuePrem,
		IFNULL( tor.PREM, lr.NEXT_PREM ) duePrem,
				CASE
						tor.RENEWAL_STATUS 
						WHEN 2 THEN
						tor.PREM 
						WHEN 3 THEN
						tor.PREM 
					END paidPrem,
	tor.PAID_DATE paidDate,
	tor.PAY_MODE payMode,
	fun_getTenantDictValue ( 100013, 'CD105', tor.PAY_MODE ) payModeTitle,
	IFNULL( tor.RENEWAL_STATUS, '1' ) renewalStatus,
	tor.RENEWAL_STATUS renewalStatusTitle 
FROM
	t_order_lrisk lr
	INNER JOIN t_order_risk_pay_due_date pay ON lr.RISK_ID = pay.RISK_ID 
	AND lr.BUSI_ORDER_ID = pay.BUSI_ORDER_ID
	INNER JOIN t_order tr ON tr.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
	AND tr.MCOM_ID IN ( 100000, 100330, 100331, 2018112312575720686 ) 
	AND tr.MCOM_ID IN ( 100000, 100330, 100331, 2018112312575720686 )
	INNER JOIN t_order_life tl ON tl.BUSI_ORDER_ID = lr.BUSI_ORDER_ID
	INNER JOIN tb_manage_com tm ON tr.MCOM_ID = tm.MCOM_ID
	INNER JOIN t_sale_com ts ON tr.SCOM_ID = ts.SCOM_ID
	INNER JOIN t_broker tb ON tb.REG_ID = tr.SERVICE_BROKER_ID
	INNER JOIN t_tenant_product tp ON tp.PROD_ID = lr.PDT_ID
	INNER JOIN T_TENANT_SUPPLIER supplyier ON tr.SUPPLIER_ID = supplyier.SUPPLIER_ID
	INNER JOIN T_ORDER_HOLDER th ON th.BUSI_ORDER_ID = lr.BUSI_ORDER_ID
	INNER JOIN T_CUSTOMER tc ON tc.CUSTOMER_ID = th.CUSTOMER_ID
	LEFT JOIN t_order_renewal tor ON tor.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
	AND tor.DUE_TIME = pay.DUE_TIME 
	AND tor.PAY_NUM = pay.PAY_NUM 
	AND tor.RISK_ID = lr.RISK_ID 
	AND tor.RENEWAL_STATUS <> '4' 
WHERE
	( lr.RISK_STATE IN ( '1', '4' ) OR ( lr.RISK_STATE = '2' AND lr.RISK_STATE_CAUSE <> '03' ) ) 
	AND ( lr.CHARGE_PERIOD_VALUE > 1 OR ( lr.CHARGE_PERIOD_VALUE = 1 AND lr.COVERAGE_PERIOD_VALUE = 1 AND lr.AUTO_RENEW = '2' ) ) 
	AND lr.TENANT_ID = 100013 
ORDER BY
	mcomId 
	) tmp_count
List<ExtTOrderLriskDetailRepVO> list = extTOrderRenewalMapper.orderList(reqVO);
		// 查询原因
for (ExtTOrderLriskDetailRepVO extTOrderLriskDetailRepVO : list) {
	if (extTOrderLriskDetailRepVO.getPayDueDate() != null || extTOrderLriskDetailRepVO.gettOrderRenewalInsertTime() != null) {
	    String riskStateCause = extTOrderRenewalMapper.selectRiskStateCauseBy(extTOrderLriskDetailRepVO.getRiskId(), extTOrderLriskDetailRepVO.getBusiOrderId(),
						extTOrderLriskDetailRepVO.getPayDueDate(),
						extTOrderLriskDetailRepVO.gettOrderRenewalInsertTime());
		extTOrderLriskDetailRepVO.setRiskStateCause(riskStateCause);
	}
}
SELECT
				CASE
					WHEN
						TO_DAYS( t2.DUE_TIME ) = TO_DAYS( #{payDueDate} )
					THEN
						em.EDOR_TYPE 
				END riskStateCause
			FROM
				t_edor_main em
				INNER JOIN t_order_lrisk_log t1 ON em.CHANGE_ID = t1.CHANGE_ID
				INNER JOIN t_order_risk_pay_due_date t2 ON t1.RISK_ID = t2.RISK_ID
				AND t1.BUSI_ORDER_ID = t2.BUSI_ORDER_ID
				AND em.TENANT_ID = t1.TENANT_ID
				AND t1.CHANGED_INDI = 'Y'
			WHERE
				em.BUSI_ORDER_ID = #{busiOrderId}    
				AND  t1.RISK_ID = #{riskId}
				AND em.STATUS = '1003'
				AND em.INSERT_TIME <![CDATA[<]]>  DATE_ADD(
				CASE
					WHEN
						t2.DUE_TIME IS NULL
					THEN
						#{tOrderRenewalInsertTime}
					WHEN
						#{tOrderRenewalInsertTime} IS NULL
					THEN
						t2.DUE_TIME ELSE
					IF(
							DATE_FORMAT( t2.DUE_TIME, '%Y-%m-%d' ) <![CDATA[>]]> DATE_FORMAT(#{tOrderRenewalInsertTime}, '%Y-%m-%d' ),
							DATE_FORMAT( t2.DUE_TIME, '%Y-%m-%d' ),
							DATE_FORMAT( #{tOrderRenewalInsertTime}, '%Y-%m-%d' )
					)
				END,
				INTERVAL 1 DAY )
			ORDER BY
				em.INSERT_TIME DESC ,t2.DUE_TIME asc
			LIMIT 1  ;

 

<think>嗯,用户问的是VBA用ADODB查询SQL Server数据耗时问题。首先,我需要回忆一下ADODB在VBA中的使用方式,常见的性能瓶颈可能在哪里。用户可能已经会用基本的连接和查询,但遇到了速度慢的情况,需要优化。 首先,可能的原因有很多,比如网络延迟、查询语句效率低、数据量过大、或者没有正确使用连接参数。要一步步分析。常见的优化点包括连接字符串的配置,比如使用TCP/IP而不是命名管道,启用数据压缩等。然后,查询语句本身是否有优化的空间,比如是否缺少索引,或者返回的数据量过大,是否需要分页。 接下来,用户可能没有提到具体的情况,比如查询的数据量有多大,是第一次连接慢还是每次查询都慢。这时候可能需要考虑连接池的设置,是否每次查询都重新连接,导致频繁的握手过程。使用连接池可以复用连接,减少开销。 另外,数据返回的方式也可能影响性能,比如是否使用游标的类型是否正确。前向只读游标通常更快。还有,是否一次性获取所有数据,或者分块获取,比如设置Recordset的PageSize或者CacheSize。 还有可能,VBA本身处理数据的方式不够高效,比如在循环中逐行处理记录,这时候可以考虑将数据一次性读入数组,或者使用GetRows方法转换,减少交互次数。 此外,字段引用方式也可能影响速度,比如使用字段的索引号而不是名称,因为按名称查找会有额外的开销。 然后,需要考虑是否启用了异步查询,这样在等待结果时VBA可以处理其他任务,但用户可能更关心的是总体耗时,所以同步可能更直接,但异步可能让界面不冻结。 还有可能,SQL Server端的性能问题,比如是否有锁表,查询执行计划是否高效。用户可能需要检查执行计划,看看是否有全表扫描,缺少索引等情况。 总结下来,可能的解决步骤包括优化连接字符串、使用连接池、优化SQL查询、调整游标和锁定类型、批量获取数据、使用数组处理、检查网络和服务器性能等。需要逐一排查,给出具体的建议,比如示例代码,说明如何修改连接字符串,如何设置Recordset的参数,如何分页等。 最后,用户可能需要一个结构清晰的回答,分点列出可能的原因和解决方案,方便他们逐步尝试。同时,要确保建议的可行性,避免过于复杂的操作,特别是针对VBA的环境,可能用户对服务器端的调整权限有限,所以重点放在客户端和代码优化上。</think>在VBA中使用ADODB查询SQL Server耗时问题,可以通过以下系统性优化方案逐步解决: 一、连接层优化 1. 连接字符串配置示例: ```vba connStr = "Provider=SQLOLEDB;Data Source=服务器地址;" & _ "Initial Catalog=数据库名;User ID=用户名;Password=密码;" & _ "Network Library=DBMSSOCN;" & _ ' 强制使用TCP/IP协议 "Use Encryption for Data=False;" & _ "Packet Size=4096;" ' 适当增大数据包大小 ``` 2. 建议启用连接池(默认已启用),避免重复创建连接对象 二、查询执行优化 1. 参数化查询示例: ```vba Set cmd = New ADODB.Command cmd.CommandText = "SELECT * FROM Orders WHERE OrderDate > ?" cmd.Parameters.Append cmd.CreateParameter("date", adDate, adParamInput, , CDate("2023-01-01")) Set rs = cmd.Execute ``` 2. 游标和锁定类型优化: ```vba rs.CursorLocation = adUseClient ' 客户端游标 rs.CursorType = adOpenForwardOnly ' 只进游标 rs.LockType = adLockReadOnly ' 只读锁定 rs.Open Source:=cmd, ActiveConnection:=conn ``` 三、数据传输优化 1. 字段访问优化: ```vba ' 优先使用字段索引号(效率比字段名高20-30%) Do While Not rs.EOF Debug.Print rs.Fields(0).Value ' 索引号访问 rs.MoveNext Loop ``` 2. 批量数据处理建议: ```vba ' 使用GetRows方法将数据转为二维数组 Dim data As Variant data = rs.GetRows ' 全部数据一次性加载 rs.Close ' 处理数组数据(比逐行读取快5-10倍) For i = 0 To UBound(data, 2) For j = 0 To UBound(data, 1) Debug.Print data(j, i) Next Next ``` 四、分页查询方案(适合大数据量) ```vba Const PAGE_SIZE = 5000 Dim pageIndex As Long pageIndex = 0 Do Set rs = New ADODB.Recordset rs.Open "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) as RowNum, * FROM LargeTable) " & _ "WHERE RowNum BETWEEN " & (pageIndex * PAGE_SIZE + 1) & " AND " & ((pageIndex + 1) * PAGE_SIZE), _ conn, adOpenForwardOnly, adLockReadOnly If rs.EOF Then Exit Do ' 处理当前页数据 ProcessData rs.GetRows pageIndex = pageIndex + 1 rs.Close Loop ``` 五、性能监控建议 1. 在SQL Server端检查查询计划: ```sql SET STATISTICS TIME ON SET STATISTICS IO ON -- 执行目标查询 ``` 2. 在VBA中添加计时监控: ```vba Dim startTime As Double startTime = Timer ' 执行查询操作 Debug.Print "耗时:" & Round(Timer - startTime, 2) & "秒" ``` 典型优化效果对比: - 未优化查询(10万条记录):约12-15秒 - 优化后查询:可缩短至3-5秒 - GetRows+数组处理:可进一步降至1-2秒 注意事项: 1. 网络延迟对性能影响显著,内网环境建议保证带宽≥100Mbps 2. 复杂查询尽量在数据库端通过存储过程完成 3. 定期重建表索引(ALTER INDEX ALL ON TableName REBUILD) 4. 避免在循环中执行SQL查询(N+1查询问题) 通过以上多级优化策略组合实施,通常可以将查询性能提升3-10倍。建议从连接配置和查询方式入手,逐步实施各层优化措施。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值