SQL优化

[奔雷手]

select 12781002 AS IS_SELECTED,v.VIN,v.OWNER_NO,v.LICENSE,v.ENGINE_NO,v.BRAND,v.SERIES,v.MODEL,v.COLOR,v.APACKAGE,v.INSURANCE_ADVISOR,v.MAINTAIN_ADVISOR,v.NEXT_MAINTAIN_DATE,
(SELECT MAX(R.INSURANCE_END_DATE) FROM  TT_SERVICE_INSURANCE R  WHERE  R.entity_code=v.entity_code AND R.vin=v.vin AND R.IS_VALID = 12781001)INSURANCE_END_DATE,
(SELECT MIN(R.INSURANCE_BEGIN_DATE) FROM TT_SERVICE_INSURANCE R WHERE R.entity_code=v.entity_code AND R.vin=v.vin AND R.IS_VALID = 12781001)INSURANCE_BEGIN_DATE, 
v.last_maintain_advisor from TM_VEHICLE v where v.ENTITY_CODE='WD322800' order by v.VIN;

求解

[funxu]

一个办法是少用嵌套查询，改成临时表

[funxu]

还有是加索引

[奔雷手]

[Quote=引用 2 楼 funxu 的回复:]
还有是加索引
[/Quote]
这两方法 目前不行

[li_develop]

那你可以尝试把你需要的结果做成一个服务，在服务器启动后运行，定时整理，吧结果存起来，这样的效率最高，因为根本不需要消耗统计对比的开销，直接取出来的就是结果

[xiangaylian]

仅这条SQL语句而言，从逻辑上要优化的话，优化空间很小了。。

[奔雷手]

就是想请教 在这条SQL语句上 或者用别的SQL语句 有无方法 优化

[lnmhfeng]

帮顶，学习

[小宏]

帮顶，学习了！

[koumingjie]

在vin，IS_VALID，ENTITY_CODE，INSURANCE_BEGIN_DATE 字段再索引试试

[koumingjie]

在vin，IS_VALID，ENTITY_CODE，INSURANCE_BEGIN_DATE 字段上加索引试试

[koumingjie]

SELECT 12781002 AS IS_SELECTED,
       v.VIN,
       v.OWNER_NO,
       v.LICENSE,
       v.ENGINE_NO,
       v.BRAND,
       v.SERIES,
       v.MODEL,
       v.COLOR,
       v.APACKAGE,
       v.INSURANCE_ADVISOR,
       v.MAINTAIN_ADVISOR,
       v.NEXT_MAINTAIN_DATE,
       R.INSURANCE_END_DATE,
       R.INSURANCE_BEGIN_DATE,
       v.last_maintain_advisor	
FROM   TM_VEHICLE v
       LEFT JOIN (
                SELECT entity_code,
                       vin,
                       IS_VALID,
                       MAX(R.INSURANCE_END_DATE) AS INSURANCE_END_DATE,
                       MIN(R.INSURANCE_BEGIN_DATE) AS INSURANCE_BEGIN_DATE
                FROM   TT_SERVICE_INSURANCE
                GROUP BY
                       entity_code,
                       vin,
                       IS_VALID
            ) R
            ON  R.entity_code = v.entity_code
            AND R.vin = v.vin
            AND R.IS_VALID = 12781001
WHERE  v.ENTITY_CODE = 'WD322800'
ORDER BY
       v.VIN