CODEWITHSUNDEEP
sql-serverperformanceviewcross-apply
Sathishkumar Karunamoorthy
Sathishkumar Karunamoorthy

Reputation: 1

SQL Server cross apply performance issue

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

Answers (3)

dean
dean

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

Rıfat Erdem Sahin
Rıfat Erdem Sahin

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.

  • Reference

Microservices , wikipedia ,

https://en.m.wikipedia.org/wiki/Microservices

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

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

Related Questions