表的记录在16万左右,存储过程如下:
CREATE procedure kufangChuRuKuReport_KUCUN_bgy
@FromTime varchar(50),
@ToTime varchar(50),
@czy varchar(50)
as
begin
set nocount on
declare @sjcdrq varchar(50)
select @sjcdrq=convert(varchar(4),year(riqi))+'-'+convert(varchar(2),month(riqi))+'-'+convert(varchar(2),day(riqi)) from kf_wlcundang where year(riqi)=year(@fromtime) and month(riqi)=month(@fromtime) and (bgybh=@czy) select x.kfbh,x.bm,x.gysmc,x.lbjdh,x.lbjmc,x.ggxh,x.dw,x.sfjcj,x.shlx,x.jhjg,qc.QCnum,bin.incount,bout.outcount,(isnull(qc.QCnum,0)+isnull(bin.incount,0)-isnull(bout.outcount,0)) as StoreAmount from kf_bgyqcsj_view x
left join
(select kfbh,bm,lbjdh,kcsl as QCnum from kf_wlcundang where (RiQi=@sjcdrq) and (bgybh=@czy) ) qc
on x.kfbh=qc.kfbh and x.bm=qc.bm and x.lbjdh=qc.lbjdh
left join
(select tpind.lbjdh,tpind.bm,tpind.kfbh,
sum(tpind.rksl) as inCount
from kf_billin tpin left join kf_billindetail tpind on tpin.rkdh=tpinD.rkdh
left join kf_bgyqcsj_view tp1 on tpinD.lbjdh=tp1.lbjdh and tpind.bm=tp1.bm and tpind.kfbh=tp1.kfbh
where (tpin.isfinished=1) and(tpin.rkrq >= @FromTime and tpin.rkrq <= @ToTime) and (tp1.bgybh=@czy)
group by tpinD.lbjdh,tpind.bm,tpind.kfbh ) bin
on x.kfbh=bin.kfbh and x.bm=bin.bm and x.lbjdh=bin.lbjdh
left join
(select outd.bm,outd.lbjdh,outd.kfbh,
sum(outd.cksl) as outCount
from kf_billout bout left join kf_billoutdetail outd on bout.ckdh=outd.ckdh
left join kf_bgyqcsj_view tp1 on outd.lbjdh=tp1.lbjdh and outd.bm=tp1.bm and outd.kfbh=tp1.kfbh
where (bout.isfinished=1) and(bout.ckrq >= @FromTime and bout.ckrq <= @ToTime) and (tp1.bgybh=@czy)
group by outd.lbjdh,outd.bm,outd.kfbh) bout
on bout.kfbh=x.kfbh and bout.bm=x.bm and bout.lbjdh=x.lbjdh
where x.bgybh=@czy
order by x.kfbh,x.bm,x.lbjdh set nocount off
end
GOVB调用程序如下:
bomwldy.CommandTimeout = 0
bomwldy.ConnectionTimeout = 0
bomwldy.ConnectionString = kfConnectString
bomwldy.RecordSource = "kufangChuRuKuReport_KUCUN_bgy '" & DTPicker1.Value & "','" & DTPicker2.Value & "','" & Trim(bgymc) & "'"
bomwldy.Refresh
Set DataGrid1.DataSource = bomwldy
DataGrid1.Refresh
CREATE procedure kufangChuRuKuReport_KUCUN_bgy
@FromTime varchar(50),
@ToTime varchar(50),
@czy varchar(50)
as
begin
set nocount on
declare @sjcdrq varchar(50)
select @sjcdrq=convert(varchar(4),year(riqi))+'-'+convert(varchar(2),month(riqi))+'-'+convert(varchar(2),day(riqi)) from kf_wlcundang where year(riqi)=year(@fromtime) and month(riqi)=month(@fromtime) and (bgybh=@czy) select x.kfbh,x.bm,x.gysmc,x.lbjdh,x.lbjmc,x.ggxh,x.dw,x.sfjcj,x.shlx,x.jhjg,qc.QCnum,bin.incount,bout.outcount,(isnull(qc.QCnum,0)+isnull(bin.incount,0)-isnull(bout.outcount,0)) as StoreAmount from kf_bgyqcsj_view x
left join
(select kfbh,bm,lbjdh,kcsl as QCnum from kf_wlcundang where (RiQi=@sjcdrq) and (bgybh=@czy) ) qc
on x.kfbh=qc.kfbh and x.bm=qc.bm and x.lbjdh=qc.lbjdh
left join
(select tpind.lbjdh,tpind.bm,tpind.kfbh,
sum(tpind.rksl) as inCount
from kf_billin tpin left join kf_billindetail tpind on tpin.rkdh=tpinD.rkdh
left join kf_bgyqcsj_view tp1 on tpinD.lbjdh=tp1.lbjdh and tpind.bm=tp1.bm and tpind.kfbh=tp1.kfbh
where (tpin.isfinished=1) and(tpin.rkrq >= @FromTime and tpin.rkrq <= @ToTime) and (tp1.bgybh=@czy)
group by tpinD.lbjdh,tpind.bm,tpind.kfbh ) bin
on x.kfbh=bin.kfbh and x.bm=bin.bm and x.lbjdh=bin.lbjdh
left join
(select outd.bm,outd.lbjdh,outd.kfbh,
sum(outd.cksl) as outCount
from kf_billout bout left join kf_billoutdetail outd on bout.ckdh=outd.ckdh
left join kf_bgyqcsj_view tp1 on outd.lbjdh=tp1.lbjdh and outd.bm=tp1.bm and outd.kfbh=tp1.kfbh
where (bout.isfinished=1) and(bout.ckrq >= @FromTime and bout.ckrq <= @ToTime) and (tp1.bgybh=@czy)
group by outd.lbjdh,outd.bm,outd.kfbh) bout
on bout.kfbh=x.kfbh and bout.bm=x.bm and bout.lbjdh=x.lbjdh
where x.bgybh=@czy
order by x.kfbh,x.bm,x.lbjdh set nocount off
end
GOVB调用程序如下:
bomwldy.CommandTimeout = 0
bomwldy.ConnectionTimeout = 0
bomwldy.ConnectionString = kfConnectString
bomwldy.RecordSource = "kufangChuRuKuReport_KUCUN_bgy '" & DTPicker1.Value & "','" & DTPicker2.Value & "','" & Trim(bgymc) & "'"
bomwldy.Refresh
Set DataGrid1.DataSource = bomwldy
DataGrid1.Refresh
解决方案 »
- VB 用Image控件装载图片后,如何截取?
- 工程组下不能加载工程的问题!!!急!!!!!
- 数据导出文本文件
- 请问,怎样做活动工具栏?
- DataGrid控件和Adodc.recordset中某行数据的获得?
- 我用API做了一个动态生成菜单的工具,但菜单在MDI子窗体中不能显示?为什么?
- Data控件与SQL远程数据库
- 用Put写文件的问题
- 关于注册表删除一个键值的问题——我的代码怎么不行?50分奉上!50分奉上!50分奉上!
- 关于Netmeeting...
- TreeVeiw中对子树中的信息进行分类?求助啊!?谢谢!
- 菜鸟求救--vb读取excel数据,并按照想要的格式打印--最后编译成exe 我菜的不行 所以最好有代码~~
这种情况一时不好说,你可以逐段逐点来排查.比如,把程序在服务器上运行看看是否还这样,或者干脆新建一个VB工程,只把能运行这个查询的必要代码和引用等加进来,再运行看看.如果都出现这样的问题,就是系统的事,备份数据库之后,卸载再重装,还这样,就把VB也卸载再重装(免得有可能是VB用的数据库驱动程序的问题).
dbcc dbreindex('表名','',80) --80表示填充因子