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.
Chris W wrote:
Query much slower on 2005 compared to 2000
DogObsessedperson
x646d63
what's the database size whats the tlog size
whats the location of your OS datafiles and logfiles
some wild guess
1. have you run a dbcc shrink database or dbcc shrinkfile
your db could be fragmented causing it to react very slow
Bastian W.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
CaptBeagle
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:
Hibernating Bear
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.
Behrooz PB
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
Troy Lundin
I tried running the query plans against both 2000 and 2005. With 2000 it took 40 seconds to display the query plan. With 2005 I gave up after 39 minutes!
So I then decided to show the estimated query plan instead. With 2000 it showed one query as i've always been used to. With 2005 it was showed 27 seperate queries. I think they were all the seperate UDF's in my view. So I therefore decided that fixing the UDF's was the way to go.
I've managed to change all of the UDF's that involved queries to be a subquery instead. This left me with just a few UDF's that deal with string concatination etc. After these changes I now have these results...
Old Query in SQL 2000 - 23 seconds
Old Query in SQL 2005 - 39 seconds
New Query in SQL 2000 - 17 seconds
New Query in SQL 2005 - 18 seconds
So i've got it from a gap of 16 seconds with my old query to just one second. Therefore I can only assume 2005 can't handle UDF's anywhere near as well as 2000 (at least on my machines specification anyway).
Thanks.