我用的是下面的sql语句,可是执行起来特别慢,执行一个功能要一分钟左右
谁能帮帮我给优化一下啊
select count(distinct(DI.LoginName)) as DealerCount
from AC_DealerBaseInfo as DI,AC_DealerSeries as DS,[360che]..[AC_CarModel_Series] as CS,[Franchiser]..[AC_ChinaCityRelation] as CC
where
DI.LoginName=DS.LoginName
and
DS.CarSeriesId=CS.SeriesId
and
DI.CityId=CC.F_Id
and CC.F_ProvinceId=@F_ProvinceId
and CS.BrandId=@BrandId
and DI.IsCharge=@IsCharge
and DI.IsEnable=@IsEnableselect count(distinct(DI.LoginName)) as DealerCount
from AC_DealerBaseInfo as DI,AC_DealerSeries as DS,[360che]..[AC_CarModel_Series] as CS,[Franchiser]..[AC_ChinaCityRelation] as CC
where DI.LoginName=DS.LoginName
and DS.CarSeriesId=CS.SeriesId
and DI.CityId=CC.F_Id
and CC.F_ProvinceId=@F_ProvinceId
and BrandId=@BrandId
select count(distinct(BP.LoginName)) as AllPass from [360Franchiser]..[AC_DealerBaseInfo] as BI,AC_DealerSeries as DS,[360che]..[AC_CarModel_Series] as CS, [360Franchiser]..[AC_CheckExtendDealerBaseInfoPic] as BP,[Franchiser]..[AC_ChinaCityRelation] as CC
where BI.LoginName=BP.LoginName and BI.CityId=CC.F_Id and BI.LoginName=DS.LoginName and DS.CarSeriesId=CS.SeriesId
and OperateType='Pass' and BI.LoginName not in(select LoginName from [360Franchiser]..[AC_CheckExtendDealerBaseInfoPic] where OperateType <> 'Pass' or isnull(OperateType,'')='')
and CC.F_ProvinceId=@F_ProvinceId and CS.BrandId=@BrandId and BI.IsCharge=@IsCharge and BI.IsEnable=@IsEnable
谁能帮帮我给优化一下啊
select count(distinct(DI.LoginName)) as DealerCount
from AC_DealerBaseInfo as DI,AC_DealerSeries as DS,[360che]..[AC_CarModel_Series] as CS,[Franchiser]..[AC_ChinaCityRelation] as CC
where
DI.LoginName=DS.LoginName
and
DS.CarSeriesId=CS.SeriesId
and
DI.CityId=CC.F_Id
and CC.F_ProvinceId=@F_ProvinceId
and CS.BrandId=@BrandId
and DI.IsCharge=@IsCharge
and DI.IsEnable=@IsEnableselect count(distinct(DI.LoginName)) as DealerCount
from AC_DealerBaseInfo as DI,AC_DealerSeries as DS,[360che]..[AC_CarModel_Series] as CS,[Franchiser]..[AC_ChinaCityRelation] as CC
where DI.LoginName=DS.LoginName
and DS.CarSeriesId=CS.SeriesId
and DI.CityId=CC.F_Id
and CC.F_ProvinceId=@F_ProvinceId
and BrandId=@BrandId
select count(distinct(BP.LoginName)) as AllPass from [360Franchiser]..[AC_DealerBaseInfo] as BI,AC_DealerSeries as DS,[360che]..[AC_CarModel_Series] as CS, [360Franchiser]..[AC_CheckExtendDealerBaseInfoPic] as BP,[Franchiser]..[AC_ChinaCityRelation] as CC
where BI.LoginName=BP.LoginName and BI.CityId=CC.F_Id and BI.LoginName=DS.LoginName and DS.CarSeriesId=CS.SeriesId
and OperateType='Pass' and BI.LoginName not in(select LoginName from [360Franchiser]..[AC_CheckExtendDealerBaseInfoPic] where OperateType <> 'Pass' or isnull(OperateType,'')='')
and CC.F_ProvinceId=@F_ProvinceId and CS.BrandId=@BrandId and BI.IsCharge=@IsCharge and BI.IsEnable=@IsEnable
解决方案 »
- 100 分求助一存储过程
- exec内部执行变量语句
- 问一个问题,怎么把一台电脑的SQL操作转向另一台机器上。
- 这种存储进程不能通过程序返回记录结果集吗?请高人指点,一直未解决
- OpenDataSource问题,请教邹大驾
- 在存储过程中如何把变量作为表字段查询
- 能将列名不同但类型一样的值从另一个表中导入进来吗?
- SQLserver数据库被插入JS代码 如何整个数据库替换字符串
- 有谁对VB+Access开发比较熟悉的,请帮我看看贴子。在VB版和Access版都放了两天了,没有得到解答。您要是给圆满解决了,我到本版来给您送
- 关于对多个表同时写数据的问题,请教各位高手?谢谢!!
- 存储过程循环结果并插入新表
- sql server 2008备份证书的时候提示无法写入文件
isnull那个使用是因为每个LoginName 在该表会有三条数据,当该LoginName对应的TYpe都是Pass的时候才通过,否则是不通过
全国所有的省份,每个省份走一下上面的sql语句,看了一下大约每个省份要两秒钟……
第二:在多表连接用到inner join 的时候,如果tbB表数据大于tbA表的时候,语句尽可能写成select * from tbA as a inner hash join tbB as b on a.字段=b.字段对数据的检索效率有一定的提高。个人建议,仅供参考。