August 27, 2015 at 5:28 am
I have a view as below:
CREATE VIEW V1 AS
SELECT
T1.Col1, F1.Col1, T1.Col2, T2.Col2...
FROM T1
INNER JOIN T2 ON T1.Col2 = T2.Col1
CROSS APPLY
UDF(T1.Col1, T2.Col2) F1
The inner join returns million records. Here, in this case, I know the exact value of T1.Col1 which will be passed from the application. Is there a way to rewrite the above view so that the cross apply can be applied only on the filtered records (based on T1.Col1 value) instead of processing all records and then filter later?
Current view doesn't return any value even after 20 minutes :crying:, whereas when I pass the values directly to the UDF, it returns the output in less than a second and the inner join without cross apply takes 2 seconds.
August 27, 2015 at 6:00 am
SathishK (8/27/2015)
I have a view as below:
CREATE VIEW V1 AS
SELECT
T1.Col1, F1.Col1, T1.Col2, T2.Col2...
FROM T1
INNER JOIN T2 ON T1.Col2 = T2.Col1
CROSS APPLY
UDF(T1.Col1, T2.Col2) F1
The inner join returns million records. Here, in this case, I know the exact value of
T1.Col1which will be passed from the application. Is there a way to rewrite the above view so that the cross apply can be applied only on the filtered records (based on T1.Col1 value) instead of processing all records and then filter later?Current view doesn't return any value even after 20 minutes :crying:, whereas when I pass the values directly to the UDF, it returns the output in less than a second and the inner join without cross apply takes 2 seconds.
From what you describe, it sounds like UDF is your problem, so you'll need to post the definition of it. If it's a multi-statement table-value function (MTVF) and not an inline (ITVF) one, performance is going to tank.
Just to make sure, are T1.Col2 and T2.Col1 the same data type?
August 27, 2015 at 6:37 am
From what you describe, it sounds like UDF is your problem, so you'll need to post the definition of it. If it's a multi-statement table-value function (MTVF) and not an inline (ITVF) one, performance is going to tank.
The UDF is a MTVF with 4 cursors in it. Below is the skeleton of the UDF:
CREATE FUNCTION [dbo].[UDF](@Col1 INT, @Col2 INT)
RETURNS @TBL TABLE(Col1 int, Col2 int, Col3 VARCHAR(10), Col4 int) AS
DECLARE CURSOR1 CURSOR FOR
SELECT DISTINCT Col1 FROM TBL1 WHERE Col2 = @Col1 AND Col3 = @Col2
OPEN CURSOR1
FETCH NEXT FROM CURSOR1 INTO @Col1
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE CURSOR2 CURSOR FOR...
INSERT INTO @TBLVAR
SELECT * FROM SRCTBL1 WHERE <condition>
CLOSE CURSOR2
DEALLOCATE CURSOR2
DECLARE CURSOR3 CURSOR FOR...
INSERT INTO @TBLVAR
SELECT * FROM SRCTBL2 WHERE <condition>
CLOSE CURSOR3
DEALLOCATE CURSOR3
DECLARE CURSOR4 CURSOR FOR...
INSERT INTO @TBLVAR
SELECT * FROM SRCTBL3 WHERE <condition>
CLOSE CURSOR4
DEALLOCATE CURSOR4
CLOSE CURSOR1
DEALLOCATE CURSOR2
SELECT Col1, Col2, Col3, Col4
For security reasons and complexity of the UDF, I can't provide the actual function here, but, this is the basic structure of the UDF. In case, if you need the actual query, I can create a sample and share it here.
Just to make sure, are T1.Col2 and T2.Col1 the same data type?
Yes, both are of type int.
August 27, 2015 at 6:44 am
We can't tune it if we can't see it.
If you can;'t post it, then you're going to have to rewrite it yourself into the form of an in-line table valued function (a single select). If you can't, then the current performance will have to be accepted, as functions are notorious performance problems, and cursors in a function are just going to make it orders of magnitude worse.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2015 at 7:09 am
GilaMonster (8/27/2015)
We can't tune it if we can't see it.If you can;'t post it, then you're going to have to rewrite it yourself into the form of an in-line table valued function (a single select). If you can't, then the current performance will have to be accepted, as functions are notorious performance problems, and cursors in a function are just going to make it orders of magnitude worse.
Amen to all points Gail makes. There's nothing else to say.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply