reads差异遇到一个问题,在用sql profiler抓取的语句(在存储过程中)INSERT INTO PaySupplier(IsAdjData,EHSaleID,EHSaleID_CRM,CustomerCode,Product,Memo,SupplierName,PayDate,CustomerID,Type,PPID,CustomerID_CRM,Supplier_CRM,Type_CRM,Employees,Amt,Status,UpdateDate,CRMPayDate)
SELECT 1, @EHSaleID,dbo.f_EHSaleID_CrmID(@EHSaleID),@CustomerCode,'aaa', @PayDate + 'bbb','', @PayDate ,@CustomerID, '21'
,@PPID, dbo.f_CustomerID_CrmID(@CustomerID),'','' ,COUNT(DISTINCT CONVERT(VARCHAR(50), empid)),SUM(InsTotal),'01',getdate(),CONVERT(VARCHAR(10),GETDATE(),120)
FROM
(
SELECT SocialSupplier,EmpID,(ServiceFee + InsRepFee - (InsCost + InsRepCost+GJJCost + GJJRepCost)) AS InsTotal
FROM payroll2
WHERE PPID = @PPID AND CustomerId = @CustomerID AND (InsIncomeFile = @PayDate AND InsPayFile = @PayDate) AND Status = '01' AND InsServiceStatus= '01'
) a HAVING COUNT(*)>0此一句 reads 14566
然后我自己手动执行的时候reads 1900相差巨大,此表上没有触发器,索引有7个。大家能帮忙分析下看看为何差异这么大。
SELECT 1, @EHSaleID,dbo.f_EHSaleID_CrmID(@EHSaleID),@CustomerCode,'aaa', @PayDate + 'bbb','', @PayDate ,@CustomerID, '21'
,@PPID, dbo.f_CustomerID_CrmID(@CustomerID),'','' ,COUNT(DISTINCT CONVERT(VARCHAR(50), empid)),SUM(InsTotal),'01',getdate(),CONVERT(VARCHAR(10),GETDATE(),120)
FROM
(
SELECT SocialSupplier,EmpID,(ServiceFee + InsRepFee - (InsCost + InsRepCost+GJJCost + GJJRepCost)) AS InsTotal
FROM payroll2
WHERE PPID = @PPID AND CustomerId = @CustomerID AND (InsIncomeFile = @PayDate AND InsPayFile = @PayDate) AND Status = '01' AND InsServiceStatus= '01'
) a HAVING COUNT(*)>0此一句 reads 14566
然后我自己手动执行的时候reads 1900相差巨大,此表上没有触发器,索引有7个。大家能帮忙分析下看看为何差异这么大。
解决方案 »
- 求个日期的帖子!!!!
- "安装程序检测到Microsoft .Net Framework 存在问题,无法继续.此系统上未安装Microsoft.Net Framework 2.0,或者该软件已损坏"请问是什么原因啊?
- sqlserver2005,web打开首页总是提示找不到表,刷新几次就行了。。。
- SQL2005中是不是都可用VARCHAR(MAX)和NVARCHAR(MAX)代替TEXT和NTEXT了?
- 上亿条记录,表的设计问题,请教!!!!!
- 各位大哥,帮帮我吧:[OLE/DB provider returned message: 无法启动应用程序。工作组信息文件丢失,或是已被其它用户以独占方式打开。]
- 用SQL语句打印等腰三角形
- 这个存储过程的错在哪里?怎么改呀?
- 什么问题呢?
- 请问怎么在两个数据库中两表中就建立关系。
- 求救,请大家帮帮忙哦
- update后记录不相否?
,Supplier_CRM,Type_CRM,Employees,Amt,Status,UpdateDate,CRMPayDate)
SELECT 1, @EHSaleID,dbo.f_EHSaleID_CrmID(@EHSaleID),@CustomerCode,'aaa', @PayDate + 'bbb','', @PayDate ,@CustomerID, '21'
,@PPID, dbo.f_CustomerID_CrmID(@CustomerID),'','' , COUNT(DISTINCT CONVERT(VARCHAR(50), empid)), SUM(InsTotal),'01',getdate(),dbo.f_GetCrmBillDate('',@NextPaydate,@CustomerID)
FROM
(
SELECT SocialSupplier,EmpID,(ServiceFee + InsRepFee) AS InsTotal
FROM payroll2
WHERE PPID = @PPID AND CustomerId = @CustomerID AND (InsIncomeFile = @Paydate AND InsPayFile = @NextPayDate) AND Status = '01' AND InsServiceStatus= '01'
) a HAVING COUNT(*)>0
INSERT INTO PaySupplier(IsAdjData,EHSaleID,EHSaleID_CRM,CustomerCode,Product,Memo,SupplierName,PayDate,CustomerID,Type,PPID,CustomerID_CRM
,Supplier_CRM,Type_CRM,Employees,Amt,Status,UpdateDate,CRMPayDate)
SELECT 1, @EHSaleID,dbo.f_EHSaleID_CrmID(@EHSaleID),@CustomerCode,'aaa', @PayDate + 'bbb','', @PayDate ,@CustomerID, '21'
,@PPID, dbo.f_CustomerID_CrmID(@CustomerID),'','' , COUNT(DISTINCT CONVERT(VARCHAR(50), empid)),-SUM(InsTotal),'01',getdate(),dbo.f_GetCrmBillDate('',@NextPaydate,@CustomerID)
FROM
(
SELECT SocialSupplier,EmpID,(InsCost + InsRepCost +GJJCost + GJJRepCost) AS InsTotal
FROM payroll2
WHERE PPID = @PPID AND CustomerId = @CustomerID AND (InsIncomeFile = @NextPayDate AND InsPayFile = @PayDate) AND Status = '01' AND InsServiceStatus= '01'
) a HAVING COUNT(*)>0
UNION ALL
SELECT 1,@EHSaleID,dbo.f_EHSaleID_CrmID(@EHSaleID),@CustomerCode,'aaa', @PayDate + 'bbb','',@Paydate,@CustomerID, '21'
,@PPID, dbo.f_CustomerID_CrmID(@CustomerID),'', '',COUNT(DISTINCT CONVERT(VARCHAR(50), empid)), -SUM(chargecost + ChargeCost2),'01',getdate(),dbo.f_GetCrmBillDate('',@NextPaydate,@CustomerID)
FROM etempcharge
WHERE IsAdjData=1 AND PayPPID = @PPID AND status='01' AND chargeKind='aaa' HAVING COUNT(*)>0第一句的read 14000 第二句为27 ,且各变量值相同,且是几乎同一时间执行。
不符合常理。难道sql server profiler中的read不准确?还是说在存储过程中的语句的read统计方式不同?