May 6, 2005 at 5:55 am
When i execute a view, the profile shows the data page read by sql server varies each time. The view has function with in that, in that function i have used top clause. But the view returns the same number of result always
hint: i didnt change the values for the where clause, and to the function also.
Using top clause will affect the sql server data page read property?
thanks,
Ananda Kumar
May 6, 2005 at 10:13 am
Are you aware that for a SQL statement that includes an UDF, Profilier and Statistics on event SQL:BatchCompleted do NOT include the resoures used by the UDF?
Here is an example using the Northwind database using the below SQL.
First, open 3 Query Analyzer windows and run each of the 3 batchs below. Each batch begins with "-- Connection".
Second, start Profiler and include events/SubEvents "Stored Procedures"/"SQL:StmtCompleted" and "TSQL"/"SQL:BatchCompleted".
Third, run the 2 SQL queries again, stop the trace and save the trace to table UDFTrace in the Northwind database.
Finally, query the trace table to get the real resource usages.
SELECT spid, EventClass
,count(*)as StmtExecCnt
,sum( Duration ) as StmtDurationMS
,sum( Reads )as StmtReadCnt
,sum( Writes )as StmtWriteCnt
,sum( CPU )as StmdCpuMs
FROM northwind.dbo.UDFTrace
where spid is not null
group by spid, EventClass
order by spid, EventClass
Here is the output after pivoting.
spid55UDF TotalUDF TSQLUDF Statement
EventClass12571245
StmtExecCnt11831182
StmtDurationMS2015013020
StmtReadCnt1987408922281861
StmtWriteCnt0000
StmdCpuMs10201010
Notice that the "TSQL"/"SQL:BatchCompleted" does not include any resources used by the UDF so you need to add batch completed and statement completed statistics to get the real usage.
Note that UDFs perform over twice the number of logical reads as compared to the non-UDF SQL.
The reproduction script:
-- Connection 1
use Nortwind
go
create FUNCTION dbo.CustomerOrderTotals
( @CustomerID char(5))
RETURNS money
AS
BEGIN
Declare @CustomerOrderTotalAmtmoney
select @CustomerOrderTotalAmt
=Sum(CONVERT(money,(OrderDetails.UnitPrice*Quantity*(1-Discount)/100))*100)
FROM Orders
join[Order Details] as OrderDetails
on OrderDetails.OrderId= Orders.OrderId
whereOrders.CustomerID = @CustomerID
RETURN @CustomerOrderTotalAmt
END
GO
-- Connection 2
use Nortwind
go
select Customers.CustomerID
,Customers.CompanyName
,dbo.CustomerOrderTotals (customers.CustomerID)
from Customers
go
use Nortwind
go
-- Connection 3
select Customers.CustomerID
,Customers.CompanyName
, CustomerOrderTotals.CustomerOrderTotalAmt
from Customers
join(
select Orders.CustomerID
,Sum(CONVERT(money,(OrderDetails.UnitPrice*Quantity*(1-Discount)/100))*100)
FROM Orders
join[Order Details] as OrderDetails
on OrderDetails.OrderId= Orders.OrderId
group by Orders.CustomerID
) as CustomerOrderTotals( CustomerID, CustomerOrderTotalAmt)
on CustomerOrderTotals.CustomerID = Customers.CustomerID
go
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply