这样的统计速度正常吗?

xf2gl 2004-12-01 11:10:08

现有物资类别表m_class,结构如下:

guid superior_guid class_name
1 1 办公用品
2 1 笔类
3 2 毛笔
4 2 钢笔


理论上可以支持无限级分类,现在有5000条类别,另外有两张表分别存储物资入库和领用记录,在统计所有物资库存的时候居然用了16分钟。机器配置为C2.5G,DDR521M,845GL主板,大家认为这样的速度正常吗?如果使用P4超线程是否会有较大的改观?欢迎大家踊跃发表意见,分数大大的有。
...全文
219 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
xf2gl 2004-12-01
  • 打赏
  • 举报
回复
引用:Yang_(扬帆破浪)
很难说
类别是5000条,但是决定因素是存储物资入库和领用记录的记录多少
从你的存储过程看,相关表的索引情况与速度应该有很大关系

请问怎么优化表的索引啊,能帮我想想吗?
xf2gl 2004-12-01
  • 打赏
  • 举报
回复
现在物资入库和物资领用的记录为0
Yang_ 2004-12-01
  • 打赏
  • 举报
回复
一般来说,统计功能的所用时间应该控制在40秒以内,所以你的应该有优化的空间
Yang_ 2004-12-01
  • 打赏
  • 举报
回复
很难说
类别是5000条,但是决定因素是存储物资入库和领用记录的记录多少
从你的存储过程看,相关表的索引情况与速度应该有很大关系


xf2gl 2004-12-01
  • 打赏
  • 举报
回复
'***********************************得到本期库存
strsql_in = "select sum(nums) as nums_in,sum(amounts) as amounts_in,avg(s_price) as avg_price from xc_material_in where ftime between '" & start_time & "' and '" & end_time & "'"
strsql_out = "select sum(nums) as nums_out,sum(amounts) as amounts_out from xc_material_out where ftime between '" & start_time & "' and '" & end_time & "'"
If class1 <> "" Then
strsql_in = strsql_in & " and class1='" & class1 & "'"
strsql_out = strsql_out & " and class1='" & class1 & "'"
End If
If class2 <> "" Then
strsql_in = strsql_in & " and class2='" & class2 & "'"
strsql_out = strsql_out & " and class2='" & class2 & "'"
End If
If class3 <> "" Then
strsql_in = strsql_in & " and class3='" & class3 & "'"
strsql_out = strsql_out & " and class3='" & class3 & "'"
End If
If class4 <> "" Then
strsql_in = strsql_in & " and class4='" & class4 & "'"
strsql_out = strsql_out & " and class4='" & class4 & "'"
End If
If class5 <> "" Then
strsql_in = strsql_in & " and class5='" & class5 & "'"
strsql_out = strsql_out & " and class5='" & class5 & "'"
End If
rs_in.Open strsql_in, Conn, 1, 1
rs_out.Open strsql_out, Conn, 1, 1

If Not (IsNull(rs_in("nums_in")) Or IsNull(rs_in("amounts_in"))) Then
arrdata(5) = rs_in("nums_in") '***********************本期入库数量
arrdata(6) = rs_in("amounts_in") '***********************本期入库金额
arrdata(7) = rs_in("avg_price") '***********************本期平均单价
rs_in.Close
Else
arrdata(5) = 0
arrdata(6) = 0
arrdata(7) = 0
rs_in.Close
End If

If Not (IsNull(rs_out("nums_out")) Or IsNull(rs_out("amounts_out"))) Then
arrdata(8) = rs_out("nums_out") '***********************本期出库数量
arrdata(9) = rs_out("amounts_out") '***********************本期出库金额
rs_out.Close
Else
arrdata(8) = 0
arrdata(9) = 0
rs_out.Close
End If

'***********************************结束


'***********************************得到期末库存
strsql_in = "select sum(nums) as nums_in,sum(amounts) as amounts_in,avg(s_price) as avg_price from xc_material_in where ftime <= '" & end_time & "'"
strsql_out = "select sum(nums) as nums_out,sum(amounts) as amounts_out from xc_material_out where ftime <= '" & end_time & "'"
If class1 <> "" Then
strsql_in = strsql_in & " and class1='" & class1 & "'"
strsql_out = strsql_out & " and class1='" & class1 & "'"
End If
If class2 <> "" Then
strsql_in = strsql_in & " and class2='" & class2 & "'"
strsql_out = strsql_out & " and class2='" & class2 & "'"
End If
If class3 <> "" Then
strsql_in = strsql_in & " and class3='" & class3 & "'"
strsql_out = strsql_out & " and class3='" & class3 & "'"
End If
If class4 <> "" Then
strsql_in = strsql_in & " and class4='" & class4 & "'"
strsql_out = strsql_out & " and class4='" & class4 & "'"
End If
If class5 <> "" Then
strsql_in = strsql_in & " and class5='" & class5 & "'"
strsql_out = strsql_out & " and class5='" & class5 & "'"
End If

rs_in.Open strsql_in, Conn, 1, 1
rs_out.Open strsql_out, Conn, 1, 1

If Not (IsNull(rs_in("nums_in")) Or IsNull(rs_in("amounts_in"))) Then
arrdata(10) = rs_in("nums_in") '***********************期末入库数量
arrdata(11) = rs_in("amounts_in") '***********************期末入库金额
arrdata(12) = FormatNumber(rs_in("avg_price"), 2) '***********************期末平均单价
rs_in.Close
Else
arrdata(10) = 0
arrdata(11) = 0
arrdata(12) = 0
rs_in.Close
End If

If Not (IsNull(rs_out("nums_out")) Or IsNull(rs_out("amounts_out"))) Then
arrdata(13) = rs_out("nums_out") '***********************期末出库数量
arrdata(14) = rs_out("amounts_out") '***********************期末出库金额
rs_out.Close
Else
arrdata(13) = 0
arrdata(14) = 0
rs_out.Close
End If

'***********************************结束


storage = arrdata
Conn.Close
Set rs_in = Nothing
Set rs_out = Nothing
Set Conn = Nothing
ErrorHandler:

End Function
xf2gl 2004-12-01
  • 打赏
  • 举报
回复
用VB写了一个com+组件,统计语句如下:

Public Function storage(ByVal start_time As Variant, ByVal end_time As Variant, ByVal class1 As String, _
ByVal class2 As String, ByVal class3 As String, ByVal class4 As String, ByVal class5 As String) As Variant
On Error GoTo ErrorHandler:

Dim strsql_in As String
Dim strsql_out As String
Dim Conn As ADODB.Connection
Dim rs_in As ADODB.Recordset
Dim rs_out As ADODB.Recordset

Set Conn = CreateObject("ADODB.Connection")
Set rs_in = CreateObject("adodb.recordset")
Set rs_out = CreateObject("adodb.recordset")
Conn.Open strODBCName(1)

If start_time = "" Then
start_time = Date
Else
start_time = CDate(start_time)
End If
If end_time = "" Then
end_time = Date
Else
end_time = CDate(end_time)
End If

class1 = SqlSafe(class1)
class2 = SqlSafe(class2)
class3 = SqlSafe(class3)
class4 = SqlSafe(class4)
class5 = SqlSafe(class5)

Dim arrdata(14)


'***********************************得到期初库存
strsql_in = "select sum(nums) as nums_in,sum(amounts) as amounts_in,avg(s_price) as avg_price from xc_material_in where ftime < ='" & start_time & "'"
strsql_out = "select sum(nums) as nums_out,sum(amounts) as amounts_out from xc_material_out where ftime < = '" & start_time & "'"
If class1 <> "" Then
strsql_in = strsql_in & " and class1='" & class1 & "'"
strsql_out = strsql_out & " and class1='" & class1 & "'"
End If
If class2 <> "" Then
strsql_in = strsql_in & " and class2='" & class2 & "'"
strsql_out = strsql_out & " and class2='" & class2 & "'"
End If
If class3 <> "" Then
strsql_in = strsql_in & " and class3='" & class3 & "'"
strsql_out = strsql_out & " and class3='" & class3 & "'"
End If
If class4 <> "" Then
strsql_in = strsql_in & " and class4='" & class4 & "'"
strsql_out = strsql_out & " and class4='" & class4 & "'"
End If
If class5 <> "" Then
strsql_in = strsql_in & " and class5='" & class5 & "'"
strsql_out = strsql_out & " and class5='" & class5 & "'"
End If
rs_in.Open strsql_in, Conn, 1, 1
rs_out.Open strsql_out, Conn, 1, 1

If Not (IsNull(rs_in("nums_in")) Or IsNull(rs_in("amounts_in"))) Then
arrdata(0) = rs_in("nums_in") '***********************期初入库数量
arrdata(1) = rs_in("amounts_in") '***********************期初入库金额
rs_in.Close
Else
arrdata(0) = 0
arrdata(1) = 0
rs_in.Close
End If

If Not (IsNull(rs_out("nums_out")) Or IsNull(rs_out("amounts_out"))) Then
arrdata(3) = rs_out("nums_out") '***********************期初出库数量
arrdata(4) = rs_out("amounts_out") '***********************期初出库金额
rs_out.Close
Else
arrdata(3) = 0
arrdata(4) = 0
rs_out.Close
End If

'***********************************结束


'***********************************得到平均价格
strsql_in = "select sum(s_price*nums)/sum(nums) as avg_price from xc_material_in where ftime > '1900-1-1 '"
If class1 <> "" Then
strsql_in = strsql_in & " and class1='" & class1 & "'"
End If
If class2 <> "" Then
strsql_in = strsql_in & " and class2='" & class2 & "'"
End If
If class3 <> "" Then
strsql_in = strsql_in & " and class3='" & class3 & "'"
End If
If class4 <> "" Then
strsql_in = strsql_in & " and class4='" & class4 & "'"
End If
If class5 <> "" Then
strsql_in = strsql_in & " and class5='" & class5 & "'"
End If
rs_in.Open strsql_in, Conn, 1, 1

If Not IsNull(rs_in("avg_price")) Then
arrdata(2) = FormatNumber(rs_in("avg_price"), 2) '***********************平均价格
rs_in.Close
Else
arrdata(2) = FormatNumber(0, 2)
rs_in.Close
End If

'***********************************结束
xf2gl 2004-12-01
  • 打赏
  • 举报
回复
定义了一个存储过程,把物资类别以另外一种结构存放在xc_material_class_temp中,统计的时候从这张表里读出物资类别ID:
CREATE procedure list_material_class

as

delete from xc_material_class_temp
insert into xc_material_class_temp(class1) (select guid from xc_material_class where superior_guid='0')

insert into xc_material_class_temp(class2) (select guid from xc_material_class where superior_guid in ((select guid from xc_material_class where superior_guid='0')))
insert into xc_material_class_temp(class3) (select guid from xc_material_class where superior_guid in (select guid from xc_material_class where superior_guid in ((select guid from xc_material_class where superior_guid='0'))))
insert into xc_material_class_temp(class4) (select guid from xc_material_class where superior_guid in (select guid from xc_material_class where superior_guid in (select guid from xc_material_class where superior_guid in ((select guid from xc_material_class where superior_guid='0')))))

update xc_material_class_temp set xc_material_class_temp.class3=xc_material_class.superior_guid from xc_material_class inner join xc_material_class_temp on (xc_material_class.guid=xc_material_class_temp.class4)
update xc_material_class_temp set xc_material_class_temp.class2=xc_material_class.superior_guid from xc_material_class inner join xc_material_class_temp on (xc_material_class.guid=xc_material_class_temp.class3)
update xc_material_class_temp set xc_material_class_temp.class1=xc_material_class.superior_guid from xc_material_class inner join xc_material_class_temp on (xc_material_class.guid=xc_material_class_temp.class2)


select * from xc_material_class_temp
GO
表xc_material_class_temp结构如下:

class1 class2 class3 class4 class5


xingfuniao 2004-12-01
  • 打赏
  • 举报
回复
sql有問題吧,才5000條而已,應該很快的
solidpanther 2004-12-01
  • 打赏
  • 举报
回复
不正常
Andy__Huang 2004-12-01
  • 打赏
  • 举报
回复
你的sql 語句是什麼寫的?
chd2001 2004-12-01
  • 打赏
  • 举报
回复
16分钟对你这样的数据量来说很不正常,你的语句是怎么写的,语句对统计的速度也很有影响的
skyinfo 2004-12-01
  • 打赏
  • 举报
回复
1、優化索引
2、在存儲過程中。可以考慮多用臨時表.簡化SQL語句
xf2gl 2004-12-01
  • 打赏
  • 举报
回复
郁闷啊~大家帮忙优化一下撒~~~
lh1979 2004-12-01
  • 打赏
  • 举报
回复
语句尽量少嵌套,少用IN,因为如果IN下的数据太多,系统查询计划越容易倾向于表扫描,还有如果用IN的话,系统会先生成结果集存放在tempdb,这样频繁I/O,浪费时间,
lh1979 2004-12-01
  • 打赏
  • 举报
回复
你的存储过程建的几个CLASS干什么用的阿,没看明白,
我看就光这个存储过程就要运行不少时间,竟然用了4重嵌套,
光这个我想就慢不少了,这边的语句肯定要优化,,还有条件尽量建索引,这样查询速度快
lh1979 2004-12-01
  • 打赏
  • 举报
回复
统计要16分钟,太慢了
vinsonshen 2004-12-01
  • 打赏
  • 举报
回复
当然这个快慢还与你的数据库记录多少有关系,和你的处理逻辑也有很大关系
vinsonshen 2004-12-01
  • 打赏
  • 举报
回复
试试:
你把语句写在查询分析器上,然后点菜单上“查询”---选“索引优化向导”--》然后按里面提示操作
xf2gl 2004-12-01
  • 打赏
  • 举报
回复
谁能帮我优化到40秒以内,200分相送,谢谢~

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧