尋高手 存儲 和水晶報表的問題 速度優化問題

Question

我寫了一個存儲過程在查詢分析器中執行用了5S 為什麽 用水晶報表調用用了3分鐘謝謝（提供的參數都是一致的 ） 誰能讓水晶報表跑快點？

Answer 1

alter PROC SA_EatSum22                            
@FromDate DATETIME = '03/26/2007',                            
@ToDate DATETIME = '04/30/2007',                            
@UnitID VARCHAR(10) = '',                            
@EmpID VARCHAR(10) = 'A02483'                            
AS                            
     
  
  
select * into #empbas               
 from EmpBas where (empstatus=0 or (empstatus=1 and @FromDate<indate))            
and(empid=@EmpID or @EmpID  = '')    --加快速度                
            
                           
SELECT t3.OrgID, t3.OrgName, t2.EmpID, t2.EmpName,                             
Amount = SUM(Amount), Plus = SUM(0.0), Total = SUM(Plus - Amount),                            
FromDate = CONVERT(VARCHAR(10), @FromDate, 101),                            
ToDate = CONVERT(VARCHAR(10), @ToDate, 101)                         
 into #PSN_EBrushList2                           
FROM PSN_EBrushList2 t1(NOLOCK), #EmpBas t2(NOLOCK), ClassOrg t3(NOLOCK)                            
WHERE t1.EmpID = t2.EmpID                            
AND t2.UnitID = t3.OrgID                            
--AND t1.IsFree < 1                            
AND t1.WorkDate BETWEEN @FromDate AND @ToDate                            
AND (t2.UnitID = @UnitID OR @UnitID = '')                                          
GROUP BY t3.OrgID, t3.OrgName, t2.EmpID, t2.EmpName                            
ORDER BY t3.OrgID, t2.EmpID          
        
        
SELECT t3.OrgID, t3.OrgName, t2.EmpID, t2.EmpName,                             
Amount = SUM(Amount), Plus = SUM(0.0), Total = SUM(Plus - Amount),                            
FromDate = CONVERT(VARCHAR(10), @FromDate, 101),                            
ToDate = CONVERT(VARCHAR(10), @ToDate, 101)                         
 into #PSN_EBrushList22                           
FROM PSN_EBrushList2 t1(NOLOCK), #EmpBas t2(NOLOCK), ClassOrg t3(NOLOCK)                            
WHERE t1.EmpID = t2.EmpID                            
AND t2.UnitID = t3.OrgID                            
AND t1.IsFree = 1                            
AND t1.WorkDate BETWEEN @FromDate AND @ToDate                            
AND (t2.UnitID = @UnitID OR @UnitID = '')                                          
GROUP BY t3.OrgID, t3.OrgName, t2.EmpID, t2.EmpName                            
ORDER BY t3.OrgID, t2.EmpID        
        
update t1        
set Amount=t1.Amount-t2.Amount,Total=t1.Total-t2.Total        
from  #PSN_EBrushList2 t1, #PSN_EBrushList22 t2          
where t1.empid=t2.empid                       
                                        
                        
select  t1.empid, eat=sum(eat)                        
into #PSN_EBrushList21                            
from  psn_eatbu t1, #EmpBas t2(NOLOCK)                           
where  extDate BETWEEN @FromDate AND @ToDate                                      
AND (t2.UnitID = @UnitID OR @UnitID = '')                           
 and t1.EmpID = t2.EmpID                           
GROUP BY t1.EmpID, t2.EmpName                           
                      
update t2                        
set Plus= eat                        
  /* Total = case when(eat- Amount)>0 then 0 else eat- Amount end   */                     
from #PSN_EBrushList21 t1,#PSN_EBrushList2 t2                        
where  t1.empid=t2.empid           
    delete from #PSN_EBrushList2 where Amount=0 and Total=0 and plus=0    
     
select*from   #PSN_EBrushList2   
 drop table #PSN_EBrushList2 
 drop table #PSN_EBrushList21      
 drop table   #PSN_EBrushList22
    
  

Answer 2

所涉及的 表結構為 
PSN_EBrushList2
的
WorkDate datetime no 8             no (n/a) (n/a) NULL
EmpID varchar no 10             no no no Chinese_Taiwan_Stroke_CI_AS
DName varchar no 20             yes no no Chinese_Taiwan_Stroke_CI_AS
ArriveTime datetime no 8             yes (n/a) (n/a) NULL
Amount money no 8 19    4     yes (n/a) (n/a) NULL
WorkTimeName varchar no 20             yes no no Chinese_Taiwan_Stroke_CI_AS
IsFree int no 4 10    0     yes (n/a) (n/a) NULL
Content varchar no 50             yes no no Chinese_Taiwan_Stroke_CI_AS
FromMin datetime no 8             yes (n/a) (n/a) NULL
ToMin datetime no 8             yes (n/a) (n/a) NULL
ExtHr money no 8 19    4     yes (n/a) (n/a) NULL
Plus money no 8 19    4     yes (n/a) (n/a) NULL
IsHoliday int no 4 10    0     yes (n/a) (n/a) NULL
EMPBAS的 
EmpId char no 10             no no no Chinese_Taiwan_Stroke_CI_AS
UnitId char no 5             yes no yes Chinese_Taiwan_Stroke_CI_AS
BankNo varchar no 3             yes no no Chinese_Taiwan_Stroke_CI_AS
EmpName char no 20             yes no yes Chinese_Taiwan_Stroke_CI_AS
EmpBirth datetime no 8             yes (n/a) (n/a) NULL
Sex char no 1             yes no yes Chinese_Taiwan_Stroke_CI_AS
FosterNo int no 4 10    0     yes (n/a) (n/a) NULL
InDate datetime no 8             yes (n/a) (n/a) NULL
OutDate datetime no 8             yes (n/a) (n/a) NULL
DepositerId char no 10             yes no yes Chinese_Taiwan_Stroke_CI_AS
AccountName char no 20             yes no yes Chinese_Taiwan_Stroke_CI_AS
IsWorkDir char no 1             yes no yes Chinese_Taiwan_Stroke_CI_AS
Son varchar no 2             yes no no Chinese_Taiwan_Stroke_CI_AS
Daugther varchar no 2             yes no no Chinese_Taiwan_Stroke_CI_AS
EmpStatus tinyint no 1 3     0     yes (n/a) (n/a) NULLClassOrg的OrgId chrOrgID no 5             no no no Chinese_Taiwan_Stroke_CI_AS
BeginDate smalldatetime no 4             no (n/a) (n/a) NULL
LevelNo tinyint no 1 3     0     no (n/a) (n/a) NULL
OrgName char no 12             no no no Chinese_Taiwan_Stroke_CI_AS
SuperOrgId chrOrgID no 5             yes no no Chinese_Taiwan_Stroke_CI_AS
GroupId varchar no 10             yes no no Chinese_Taiwan_Stroke_CI_AS
maxmans int no 4 10    0     yes (n/a) (n/a) NULL
maxindirectmans int no 4 10    0     yes (n/a) (n/a) NULL
totalmans int no 4 10    0     yes (n/a) (n/a) NULL
Special int no 4 10    0     yes (n/a) (n/a) NULL
D int no 4 10    0     yes (n/a) (n/a) NULL
W int no 4 10    0     yes (n/a) (n/a) NULL
I12 int no 4 10    0     yes (n/a) (n/a) NULL
I3 int no 4 10    0     yes (n/a) (n/a) NULL
ManageHr money no 8 19    4     yes (n/a) (n/a) NULLpsn_eatbu
的extnum char no 12             no no no Chinese_Taiwan_Stroke_CI_AS
empid char no 10             no no no Chinese_Taiwan_Stroke_CI_AS
extdate datetime no 8             no (n/a) (n/a) NULL
realhr float no 8 53    NULL yes (n/a) (n/a) NULL
EAT money no 8 19    4     yes (n/a) (n/a) NULL

Answer 3

我知道的  我的 应用程序传入的数据类型 不一样  要转换的原因!  如果有 几十万条要转换几十万次!  所以要在  传入时候   先用SQL 转换  再进行对比 查询  这样几十万 次变为一次了