Reputation: 1
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 to only 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, whereas when I pass the values directly to the UDF, it returns the output in less than a second.
Updated: Below is the structure of the UDF and I have update the question based on the comments below:
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
CLOSE CURSOR2
DEALLOCATE CURSOR2
DECLARE CURSOR3 CURSOR FOR...
INSERT INTO @TBLVAR
SELECT * FROM SRCTBL2 WHERE
CLOSE CURSOR3
DEALLOCATE CURSOR3
DECLARE CURSOR4 CURSOR FOR...
INSERT INTO @TBLVAR
SELECT * FROM SRCTBL3 WHERE
CLOSE CURSOR4
DEALLOCATE CURSOR4
CLOSE CURSOR1
DEALLOCATE CURSOR2
SELECT Col1, Col2, Col3, Col4
Upvotes: 0
Views: 920
Reputation: 10098
Create an inline table-valued function instead of the view:
create function fnx (@col1 int)
returns table
as
return (
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
where t1.col1 = @col1
)
This way you can pass the parameter to filter on t1.col1.
Upvotes: 1
Reputation: 1778
Use an in memory system. If you write this with java or .net. much faster results will be created.
Create a microservice to cache the results and serve them.
Microservices , wikipedia ,
https://en.m.wikipedia.org/wiki/Microservices
Upvotes: 0
Reputation: 176324
Use Common Table Expression:
CREATE VIEW V1
AS
WITH cte(cols...) AS
(
SELECT
T1.Col1, F1.Col1, T1.Col2, T2.Col2...
FROM T1
INNER JOIN T2
ON T1.Col2 = T2.Col1
WHERE T1.col1 = ?
)
SELECT *
FROM cte c
CROSS APPLY UDF(c.Col1, c.Col2) F1
Upvotes: 0