麻烦各位帮我看看这个存储过程,急!!

qqok 2004-04-07 11:16:55

在数据比较少的时候,程序运行是正常的.但是当数据比较大的时候,出现错误:
出错信息为:[Microsoft][ODBC SQL Server Driver]超时已过期.
麻烦各位帮我看看,应该如何优化,加快数据读取速度.非常感谢!!
说明:
mysp_phsTotal --存储过程
companyVIEW --视图
Company--代理商表
Phs_Num--小灵通表

程序运行后得到的效果如下所示:

http://www.65288.com/bb.htm
============================================================
1.mysp_phsTotal

CREATE proc mysp_phsTotal
@uptime1 varchar(50),
@uptime2 varchar(50),
@fnote int
as
declare @sql varchar(8000)
set @sql='select companyid as [代理\数量/镇区] '
select @sql=@sql+',sum(case phsBrand when '''+phsBrand+''' then phsNum else 0 end ) '+ phsBrand
from (select distinct phsBrand from companyVIEW ) aaaa
select @sql=@sql+' ,sum(phsNum ) as 累计放号量 into #代理商放号量 From companyVIEW where convert(varchar(50), phsUpdate ,111) between ''' + @uptime1 + ''' and ''' + @uptime2 + ''' and note = ' + convert(varchar(10),@fnote) +' group by companyid order by 累计放号量 desc select * from #代理商放号量 union all select top 1 companyid=''累计放号量'','
select @sql=@sql+ phsBrand+'=(select sum(phsNum ) from companyVIEW where convert(varchar(50), phsUpdate ,111 ) between ''' + @uptime1 + ''' and ''' + @uptime2 + ''' and phsBrand='''+phsBrand+''' and note = ' + convert(varchar(10),@fnote) +'),'
from (select distinct phsBrand from companyVIEW ) aaaa
select @sql=@sql+' sum(phsNum) as 累计放号量 From companyVIEW where convert(varchar(50),phsUpdate ,111) between ''' + @uptime1 + ''' and ''' + @uptime2 + ''' and note = ' + convert(varchar(10),@fnote) +''
exec(@sql)
GO

2.companyVIEW --视图 :即为两个表关联

3.代理商表Company
ID CompanyID note
----------- ---------------------------------
1 平记 1
2 国信 1
3 国懋 1
4 捷通 1
5 科达 1
6 天地 1
22 润讯 2
23 凤宇 2
24 鸿基 2
36 讯联 2
37 讯兴 2
38 耀华 2
39 易昊 2
54 横沥 3
55 天纬 3
66 石龙 3
67 石排 3



4.小灵通表Phs_Num

ID CompanyID PhsBrand PhsNum PhsUpDate
--------------------------------------------------------------
19225 平记 UT318 40 2003-09-04 00:00:00
19226 平记 UT618+ 10 2003-09-04 00:00:00
19227 润讯 700-UC 50 2003-09-04 00:00:00
19228 润讯 UT318 12 2003-09-04 00:00:00
19229 润讯 UT618+ 13 2003-09-04 00:00:00
19230 天地 UT318 10 2003-09-04 00:00:00
19231 天地 UT618 100 2003-09-04 00:00:00
19232 天纬 700-UC 70 2003-09-04 00:00:00
19233 天纬 UT618 123 2003-09-04 00:00:00

5.网页代码

<%
Dim adoRs,Up_date1,Up_date2,fnote,cc
'up_date1 = "2003-05-13 10:46:00"
'up_date2 = "2003-05-13 10:58:00"
up_date1 = wf_DateToChar(request("sdate"),"long")
up_date2 = wf_DateToChar(request("edate"),"long")
set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = conn
cmdSp.CommandTimeout=1000
'response.write up_date1&" "&up_date2&" "&fnote
'response.end
Set adoRs = conn.execute("mysp_phsTotal '" & up_date1 & "','" & up_date2 & "','"&fnote&"'")
%>


<table width="775" border="0" cellspacing="0" cellpadding="0" >
<tr>
<td colspan="3"> <div align="center"><%=cc%>代理商放号量统计表</div></td>
</tr>
<tr>
<td height="8" colspan="3"></td>
</tr>
<tr>
<td width="39%" height="22"> 日期:<%=wf_DateToChar(now(),"long")%> </td>
<td width="38%"><a href="../town/ok.xls" target="_blank"> </a>累计数日期:<%=up_date1%>-<%=up_date2%></td>
<td width="23%"><a href="total.xls" target="_blank">生成Excel</a></td>
</tr>
</table>
<table width="1000" height="8" border="1" cellpadding="0" cellspacing="0" bordercolordark="#FFFFFF" bordercolorlight="#000000" >
<%
dim n
n=adors.fields.count
response.write "<tr>"
for i = 0 to n
If i < n Then
response.write "<td width=100 align='center'>" & adors.fields(i).name & "</td>"
End If
Next

response.write "</tr>"

while Not adoRS.EOF
response.write "<tr>"
for i = 0 to n
If i <> n Then
aa = adors.fields(i).value
If isnumeric(adors.fields(i).value) Then
If aa =0 Then aa = " "
End If
response.write "<td width=50>" & aa & "</td>"
End If
Next
response.write "</tr>"
adoRS.MoveNext
wend

%>
</table>

...全文
16 点赞 收藏 27
写回复
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
--结果是:
2003/09/04 不在范围内 1 .......


而这个日期显然是在统计范围内的,你的存储过程却统计不进去.
回复
--以你给我的数据为例,执行下面的查询:

select convert(varchar(50),phsupdate,111)
,case when convert(varchar(50),phsupdate,111) between '2003/09/1' and '2004/09/1'
then '在范围内' else '不在范围内' end
,* from companyview
where note=1
and companyid='科达' and phsbrand='[UT218]'
回复
你自己那个统计结果才是错的,你仔细检查一下.
回复
qqok 2004-04-08
但是我发现结果是错的.
CREATE proc mysp_phsTotal
@uptime1 varchar(50),
@uptime2 varchar(50),
@fnote int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+phsBrand+'=sum(case phsBrand when '''+phsBrand+''' then phsNum else 0 end )'
from (select distinct phsBrand from CompanyVIEW where phsBrand<>'[]')a

set @sql='select [代理\数量/镇区]=case grouping(companyid) when 1 then ''累计放号量'' else companyid end'+@sql+'
,累计放号量=sum(phsNum )
from CompanyVIEW
where note='+cast(@fnote as varchar)+'
and phsUpdate between '''+@uptime1+''' and '''+@uptime2+'''
group by CompanyID with rollup'
exec(@sql)
GO
回复
清晨光 2004-04-08
我也来试试啊!!
up
回复
qqok 2004-04-08
CREATE proc mysp_phsTotal
@uptime1 varchar(50),
@uptime2 varchar(50),
@fnote int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+phsBrand+'=sum(case phsBrand when '''+phsBrand+''' then phsNum else 0 end )'
from (select distinct phsBrand from CompanyVIEW where phsBrand<>'[]')a

set @sql='select [代理\数量/镇区]=case grouping(companyid) when 1 then ''累计放号量'' else companyid end'+@sql+'
,累计放号量=sum(phsNum )
from CompanyVIEW
where note='+cast(@fnote as varchar)+'
and phsUpdate between '''+@uptime1+''' and '''+@uptime2+'''
group by CompanyID with rollup'
exec(@sql)
GO


这次可以了.
回复
qqok 2004-04-08
我来试试先.
回复
--跟视图没什么关系啊? 你看我的测试

--测试

--测试数据
create table Company(ID int,CompanyID char(10),note int)
insert company select 1,'平记',1
union all select 2,'国信',1
union all select 3,'国懋',1
union all select 4,'捷通',1
union all select 5,'科达',1
union all select 6,'天地',1
union all select 22,'润讯',2
union all select 23,'凤宇',2
union all select 24,'鸿基',2
union all select 36,'讯联',2
union all select 37,'讯兴',2
union all select 38,'耀华',2
union all select 39,'易昊',2
union all select 54,'横沥',3
union all select 55,'天纬',3
union all select 66,'石龙',3
union all select 67,'石排',3

create table Phs_Num(ID int,CompanyID char(10),PhsBrand varchar(10),PhsNum int,PhsUpDate datetime)
insert Phs_Num select 19225,'平记','UT318',40,'2003-09-04 00:00:00'
union all select 19226,'平记','UT618+',10,'2003-09-04 00:00:00'
union all select 19227,'润讯','700-UC',50,'2003-09-04 00:00:00'
union all select 19228,'润讯','UT318',12,'2003-09-04 00:00:00'
union all select 19229,'润讯','UT618+',13,'2003-09-04 00:00:00'
union all select 19230,'天地','UT318',10,'2003-09-04 00:00:00'
union all select 19231,'天地','UT618',100,'2003-09-04 00:00:00'
union all select 19232,'天纬','700-UC',70,'2003-09-04 00:00:00'
union all select 19233,'天纬','UT618',123,'2003-09-04 00:00:00'
go

--视图
CREATE VIEW dbo.CompanyVIEW
AS
SELECT dbo.Company.note, dbo.Phs_Num.ID, dbo.Phs_Num.PhsNum,
dbo.Phs_Num.PhsUpDate, '[' + dbo.Phs_Num.PhsBrand + ']' as Phsbrand, dbo.Company.CompanyID
FROM dbo.Company INNER JOIN
dbo.Phs_Num ON dbo.Company.CompanyID = dbo.Phs_Num.CompanyID
go

--存储过程
CREATE proc mysp_phsTotal
@uptime1 varchar(50),
@uptime2 varchar(50),
@fnote int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+phsBrand+'=sum(case phsBrand when '''+phsBrand+''' then phsNum else 0 end )'
from (select distinct phsBrand from CompanyVIEW where phsBrand<>'[]')a

set @sql='select [代理\数量/镇区]=case grouping(companyid) when 1 then ''累计放号量'' else companyid end'+@sql+'
,累计放号量=sum(phsNum )
from CompanyVIEW
where note='+cast(@fnote as varchar)+'
and phsUpdate between '''+@uptime1+''' and '''+@uptime2+'''
group by CompanyID with rollup'
exec(@sql)
GO

--调用
exec mysp_phsTotal '2003/09/1','2004/09/1',1
go

--删除测试
drop table Company,Phs_Num
drop view CompanyVIEW
drop proc mysp_phsTotal

/*--测试结果
代理\数量/镇区 700-UC UT318 UT618 UT618+ 累计放号量
---------- ----------- ----------- ----------- ----------- -----------
平记 0 40 0 10 50
天地 0 10 100 0 110
累计放号量 0 50 100 10 160

(所影响的行数为 3 行)
--*/

回复
qqok 2004-04-08
Phs_Num 表的 PhsBrand 字段没有空值,还是出错.

噢,不好意思,这个视图是有用的.

CREATE VIEW dbo.CompanyVIEW
AS
SELECT dbo.Company.note, dbo.Phs_Num.ID, dbo.Phs_Num.PhsNum,
dbo.Phs_Num.PhsUpDate, '[' + dbo.Phs_Num.PhsBrand + ']' as Phsbrand, dbo.Company.CompanyID
FROM dbo.Company INNER JOIN
dbo.Phs_Num ON dbo.Company.CompanyID = dbo.Phs_Num.CompanyID


回复
snowcloud2002 2004-04-08
高!!实在是高!
回复
qqok 2004-04-08
TO:zjcxc(: 邹建 :)
不好意思,我仔细检查了一下,结果发现是我不小心搞错了.
邹建大哥,你的热心帮助,我无以回报,只能以分相许,万分感谢!!!
因本贴分数不够200分,我将发个新贴,请邹建大哥进去接分.
同时多谢各位的指点!
回复
qqok 2004-04-07
快来人啊!!
回复
qqok 2004-04-07
UP UP UP!!!
回复
qqok 2004-04-07
UP
回复
应该是检查 Phs_Num 表的 PhsBrand 字段是否有空值,如果有,就会出这个错误.
回复
你的company表中有空字段吗?
回复
qqok 2004-04-07
exec mysp_phsTotal '2003/09/1','2004/09/1',1
go

服务器: 消息 1038,级别 15,状态 3,行 1
不能使用空白的对象或列名。如果必要,请使用一个空格。
服务器: 消息 1038,级别 15,状态 1,行 1
不能使用空白的对象或列名。如果必要,请使用一个空格。
回复
qqok 2004-04-07
多谢各位,测试一下.
回复
CCEO 2004-04-07
索引:

Phs_Num:(PhsUpDate,CompanyID)

Company:(CompanyID,note) ---数据少,可以不建
回复
在两个表的note,CompanyID,phsUpdate上各自建立索引
回复
发动态
发帖子
ASP
创建于2007-09-28

2.8w+

社区成员

ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
申请成为版主
社区公告
暂无公告