Convert a Funtion using a cursor to a set based approach or CTE for better performance
I have a view that calls a function which takes a long time to complete.
Can anyone suggest how can I convert this cursor to a set based approach or CTE for better performance?
the view without the call to the function runs in seconds, adding the call to the function kills the performance.
Data in the dbo.OPERATION table looks like this:
if i call the function with dbo.OPERATION('W','CC18246
Any help would be greatly appreciated.
Can anyone suggest how can I convert this cursor to a set based approach or CTE for better performance?
the view without the call to the function runs in seconds, adding the call to the function kills the performance.
ALTER FUNCTION [dbo].[CT_OpsBetween] (@TYPE nchar(1), @BASE nvarchar(30), @LOT nvarchar(3), @SPLIT nvarchar(3), @SUB nvarchar(3), @eSeq smallint)
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @OpsBetween nvarchar(255);
DECLARE @MyCursor CURSOR;
DECLARE @SEQUENCE_NO smallint;
DECLARE @RESOURCE_ID nvarchar(15);
DECLARE @OPERATION_TYPE nvarchar(15);
DECLARE @SERVICE_ID nvarchar(15);
SET @OpsBetween = ''
SET @MyCursor = CURSOR FOR
SELECT SEQUENCE_NO, RESOURCE_ID, OPERATION_TYPE, SERVICE_ID
from dbo.OPERATION
where TYPE = @TYPE AND BASE_ID = @BASE AND LOT_ID = @LOT and SPLIT_ID = @SPLIT AND SUB_ID = @SUB AND SEQUENCE_NO < @eSeq
ORDER BY SEQUENCE_NO
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @SEQUENCE_NO, @RESOURCE_ID, @OPERATION_TYPE, @SERVICE_ID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @OpsBetween <> ''
BEGIN
set @OpsBetween = @OpsBetween + ', '
END
SET @OpsBetween = @OpsBetween + Cast(@SEQUENCE_NO as varchar(10)) + '-' + CASE WHEN @OPERATION_TYPE IS NOT NULL THEN @OPERATION_TYPE ELSE CASE WHEN @SERVICE_ID IS NOT NULL THEN @SERVICE_ID ELSE @RESOURCE_ID END END;
FETCH NEXT FROM @MyCursor INTO @SEQUENCE_NO, @RESOURCE_ID, @OPERATION_TYPE, @SERVICE_ID
END;
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
RETURN @OpsBetween
ENDData in the dbo.OPERATION table looks like this:
TYPE BASE_ID LOT_ID SPLIT_ID SUB_ID SEQUENCE_NO RESOURCE_ID SERVICE_ID OPERATION_TYPE
W CC182460 1 0 0 5 QC NULL TRV APPR
W CC182460 1 0 0 10 MISC NULL PROCURE
W CC182460 1 0 0 20 QC NULL QC_MTR
W CC182460 1 0 0 30 MISC NULL MARK ASSY API
W CC182460 1 0 0 40 QC NULL HARDNESS 029
W CC182460 1 0 0 50 QC NULL UTPA 040
W CC182460 1 0 0 60 ASSY NULL NULL
W CC182460 1 0 0 70 TEST NULL TEST API
W CC182460 1 0 0 80 PAINT NULL PAINT 028
W CC182460 1 0 0 90 TAG NULL TAG
W CC182460 1 0 0 100 QC NULL QC FINAL 10.0
W CC182460 1 0 0 110 MISC NULL PACK/SHIP 027
W CC182460 1 0 0 120 FINAL NULL NULL
W CC182461 1 0 0 10 MISC NULL PROCURE
W CC182461 1 0 0 20 ASSY NULL NULL
W CC182461 1 0 0 30 LATHE NULL NULL
W CC182461 1 0 0 40 MISC NULL MARK BALL
W CC182461 1 0 0 50 XYLAN NULL XYLAN IN-HOUSE
W CC182461 1 0 0 60 ASSY NULL ASSY BALL (F)
W CC182461 1 0 0 70 TEST NULL TEST API
W CC182461 1 0 0 80 TAG NULL TAG
W CC182461 1 0 0 90 QC NULL QC FINAL 10.0
W CC182461 1 0 0 100 MISC NULL PACK/SHIP 027
W CC182461 1 0 0 110 FINAL NULL NULL
W CC182471 5 0 0 10 MISC NULL PROCURE
W CC182471 5 0 0 20 MISC NULL NULL
W CC182471 5 0 0 30 QC NULL HARDNESS 029
W CC182471 5 0 0 40 QC NULL QC FINAL 10.0
W CC182471 5 0 0 50 MISC NULL PACK/SHIP 027
W CC182471 5 0 0 60 FINAL NULL NULLif i call the function with dbo.OPERATION('W','CC18246
Any help would be greatly appreciated.
ASKER
Thanks, but i get
"Argument data type xml is invalid for argument 1 of stuff function."
"Argument data type xml is invalid for argument 1 of stuff function."
ASKER
fixed it with
FOR XML PATH('')), 1, 1, '') AS nvarchar(255)) AS return_value
but it runs in the exact same amount of time (no performance increase)
FOR XML PATH('')), 1, 1, '') AS nvarchar(255)) AS return_value
but it runs in the exact same amount of time (no performance increase)
Hmm, how large is the dbo.OPERATION table and on what column(s) is it clustered?
We could also try an iTVF. I'll work on that too.
We could also try an iTVF. I'll work on that too.
ASKER
966007 records in the table
Clustered index:
TYPE
BASE_ID
LOT_ID
SPLIT_ID
SUB_ID
SEQUENCE_NO
Clustered index:
TYPE
BASE_ID
LOT_ID
SPLIT_ID
SUB_ID
SEQUENCE_NO
Very similar, just using iTVF style. This will have to be called differently, using CROSS APPLY rather than just a scalar function in the SELECT.
--
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION [dbo].[CT_OpsBetween2] --<--used a different function name so you can test w/o losing the original function
(
@TYPE nchar(1), @BASE nvarchar(30), @LOT nvarchar(3),
@SPLIT nvarchar(3), @SUB nvarchar(3), @eSeq smallint )
RETURNS TABLE
AS
RETURN (
SELECT STUFF(CAST((
SELECT ',' + Cast(SEQUENCE_NO AS varchar(10)) + '-' +
COALESCE(OPERATION_TYPE, SERVICE_ID, RESOURCE_ID)
FROM dbo.OPERATION
WHERE TYPE = @TYPE AND BASE_ID = @BASE AND
LOT_ID = @LOT AND SPLIT_ID = @SPLIT AND
SUB_ID = @SUB AND SEQUENCE_NO < @eSeq
ORDER BY SEQUENCE_NO
FOR XML PATH(''), TYPE
) AS nvarchar(255)), 1, 1, '') AS return_value
)
/*end of function code*/
GO
--
Wow, that's the perfect index for that query. That's as fast as that lookup can get, so that's not the issue.
ASKER
Thanks, how do i call the Inline view using CROSS APPLY from my view?
ASKER
Got it to cross apply - 3x longer...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As to the sample data, what we really need is CREATE TABLE and INSERT statement(s) to load the table. A picture/"splat" of data doesn't really help us with testing.
Open in new window