The problem is getting worse for me. I've now tried running my fixed query on larger data files and it is now slower than the original query.

If I call a Sub-Query by doing:-

(SELECT VehicleID FROM dbo.VehicleAllocations WHERE StartDate = dbo.JobDates.StartDate AND AllocationID = dbo.Allocations.AllocationID) AS VehicleID

This is much slower than calling a UDF which effectively does the same thing:-

dbo.GetVehicleID(dbo.JobDates.StartDate, dbo.Allocations.AllocationID) AS VehicleID

The UDF looks like this...

CREATE FUNCTION [dbo].[GetVehicleID] (@StartDate datetime. @AllocationID int)
RETURNS varchar(10) AS
BEGIN
DECLARE @VehicleID varchar(10)
SELECT @VehicleID = VehicleID FROM dbo.VehicleAllocations
WHERE StartDate = @StartDate AND AllocationID = @AllocationID
RETURN @VehicleID
END

If I compare 2 queries, one which calls 3 UDF's similar to the above and another which calls 3 sub-queries and do a simple WHERE clause, the UDF query takes 2 seconds and the Sub-query method takes 13 seconds.

I can't figure out why something which on the face of it look as though it is doing something pretty identical is so different, and only on SQL Server 2005.

ChrisW at 2007-9-3 >

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

<Chris href="mailto:W@discussions.microsoft.com">W@discussions.microsoft.com>

wrote in message href="news_3Ad9dc4e20-4716-4f0f-81e0-25a12ea38a3d_WBRev1__40discussions.microsoft.com">news:d9dc4e20-4716-4f0f-81e0-25a12ea38a3d_WBRev1_@discussions..microsoft.com...

This

post has been edited either by the author or a moderator in the Microsoft

Forums: http://forums.microsoft.com

The problem is getting worse for me. I've now tried running my fixed

query on larger data files and it is now slower than the original query.

If I call a Sub-Query by doing:-

(SELECT VehicleID FROM dbo.VehicleAllocations WHERE StartDate =

dbo.JobDates.StartDate AND AllocationID = dbo.Allocations.AllocationID) AS

VehicleID

This is much slower than calling a UDF which effectively does the same

thing:-

dbo.GetVehicleID(dbo.JobDates.StartDate, dbo.Allocations.AllocationID) AS

VehicleID

The UDF looks like this...

CREATE FUNCTION [dbo].[GetVehicleID] (@StartDate datetime. @AllocationID

int)
RETURNS varchar(10) AS
BEGIN
DECLARE @VehicleID

varchar(10)
SELECT @VehicleID = VehicleID FROM dbo.VehicleAllocations

WHERE StartDate = @StartDate AND AllocationID = @AllocationID
RETURN

@VehicleID
END

If I compare 2 queries, one which calls 3 UDF's similar to the above

and another which calls 3 sub-queries and do a simple WHERE clause, the UDF

query takes 2 seconds and the Sub-query method takes 13 seconds.

I can't figure out why something which on the face of it look as though it

is doing something pretty identical is so different, and only on SQL Server

2005.

MVPUser at 2007-9-3 >

Hi Chris,

As you mentioned, Scalar Functions are poor performers in a SELECT statement so we should concentrate on seeing why the view with subqueries is doing poorly.

Can you post the following information:

• SQL for the view that uses the subqueries
• Table schemas with sample data
• Results from actual query with SET STATISTICS IO ON

JaredKo_Seattle at 2007-9-3 >

I will work on doing the table schema and sample data. Firstly here are the results on the SET STATISTICS IO ON...

Table 'Drivers'. Scan count 0, logical reads 1800, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContractDriverAllocations'. Scan count 3, logical reads 12441, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DriverTypes'. Scan count 1, logical reads 722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Vehicles'. Scan count 0, logical reads 2958, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContractVehicleAllocations'. Scan count 1, logical reads 6023, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VehicleTypes'. Scan count 1, logical reads 714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContractDates'. Scan count 1, logical reads 3411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContractMovements'. Scan count 1, logical reads 306, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Clients'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contracts'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContractDrivers'. Scan count 1, logical reads 95, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContractVehicles'. Scan count 1, logical reads 61, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

ChrisW at 2007-9-3 >