数据库太大,我发不过去呀。。 我的数据库格式为 dm(int) name(varchar) pid(int) rwxs(int) rxyj(int) 1 1 0 400 0 2 2 1 400 0...............30000 30000 29999 400 0累计rwxs的值到rxyj里,存储过程用你刚写的那个 CREATE proc p_calc as create index inx_dm on user2(dm) create index inx_pid on user2(pid) update user2 set rxyj=0 update user2 set rxyj=rwxs from user2 a where not exists( select 1 from user2 where pid=a.dm and pid<>0) while @@rowcount>0 update user2 set rxyj=isnull(a.rwxs,0)+isnull(b.rxyj,0) from user2 a join( select pid,rxyj=sum(rxyj) from user2 where rxyj<>0 group by pid )b on a.dm=b.pid where a.rxyj=0 and not exists( select 1 from user2 where pid=a.dm and rxyj=0 and dm<>pid) GO---然后在ASP里调用 <% set db=server.createobject("adodb.connection") db.ConnectionTimeout=0 db.commandtimeout=0 db.open conn db.execute "exec p_calc" db.close %>全部的都是这样了,你看哪里不正确,帮我改一下呀
create index 这两句,应该是在查询分析器中单独执行,执行一次就够了,你还是看看书吧,索引的慨念居然一点都没有 --这样的存储过程,处理正常的数据是没有问题,处理你的测试数据那么特殊的,决定不行 create proc p_calc as update user2 set rxyj=-1 update user2 set rxyj=rwxs from user2 a where not exists( select 1 from user2 where pid=a.dm) while @@rowcount>0 update user2 set rxyj=isnull(a.rwxs,0)+isnull(b.rxyj,0) from user2 a join( select pid,rxyj=sum(rxyj) from user2 where rxyj<>-1 group by pid )b on a.dm=b.pid where a.rxyj=-1 and not exists( select 1 from user2 where pid=a.dm and rxyj=-1)
不过十来行程序,竟然有这么多错误,性能不慢就奇怪了! not exists, <>, 这种写法是最烂的,你建100个索引都没用的。 你还建了一个虚拟表,有那么复杂吗?难道不能直接连接吗?还有一个子查询(not exists那条)! 慢是正常的! 如果你的数据到了300万条,那就算是300万巨型机也跑到动了!
关键是在create index这两句,因为创建一次就可以了,反复创建,不慢才怪!
bbcbs(笨笨虫)你好惨啊,girl friend也跟你byebye了
Try:create proc p_calc as set nocount on ---加这一句update user2 set rxyj=0 update user2 set rxyj=rwxs from user2 a where not exists( select 1 from user2 where pid=a.dm) while @@rowcount>0 update user2 set rxyj=isnull(a.rwxs,0)+isnull(b.rxyj,0) from user2 a join( select pid,rxyj=sum(rxyj) from user2 where rxyj<>-1 group by pid )b on a.dm=b.pid where a.rxyj=-1 and not exists( select 1 from user2 where pid=a.dm and rxyj=-1)
Try:create proc p_calc as set nocount on ---加这一句update user2 set rxyj=-1 update user2 set rxyj=rwxs from user2 a where not exists( select 1 from user2 where pid=a.dm) while @@rowcount>0 update user2 set rxyj=isnull(a.rwxs,0)+isnull(b.rxyj,0) from user2 a join( select pid,rxyj=sum(rxyj) from user2 where rxyj<>-1 group by pid )b on a.dm=b.pid where a.rxyj=-1 and not exists( select 1 from user2 where pid=a.dm and rxyj=-1)
(所影响的行数为30000行)
(所影响的行数为1行)
(所影响的行数为1行)
。
。
(所影响的行数为1行)
后面很多邹建,我的QQ是780799,我强烈要求你加我QQ,哥,哥呀,我真心真意的求你帮我,帮我好吗?我都急的疯了,这几天也不想吃饭,想到这个问题就失眠,女朋友说我这几天不对头在外面有女人因这事和我分手,你不要以为我骗你,我说的都是真的呀,等这个项目过后,我一定一定会好好好好的学习MSSQL的,但现在火烧眉毛了,,帮帮我呀
我的数据库格式为
dm(int) name(varchar) pid(int) rwxs(int) rxyj(int)
1 1 0 400 0
2 2 1 400 0...............30000 30000 29999 400 0累计rwxs的值到rxyj里,存储过程用你刚写的那个
CREATE proc p_calc
as
create index inx_dm on user2(dm)
create index inx_pid on user2(pid)
update user2 set rxyj=0
update user2 set rxyj=rwxs
from user2 a
where not exists(
select 1 from user2 where pid=a.dm and pid<>0)
while @@rowcount>0
update user2 set rxyj=isnull(a.rwxs,0)+isnull(b.rxyj,0)
from user2 a join(
select pid,rxyj=sum(rxyj) from user2
where rxyj<>0 group by pid
)b on a.dm=b.pid
where a.rxyj=0 and not exists(
select 1 from user2 where pid=a.dm and rxyj=0 and dm<>pid)
GO---然后在ASP里调用
<%
set db=server.createobject("adodb.connection")
db.ConnectionTimeout=0
db.commandtimeout=0
db.open conn
db.execute "exec p_calc"
db.close
%>全部的都是这样了,你看哪里不正确,帮我改一下呀
--这样的存储过程,处理正常的数据是没有问题,处理你的测试数据那么特殊的,决定不行
create proc p_calc
as
update user2 set rxyj=-1
update user2 set rxyj=rwxs
from user2 a
where not exists(
select 1 from user2 where pid=a.dm)
while @@rowcount>0
update user2 set rxyj=isnull(a.rwxs,0)+isnull(b.rxyj,0)
from user2 a join(
select pid,rxyj=sum(rxyj) from user2
where rxyj<>-1 group by pid
)b on a.dm=b.pid
where a.rxyj=-1 and not exists(
select 1 from user2 where pid=a.dm and rxyj=-1)
not exists, <>, 这种写法是最烂的,你建100个索引都没用的。
你还建了一个虚拟表,有那么复杂吗?难道不能直接连接吗?还有一个子查询(not exists那条)!
慢是正常的!
如果你的数据到了300万条,那就算是300万巨型机也跑到动了!
as
set nocount on ---加这一句update user2 set rxyj=0
update user2 set rxyj=rwxs
from user2 a
where not exists(
select 1 from user2 where pid=a.dm)
while @@rowcount>0
update user2 set rxyj=isnull(a.rwxs,0)+isnull(b.rxyj,0)
from user2 a join(
select pid,rxyj=sum(rxyj) from user2
where rxyj<>-1 group by pid
)b on a.dm=b.pid
where a.rxyj=-1 and not exists(
select 1 from user2 where pid=a.dm and rxyj=-1)
as
set nocount on ---加这一句update user2 set rxyj=-1
update user2 set rxyj=rwxs
from user2 a
where not exists(
select 1 from user2 where pid=a.dm)
while @@rowcount>0
update user2 set rxyj=isnull(a.rwxs,0)+isnull(b.rxyj,0)
from user2 a join(
select pid,rxyj=sum(rxyj) from user2
where rxyj<>-1 group by pid
)b on a.dm=b.pid
where a.rxyj=-1 and not exists(
select 1 from user2 where pid=a.dm and rxyj=-1)
以上的表,多了一个列renshu(int)
当pid对应dm时,说明pid是dm的下线,这样很多级的对应,想要的效果,经过一次计算,renshu列里自动生出他所有下层下线的人数,而不是第一级下级的人数在ASP里的递归太慢了
请教~~