求救：查询一直在等待

esen 发表于 2011-05-18 11:01

esen 发表于 2011-05-18 11:02

esen 发表于 2011-05-18 11:03

andkylee 发表于 2011-05-18 12:41

用sp_sysmon监控，看看系统的状态。

chuxu 发表于 2011-05-18 13:32

你的查询语句也太复杂了吧，而且从这个查询计划中已经看到了几处table scan，如果表比较大，慢也是正常的了。

esen 发表于 2011-05-18 13:45

在客户端，我执行同样的sql，却只需要40-50秒就可以出来数据，升级到15才这样的，sql如下：
SELECT viewbolt.in_no,
viewbolt.item_code,
viewbolt.bolt_pcno,
viewbolt.color_no,
viewbolt.lot_no,
viewbolt.bolt_no,
viewbolt.area_no,
viewbolt.scalar_qty,
viewbolt.curr_qty,
viewbolt.in_date,
viewbolt.loc_number,
viewbolt.categ_type,
viewbolt.po_number,
viewbolt.sale_no,
viewbolt.cust_no,
viewbolt.trade_no,
viewbolt.rpt_no,
viewbolt.re_value_price,
viewbolt.unit_price,
item_master.wei_ave_price,
viewbolt.ys_name ,
item_master.item_uom,
customer.sal_name,
customer.cust_name,
item_master.item_desc,
item_master.item_sub_categ_code,
item_categ.item_sub_categ_desc,
customer.trademark_name,
purchase_order.order_date,
purchase_order.remarks,
viewbolt.first_in_date,
datepart(day,viewbolt.first_in_date) as first_in_day,
datepart(month,viewbolt.first_in_date) as first_in_month,
datepart(year,viewbolt.first_in_date) as first_in_year,
ceiling(convert(dec(6,2),datediff(day,viewbolt.first_in_date,getdate()))/30) as aging_categ,
stock_list.status,
stock_list.remark,
      buyer.buyer_name_ch,purchase_order.buyer
FROM viewbolt,customer,item_master,purchase_order,item_categ,stock_list,buyer
WHERE viewbolt.sale_no = customer.sal_noand
viewbolt.cust_no = customer.cust_no and
viewbolt.trade_no= customer.trade_no and
viewbolt.item_code = item_master.item_code and
item_master.item_sub_categ_code = item_categ.item_sub_categ_code and
viewbolt.po_number *= purchase_order.po_number and
viewbolt.bolt_pcno = stock_list.bolt_pcno and
purchase_order.buyer *= buyer.buyer_name and
(( viewbolt.in_no >= '%' ) AND
( viewbolt.in_no <= '%' ) or
(viewbolt.in_no like '%' and '%'='%' )) and
( viewbolt.item_code like '%' ) AND
( viewbolt.loc_number like '%' ) AND
( viewbolt.categ_type like '%' ) and
viewbolt.out_no='' and viewbolt.dispart <>'T' and
viewbolt.sale_no like '%' and
viewbolt.cust_no like '%' and
viewbolt.trade_no like '%'

UNION

SELECT viewbolt.in_no,
viewbolt.item_code,
viewbolt.bolt_pcno,
viewbolt.color_no,
viewbolt.lot_no,
viewbolt.bolt_no,
viewbolt.area_no,
viewbolt.scalar_qty,
viewbolt.curr_qty,
viewbolt.in_date,
viewbolt.loc_number,
viewbolt.categ_type,
viewbolt.po_number,
viewbolt.sale_no,
viewbolt.cust_no,
viewbolt.trade_no,
viewbolt.rpt_no,
viewbolt.re_value_price,
viewbolt.unit_price,
item_master.wei_ave_price,
viewbolt.ys_name ,
item_master.item_uom,
customer.sal_name,
customer.cust_name,
item_master.item_desc,
item_master.item_sub_categ_code,
item_categ.item_sub_categ_desc,
customer.trademark_name,
purchase_order.order_date,
purchase_order.remarks,
viewbolt.first_in_date,
datepart(day,viewbolt.first_in_date) as first_in_day,
datepart(month,viewbolt.first_in_date) as first_in_month,
datepart(year,viewbolt.first_in_date) as first_in_year,
ceiling(convert(dec(6,2),datediff(day,viewbolt.first_in_date,getdate()))/30) as aging_categ,
'0',
'',
      buyer.buyer_name_ch,purchase_order.buyer

FROM viewbolt,customer,item_master,purchase_order,item_categ,buyer
WHERE viewbolt.sale_no = customer.sal_noand
viewbolt.cust_no = customer.cust_no and
viewbolt.trade_no= customer.trade_no and
viewbolt.item_code = item_master.item_code and
( item_master.item_sub_categ_code = item_categ.item_sub_categ_code ) and
viewbolt.po_number *= purchase_order.po_number and
      purchase_order.buyer *= buyer.buyer_name and
(( viewbolt.in_no >= '%' ) AND
( viewbolt.in_no <= '%' ) or
(viewbolt.in_no like '%' and '%'='%' )) and
( viewbolt.item_code like '%' ) AND
( viewbolt.loc_number like '%' ) AND
( viewbolt.categ_type like '%' ) and
viewbolt.out_no='' and viewbolt.dispart <>'T' and
viewbolt.sale_no like '%' and
viewbolt.cust_no like '%' and
viewbolt.trade_no like '%' and
      not exists (select 1 from stock_list
                        where stock_list.bolt_pcno = viewbolt.bolt_pcno
                           )

UNION

SELECT a.in_no,
a.item_code,
a.bolt_pcno,
a.color_no,
a.lot_no,
a.bolt_no,
a.area_no,
a.scalar_qty,
a.curr_qty,
a.in_date,
a.loc_number,
a.categ_type,
a.po_number,
a.sale_no,
a.cust_no,
a.trade_no,
a.rpt_no,
a.re_value_price,
a.unit_price,
c.wei_ave_price,
a.ys_name ,
c.item_uom,
b.sal_name,
b.cust_name,
c.item_desc,
c.item_sub_categ_code,
e.item_sub_categ_desc,
b.trademark_name,
d.order_date,
d.remarks,
a.first_in_date,
datepart(day,a.first_in_date) as first_in_day,
datepart(month,a.first_in_date) as first_in_month,
datepart(year,a.first_in_date) as first_in_year,
ceiling(convert(dec(6,2),datediff(day,a.first_in_date,getdate()))/30) as aging_categ,
f.status,
f.remark,
g.buyer_name_ch,d.buyer
FROM wiseform..viewbolt a,
wiseform..customer b,
wiseform..item_master c,
wiseform..purchase_order d,
wiseform..item_categ e,
wiseform..stock_list f,wiseform..buyer g
WHERE a.sale_no = b.sal_noand
a.cust_no = b.cust_no and
a.trade_no= b.trade_no and
a.item_code = c.item_code and
c.item_sub_categ_code = e.item_sub_categ_code and

a.bolt_pcno = f.bolt_pcno and
d.buyer *= g.buyer_name and
a.po_number *= d.po_number and
(( a.in_no >= '%' ) AND
( a.in_no <= '%' ) or
( a.in_no like '%' and '%' ='%' )) and
( a.item_code like '%' ) AND
( a.loc_number like '%' ) AND
( a.categ_type like '%' ) and
a.out_no='' and
a.dispart <>'T' and
a.sale_no like '%'and
a.cust_no like '%' and
a.trade_no like '%'

UNION

SELECT a.in_no,
a.item_code,
a.bolt_pcno,
a.color_no,
a.lot_no,
a.bolt_no,
a.area_no,
a.scalar_qty,
a.curr_qty,
a.in_date,
a.loc_number,
a.categ_type,
a.po_number,
a.sale_no,
a.cust_no,
a.trade_no,
a.rpt_no,
a.re_value_price,
a.unit_price,
c.wei_ave_price,
a.ys_name ,
c.item_uom,
b.sal_name,
b.cust_name,
c.item_desc,
c.item_sub_categ_code,
e.item_sub_categ_desc,
b.trademark_name,
d.order_date,
d.remarks,
a.first_in_date,
datepart(day,a.first_in_date) as first_in_day,
datepart(month,a.first_in_date) as first_in_month,
datepart(year,a.first_in_date) as first_in_year,
ceiling(convert(dec(6,2),datediff(day,a.first_in_date,getdate()))/30) as aging_categ,
'0',
'',
g.buyer_name_ch,d.buyer
FROM wiseform..viewbolt a,
wiseform..customer b,
wiseform..item_master c,
wiseform..purchase_order d,
wiseform..item_categ e,
wiseform..buyer g
WHERE a.sale_no = b.sal_noand
a.cust_no = b.cust_no and
a.trade_no= b.trade_no and
a.item_code = c.item_code and
c.item_sub_categ_code = e.item_sub_categ_code and
a.po_number *= d.po_number and
   d.buyer *= g.buyer_name and
(( a.in_no >= '%' ) AND
( a.in_no <= '%' ) or
( a.in_no like '%' and '%' ='%' )) and
( a.item_code like '%' ) AND
( a.loc_number like '%' ) AND
( a.categ_type like '%' ) and
a.out_no='' and
a.dispart <>'T' and
a.sale_no like '%'and
a.cust_no like '%' and
a.trade_no like '%' and
   not exists (select 1 from wiseform..stock_list f
                     where f.bolt_pcno = a.bolt_pcno
                        )
大侠帮忙看看

页: [1]

Chinaunix's Archiver

求救：查询一直在等待