第一条select a.hphm,
b.dmsm1 as clzl,
c.dmsm1 as jdczt,
a.YXQZ,
a.QZBFQZ,
a.BXZZRQ
from VEHICLE a, vio_code b, vio_code c
where 1 = 1 and a.hphm = 'XXXXXX' and a.hpzl = '01' and
substr(a.fdjh, -4, 4) = 'XXXXXXX' and b.dmz = '01' and b.dmlb = '6' and
c.dmz =
(select zt from vehicle where hphm = 'XXXXXX' and hpzl = '01') and
c.dmlb = '32'第二条select sfzmhm,
(select dmsm1
from vio_code
where dmlb = '25' and
dmz = (select zt from drv_driver where sfzmhm = '11111111')) as dmsm1,
JF,
YXQZ,
CFRQ,
QFRQ
from DRV_DRIVER
where 1 = 1 and sfzmhm = '11111111' and dabh = '111111111'
这两条中VEHICLE 和DRV_DRIVER是百万级表,并且都是视图...这两个视图是不能变的(原系统需要),现在执行大概都是30m左右,求更快的速度...?谢谢
b.dmsm1 as clzl,
c.dmsm1 as jdczt,
a.YXQZ,
a.QZBFQZ,
a.BXZZRQ
from VEHICLE a, vio_code b, vio_code c
where 1 = 1 and a.hphm = 'XXXXXX' and a.hpzl = '01' and
substr(a.fdjh, -4, 4) = 'XXXXXXX' and b.dmz = '01' and b.dmlb = '6' and
c.dmz =
(select zt from vehicle where hphm = 'XXXXXX' and hpzl = '01') and
c.dmlb = '32'第二条select sfzmhm,
(select dmsm1
from vio_code
where dmlb = '25' and
dmz = (select zt from drv_driver where sfzmhm = '11111111')) as dmsm1,
JF,
YXQZ,
CFRQ,
QFRQ
from DRV_DRIVER
where 1 = 1 and sfzmhm = '11111111' and dabh = '111111111'
这两条中VEHICLE 和DRV_DRIVER是百万级表,并且都是视图...这两个视图是不能变的(原系统需要),现在执行大概都是30m左右,求更快的速度...?谢谢
(select dmsm1 from vio_code where dmz = '01' and dmlb = '6') as clzl,
(select dmsm1 from vio_code where dmz = a.zt and dmlb = '32') as jdczt,
a.YXQZ,
a.QZBFQZ,
a.BXZZRQ
from VEHICLE a
where a.hphm = 'XXXXXX'
and a.hpzl = '01'
and substr(a.fdjh, -4, 4) = 'XXXXXXX' ;
b.dmsm1 as clzl,
c.dmsm1 as jdczt,
a.YXQZ,
a.QZBFQZ,
a.BXZZRQ
from vio_code b, vio_code c,VEHICLE a
where 1 = 1 and
c.dmz =
(select zt from vehicle where hphm = 'XXXXXX' and hpzl = '01') and
substr(a.fdjh, -4, 4) = 'XXXXXXX' and
a.hphm = 'XXXXXX' and
a.hpzl = '01'
and c.dmlb = '32'
and b.dmz = '01'
and b.dmlb = '6'
select a.hphm,
b.dmsm1 as clzl,
c.dmsm1 as jdczt,
a.YXQZ,
a.QZBFQZ,
a.BXZZRQ
from vio_code b, vio_code c,VEHICLE a
where 1 = 1 and
c.dmz =
(select zt from vehicle where hphm = 'XXXXXX' and hpzl = '01')
and
substr(a.fdjh, -4, 4) = 'XXXXXXX' and
a.hphm = 'XXXXXX' and
a.hpzl = '01'
and c.dmlb = '32'
and b.dmz = '01'
and b.dmlb = '6'
(select dmsm1
from vio_code
where dmz = (select zt from drv_driver where sfzmhm = '11111111')
and dmlb = '25'
) as dmsm1,
JF,
YXQZ,
CFRQ,
QFRQ
from DRV_DRIVER
where 1 = 1 and sfzmhm = '11111111' and dabh = '111111111'
第一:从硬件来优化(主要是内存 还有一个就是硬件存储设备)
第二:从操作系统来优化:(如可以适当的加大一点缓存,如果是Linux的话可以把swap交换区改大点.)
第三;从程序来优化.
第四:从数据库层来优化,对经常做查询的字段建力索引,这样可以提高查询的速度,还有一个就是优化一下配置文件,像你这种情况可以把你的缓存调大.
select A.sfzmhm,
B.dmsm1,
A.JF,
A.YXQZ,
A.CFRQ,
A.QFRQ
from DRV_DRIVER A left join vio_code B on A.zt=B.dmz and B.dmlb='25'
where sfzmhm = '11111111' and dabh = '111111111'
;
select a.hphm,
b.dmsm1 as clzl,
c.dmsm1 as jdczt,
a.YXQZ,
a.QZBFQZ,
a.BXZZRQ
from VEHICLE a, vio_code b, vio_code c
where a.hphm = 'XXXXXX' and a.hpzl = '01' and
substr(a.fdjh, -4, 4) = 'XXXXXXX' and b.dmz = '01' and b.dmlb = '6' and
c.dmz =a.zt and c.dmlb = '32'