麻烦各位帮我看看这个存储过程,急!!
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>