22,209
社区成员
发帖
与我相关
我的任务
分享百思不得其解的疑难问题--查询速度极慢
ASP+SQL2000
同一套程序分发给两个不同的用户，今天一个用户反应在查询个别功能时速度极慢。我把数据库拷了过了，放到自已的电脑上速度的确是很慢，然后我把另一个用户的数据库也拷回来速度却正常。同样的程序，同样的数据库结构，在同一台电脑里却结果相差很大，究竟是什么原因呢。跪求高手指导。
同一套程序分发给两个不同的用户，今天一个用户反应在查询个别功能时速度极慢。我把数据库拷了过了，放到自已的电脑上速度的确是很慢，然后我把另一个用户的数据库也拷回来速度却正常。同样的程序，同样的数据库结构，在同一台电脑里却结果相差很大，究竟是什么原因呢。跪求高手指导。
...全文
请发表友善的回复…
发表回复
中国风 2018-04-12
- 打赏
- 举报
可参照：
如果表结构相同，通常是以下因素引起的
1.索引碎片，重建索引
2.没有用脏读，有其它用户正在操作表数据，处理方法表名后加上WITH(nolock)
xiaoxiangqing 2018-04-12
- 打赏
- 举报
看下执行计划
cde32 2018-04-12
- 打赏
- 举报
我也这么认为
wang_peng_yl 2018-04-04
- 打赏
- 举报
又或者是不是没有创建索引
wang_peng_yl 2018-04-04
- 打赏
- 举报
我怀疑是你的数据库字段设计问题，
或者说sql语句写法问题，
就是某些字段导致全表扫苗了
ChinaITOldMan 2018-04-04
- 打赏
- 举报
Execution plan
simaqingtian 2018-04-04
- 打赏
- 举报
看着头大，应该是你的SQL语句有问题，按网上常用方法的优化下
吉普赛的歌 2018-03-17
- 打赏
- 举报
你的代码太多了， 没必要弄这么多出来。
你只把你认为慢的SQL贴出来就可以了， 其它的不需要（干扰了我们）。
liqijian 2018-03-17
- 打赏
- 举报
原因基本查明，但不如如何优化程序或数据库的设计：具体情况为：
1.在ASP程序中要查询两个表（cx_jijia_mx ,cx_jiesuan_cx）中的数据进行比较，统计出来JIJIA表比JIESUAN表多如来的记录，这一块做的时候设计了一个查询v_jj_js_biaoduan_mxsum，如下：
SELECT TOP 100 PERCENT a.*, b.biaoduan_no AS biaoduan,
(SELECT isnull(SUM(js_shuliang), 0)
FROM cx_jiesuan_mx
WHERE htmx_id = a.htmx_id AND js_biaoduan = b.biaoduan_no)
AS lj_js_shuliang,
(SELECT isnull(SUM(js_hejia), 0)
FROM cx_jiesuan_mx
WHERE htmx_id = a.htmx_id AND js_biaoduan = b.biaoduan_no) AS lj_js_hejia,
(SELECT isnull(SUM(jj_shuliang), 0)
FROM cx_jijia_mx
WHERE htmx_id = a.htmx_id AND jj_biaoduan = b.biaoduan_no) AS lj_jj_shuliang,
(SELECT isnull(SUM(jj_hejia), 0)
FROM cx_jijia_mx
WHERE htmx_id = a.htmx_id AND jj_biaoduan = b.biaoduan_no)
AS lj_jj_hejia
FROM dbo.cx_htmx a INNER JOIN
dbo.biaoduan b ON a.project = b.project
ORDER BY a.ht_id, a.htmx_id, b.biaoduan_no
2.在ASP文件中有以下语句：
set rs3=conn.execute("select fenlei,count(ht_id) as ht_id,htmx_id,max(htbh) as htbh,max(htdw) as htdw from v_jj_js_biaoduan_mxsum group by htmx_id,fenlei,biaoduan having max(project)='"&project&"' and biaoduan='"&biaoduan_&"' and round((sum(lj_js_hejia)-sum(lj_jj_hejia)),2)<0 ")
js=0
bd_js=0
if rs3.eof and rs3.bof then
is_jj_js_biaoduan=0
else
do while not rs3.eof
js=js+1
if ltrim(trim(rs3("fenlei")))=fenlei_ then
bd_js=bd_js+1
end if
rs3.movenext
loop
is_jj_js_biaoduan=bd_js
end if
rs3.close
set rs3=nothing
3.因为这两个查询表中的数据很相差很大，一个只有几十条，一个有三千条，致使执行第2步程序出现查询时间太长，超时。涉及到的几个表结构如下：
(1)表hetong_mx:
3 id int 4 0
0 ht_id int 4 1
0 wzmc_mx char 50 1
0 xinghao char 150 1
0 wz_dw char 10 1
0 shuliang float 8 1
0 daozhan_danjia float 8 1
0 daozhan_hejia float 8 1
0 beizhu char 100 1
(2)视图cx_jijia_mx:
SELECT jj.id AS jj_id, jj.jj_date, ht.project, ht.id AS ht_id, ht.fenlei, ht.htbh, ht.htdw,
hm.id AS htmx_id, hm.wzmc_mx, hm.xinghao, hm.wz_dw,
hm.shuliang AS ht_shuliang, hm.daozhan_danjia AS ht_danjia,
hm.daozhan_hejia AS ht_hejia, jj.jj_biaoduan, jj.jj_shuliang, jj.jj_danjia, jj.jj_hejia,
jj.jj_zhangjie, jj.didian, jj.didian_sum, jj.is_shenhe
FROM dbo.hetong ht INNER JOIN
dbo.hetong_mx hm ON ht.id = hm.ht_id INNER JOIN
dbo.jijia_mx jj ON ht.id = jj.ht_id AND hm.id = jj.htmx_id
(3)视图cx_jiesuan_mx:
SELECT jm.id AS js_id, jm.js_date, ht.project, ht.id AS ht_id, ht.fenlei, ht.htbh, ht.htdw,
ht.yfk_bl, ht.jsk_bl, ht.is_baohan, hm.id AS htmx_id, hm.wzmc_mx, hm.xinghao,
hm.wz_dw, hm.shuliang AS ht_shuliang, hm.daozhan_danjia AS ht_danjia,
hm.daozhan_hejia AS ht_hejia, jm.js_biaoduan, jm.js_shuliang, jm.js_danjia,
jm.js_hejia, jm.js_danhao, jm.is_jiezhuan, jm.js_hejia * ((1 - ht.zbj_bl)
- ht.yfk_bl * ht.is_baohan) AS yf_fk_jsk, jm.js_beizhu
FROM dbo.hetong ht INNER JOIN
dbo.hetong_mx hm ON ht.id = hm.ht_id INNER JOIN
dbo.jiesuan_mx jm ON ht.id = jm.ht_id AND hm.id = jm.htmx_id
liqijian 2018-03-17
- 打赏
- 举报
找到了一点点，如果删除以下一段程序，速度就不慢了，大家帮我看下：
set rs3=conn.execute("select fenlei,count(ht_id) as ht_id,htmx_id,max(htbh) as htbh,max(htdw) as htdw from v_jj_js_biaoduan_mxsum group by htmx_id,fenlei,biaoduan having max(project)='"&project&"' and biaoduan='"&biaoduan_&"' and round((sum(lj_js_hejia)-sum(lj_jj_hejia)),2)<0 ")
js=0
bd_js=0
if rs3.eof and rs3.bof then
is_jj_js_biaoduan=0
else
do while not rs3.eof
js=js+1
if ltrim(trim(rs3("fenlei")))=fenlei_ then
bd_js=bd_js+1
end if
rs3.movenext
loop
is_jj_js_biaoduan=bd_js
end if
rs3.close
set rs3=nothing
v_jj_js_biaoduan_mxsum查询数据表的语句文件
SELECT TOP 100 PERCENT a.*, b.biaoduan_no AS biaoduan,
(SELECT isnull(SUM(js_shuliang), 0)
FROM cx_jiesuan_mx
WHERE htmx_id = a.htmx_id AND js_biaoduan = b.biaoduan_no)
AS lj_js_shuliang,
(SELECT isnull(SUM(js_hejia), 0)
FROM cx_jiesuan_mx
WHERE htmx_id = a.htmx_id AND js_biaoduan = b.biaoduan_no) AS lj_js_hejia,
(SELECT isnull(SUM(jj_shuliang), 0)
FROM cx_jijia_mx
WHERE htmx_id = a.htmx_id AND jj_biaoduan = b.biaoduan_no) AS lj_jj_shuliang,
(SELECT isnull(SUM(jj_hejia), 0)
FROM cx_jijia_mx
WHERE htmx_id = a.htmx_id AND jj_biaoduan = b.biaoduan_no)
AS lj_jj_hejia
FROM dbo.cx_htmx a INNER JOIN
dbo.biaoduan b ON a.project = b.project
ORDER BY a.ht_id, a.htmx_id, b.biaoduan_no
liqijian 2018-03-17
- 打赏
- 举报
数据量小的反而慢。
没有SQL执行计划。
ASP语句直接调用ASP语句。
表结构：
1 id int 4 0
0 ht_id int 4 1
0 htmx_id int 4 1
0 jj_date datetime 8 1
0 jj_biaoduan int 4 1
0 jj_shuliang float 8 1
0 jj_danjia float 8 1
0 jj_hejia float 8 1
0 jj_zhangjie char 10 1
0 didian char 50 1
0 didian_sum char 100 1
0 is_shenhe int 4 1
查询表的语句：
SELECT jj.id AS jj_id, jj.jj_date, ht.project, ht.id AS ht_id, ht.fenlei, ht.htbh, ht.htdw,
hm.id AS htmx_id, hm.wzmc_mx, hm.xinghao, hm.wz_dw,
hm.shuliang AS ht_shuliang, hm.daozhan_danjia AS ht_danjia,
hm.daozhan_hejia AS ht_hejia, jj.jj_biaoduan, jj.jj_shuliang, jj.jj_danjia, jj.jj_hejia,
jj.jj_zhangjie, jj.didian, jj.didian_sum, jj.is_shenhe
FROM dbo.hetong ht INNER JOIN
dbo.hetong_mx hm ON ht.id = hm.ht_id INNER JOIN
dbo.jijia_mx jj ON ht.id = jj.ht_id AND hm.id = jj.htmx_id
吉普赛的歌 2018-03-16
- 打赏
- 举报
发执行计划呀，大哥
砸死牛顿的苹果 2018-03-16
- 打赏
- 举报
数据量一样吗
liqijian 2018-03-16
- 打赏
- 举报
ASP中的查询语句无变化，我在本机搭建了ASP环境，调试时只是更换了数据库文件，数据库的结构是完全一样的呀。
吉普赛的歌 2018-03-16
- 打赏
- 举报
发下查询语句，以及快、慢两种情况下的执行计划
