说出来谁能信呢，散分好啦

lhblxm 2011-04-26 10:30:30

SqlServer 真是太“神奇”了。

1、数据表设置(四个表，进销存的那种）
A、Inventory 表（InventoryID,Code,Name ...)--物料表 2w多行
B、Records 表（RecordID,RecordCode,CategoryID,RecordDate...)--出入记录表 2K多行
C、RecordDetail 表（DetailID,RecrodID,InventoryID,Quntity...)--出入记录明细 1w多行
D、IOCategory 表（CategoryID,Code,ParentID,Isout,...)--出入类别2个层次，10多行
所有表都以自增列为主键，并在此列建立聚集索引

2、这个查询不要半秒（只有200多行出入记录，执行速度让人可以理解）
select a.* from Record a
join RecordDetail b on a.recordID=b.RecordID
join IOCategory c on a.CategoryID=c.CategoryID
where b.InventoryID=191828
order by a.RecordDate

3、下面的查询要5秒（心想一定有没搞明白的地方，一会去CSDN发个贴问下应该会搞定）
select a.*，c.Isout from Record a
join RecordDetail b on a.recordID=b.RecordID
join IOCategory c on a.CategoryID=c.CategoryID
where b.InventoryID=191828
order by a.RecordDate 或

select a.* from Record a
join RecordDetail b on a.recordID=b.RecordID
join IOCategory c on a.CategoryID=c.CategoryID
where b.InventoryID=191828
order by c.IsOut

4、接下的事情让人糊涂了
A、以上代码是从一个存储过程(简称为P,代码见后面，)中分离出来的一部分（为了便于找到原因，已简化，效率一样）
B、数据库在A服务器上和B服务器上各有一个，A为原始数据库，B为用备份还原的方式建立的，B的配置比A好
C、前台程序连接A,执行P，不要半秒，连接B，执行P，7秒
D、在查询分析器中，都为7秒

5、修改A服务器上的存储过程，再改回去，在哪里运行都为7秒
A、该过程中有一变量--@Inventory,用数字（191828一个物料ID）代替后保存修改，在哪里运行都为7秒
B、再把该数字（191828）改回去，在前台执行，为7秒，再也没有以前的速度了
6、代码



ALTER proc [Material].[GetIORecordAndStrock] 

@InventoryID AS INT



as





select

 a.RecordCode 

,cast(cast(a.RecordDate as DATE) as varchar)RecordDate

,c.Name 

,case c.IsOut when 1 then b.Quantity else 0 end OutQty

,case c.Isout When 1 then 0 else b.Quantity end InQty

,case c.IsOut when 1 then b.Quantity *-1 else b.Quantity end cQty

,cast(DATEPART(m,a.recordDate)as varchar(2)) dMonth

,ROW_NUMBER ()over(order by a.recorddate,a.recordid) sn

into #t

 from 

	Material.Records a join Material.RecordDetail  b on a.RecordID=b.RecordID 

	join Material.IOCategory c on a.CategoryID =c.CategoryID 

	where b.InventoryID =@InventoryID   

	

	union all

	

	select ''单据号,

			   ''单据日期,

			   '期初结存' 摘要,

			   0 出库数量 ,

			   0 入库数量,

			   OpeningStock 结存数量,

			   0 月份 ,

			   0

			From Material.Inventory where InventoryID=@InventoryID 	; 

	--上面的代码效率有问题

	

	

	--select * from #t

create table #t1

	(

	 id [int] identity(1,1) not null

	 ,单据号 varchar(50) null

	 ,单据日期 varchar(50) null

	 ,摘要 varchar(50) null

	 ,出库数量 decimal(18,4) null

	 ,入库数量 decimal(18,4) null

	 ,结存数量 decimal(18,4) null

	 ,月份 int	

	 CONSTRAINT [PK_AlignStyle] PRIMARY KEY CLUSTERED 

(

	[id] ASC

)WITH (PAD_INDEX  = OFF, 

 STATISTICS_NORECOMPUTE  = OFF,

 IGNORE_DUP_KEY = OFF, 

 ALLOW_ROW_LOCKS  = ON, 

 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

	)

	

	

	

	insert #t1(

	  单据号

	 ,单据日期

	 ,摘要

	 ,出库数量 

	 ,入库数量

	 ,结存数量

	 ,月份)

select 

	 ''单据号

	 ,''单据日期

	 ,'期初结存' 摘要

	 ,0 出库数量 

	 ,0 入库数量

	 ,cQty 结存数量

	 ,cast( 0 as varchar(2)) 月份

From #t where sn=0



declare @month as int=1

while @month <13

begin 

insert #t1(

	  单据号

	 ,单据日期

	 ,摘要

	 ,出库数量 

	 ,入库数量

	 ,结存数量

	 ,月份)

select 

	 RecordCode

	 ,RecordDate

	 ,Name 

	 ,Nullif(OutQty,0)

	 ,nullif(InQty ,0)

	 ,(select sum(cQty) from #t where sn<=a.sn) 

	 ,dMonth   

from #t a where dMonth =@month 



union all	

	select 

	''

	,''

	,'本月合计'

	,SUM(OutQty)

	,SUM(InQty)

	,(select SUM(cQTY) from #t where dMonth <=@month )

	,@month  

	from #t 

	where dMonth =@month   and exists(select 1 from #t where dMonth =@month  ) 

	

	union all

	select 

	''

	,''

	,'本年累计'

	,SUM(OutQty)

	,SUM(InQty)

	,SUM(cQTY)

	,@month   from #t 

	where dMonth <=@month  and exists(select 1 from #t where dMonth =@month )



set @month +=1



end





select * from #t1 where coalesce(出库数量,入库数量) is not null order by id