My function with a cursor takes a minute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

My function with a cursor takes a minute

Author

Topic

LulamileGaji
Starting Member

2 Posts

Posted - 2012-06-13 : 09:11:02

Good day,

I have a function with a cursor inside this function. This function without the cursor executes for 0.0 seconds but with it execute for 56 sec. My table which I am trying to manipulate has over a million of records with no primary. But there are other keys from other table. My function is as follows:-

ALTER FUNCTION [dbo].[udf_ConcRouteWayPoints]
(
-- Add the parameters for the function here
@VehicleID INT,
@Date DATETIME,
@TimeFrom TIME(0),
@TimeTo TIME(0)
)
RETURNS VARCHAR(8000)
AS
BEGIN
-- Declare the return variable here
DECLARE @RouteWayPoint VARCHAR(8000)

SELECT @RouteWayPoint = COALESCE(@RouteWayPoint + ' - ','') + vRouteWayPointDescription
FROM MatchGPStoRouteWayPoint
WHERE iVehicleID = @VehicleID
AND CONVERT(DATE,dtTime) = @Date
AND bDepartFromWayPoint = 1
AND bArriveAtWayPoint IS NULL
AND CONVERT(VARCHAR(8), dtTime, 108) >= @TimeFrom
AND CONVERT(VARCHAR(8), dtTime, 108) <= @TimeTo
GROUP BY vRouteWayPointDescription
-- Return the result of the function
RETURN @RouteWayPoint

END

gajiL

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 09:36:43

Try converting the input variables to something that can be compared - then it can use an index on the dates

dtTime >= @Date and dtTime < @Date +1

similarly for the other one - but that might not be necessary if that reduces the data accessed by enough.
Probably won't make a difference but you could use a distinct rather than a group by and a derived table.
Check to see if it is using an index after this. It should if yoou index dtTime and include the other columns accessed.
If you want to reduce the index size then just index dtTime and get the PKs first then access the rows.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-13 : 09:42:35

just cross apply the function rather than running it inside a cursor.

Please post the cursor. I suspect that is the actual problem here.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-13 : 10:18:54

quote:
Originally posted by Transact Charlie
Please post the cursor. I suspect that is the actual problem here.

You're good.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-13 : 10:34:51

quote:
Originally posted by robvolk

quote:
Originally posted by Transact Charlie
Please post the cursor. I suspect that is the actual problem here.

You're good.

I can't quite tell if this is a gentle mockery or not......

if not then 'thank you very much'

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-13 : 10:38:01

Nope, no mockery intended.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 11:20:55

No one here will mock the suggestion that a cursor might be a problem (not that it always is).
Think the OP doesn't have an explicit cursor but is refering to the row by row processing in the update statement.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

LulamileGaji
Starting Member

2 Posts

Posted - 2012-06-14 : 03:06:56

quote:
Originally posted by Transact Charlie

just cross apply the function rather than running it inside a cursor.

Please post the cursor. I suspect that is the actual problem here.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Sorry about that there is no cursor in my function and the function is just taking more time when executing it like I posted.

gajiL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-14 : 07:02:18

Rewrite the function as an Inline Table Valued Function instead.
You will notice the performance improvement! However, you cannot longer use the function as a scalar value, so you will have to CROSS APPLY or OUTER APPLY the function name and use the result (one row only anyway) in your original query.

ALTER FUNCTION	dbo.udf_ConcRouteWayPoints
(
	@VehicleID INT,
	@Date DATETIME,
	@TimeFrom TIME(0),
	@TimeTo TIME(0)
)
RETURNS TABLE
AS
RETURN	(
		SELECT	STUFF(Data, 1, 3, '') AS WayPoints
		FROM	(
				SELECT		' - ' + vRouteWayPointDescription
				FROM		dbo.MatchGPStoRouteWayPoint AS wp
				WHERE		iVehicleID = @VehicleID
						AND bDepartFromWayPoint = 1
						AND bArriveAtWayPoint IS NULL
						AND dtTime BETWEEN @Date + @TimeFrom AND @Date + @TimeTo
				GROUP BY	vRouteWayPointDescription
				FOR XML		PATH('')
			) AS d(Data)
	)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources