Function with in clause in where condition of SP decreasing the procedure performance

Question

I have a procedure that has following functions in where condition:

select col1,col2,col3...
from table1
where
(dbo.GetFilStatus(et.SgDate,et.Speed,(SELECT COUNT(J.JId) FROM tbl_Nt J 
               inner JOIN tbl_NAssign JN ON  JN.NNo =J.NNo
               inner JOIN dbo.tbl_CStatus JS ON JS.CStatusID=J.CStatusID 
               INNER JOIN dbo.tbl_SStatus ss ON ss.SStatusID=JS.SStatusID
               WHERE JN.DriID=et.DriID AND ss.SStatusID !=9),et.IgStatus) 
              in (Select val from Split('A,B,C,D,E',',')))
              )

getfilstatus status contains the following code:-

 if (@ServerDatetime <= DATEADD(MI,-10, GETDATE()))
           BEGIN
           IF(@xIgStatus = 'ON')
           BEGIN
             set @FilStatus= 'NoSignal'
             END
             ELSE
             BEGIN
              set @FilStatus= 'Stopped'
             end
           End
        else IF(@xIgStatus = 'ON')     
           Begin
           if(@Speed>5)
           begin
               if(@JCount<=0)
                 set @FilStatus='Moving'
               else 
                 set @FilStatus='Working' 
           end 
           else
           begin
           set @FilStatus= 'Idle'
           end                   
           End
        else 
           Begin
                 set @FilStatus= 'Stopped'
           end  
    RETURN @FilStatus

GetFilStatus always returns more than 10000 records. Sometimes its more than 100000. Its slowing the final output of query. Currently its taking more than 2 mins to return the output.
I am searching for any other option or any other trick using which the query performance can be increased and I could get the output in seconds.
Any suggestions? Any ideas?

Put (Select val from Split('A,B,C,D,E',',')) output in a Temp Table .. — Kishore Sahasranaman, Oct 14 '15 at 11:09
Pre-deriving a table from that constant split() expression would be a place to start — Alex K., Oct 14 '15 at 11:10
for further recommendations, content of the function `dbo.GetFilStatus` needs to be shared. — Kishore Sahasranaman, Oct 14 '15 at 11:34
Why use split at all? WHERE dbo.GetFileStatus(...) IN ('A','B','C','D','E'). See: http://www.w3schools.com/sql/sql_in.asp — J.H., Oct 14 '15 at 12:03
http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan - find out where the problem is first. — Neville Kuyt, Oct 14 '15 at 12:07
@filter parameter has 'A,B,C,D,E'.This param may also change its not constant. .I use split to convert ('A,B,C,D,E') to ('A','B','C','D','E').Also i have added getfilstatus functions code now. — Pwavel002, Oct 14 '15 at 12:34

Kishore Sahasranaman · Answer 1 · 2015-10-14T11:49:51.420

It is better to put the split items in a temp table, as the split function needs to execute each time in the query iterations.

The third parameter has a complex inline query, i have created a temp table for the subset data and filtered necessary data inline.

SELECT S.items AS value 
INTO   #splited_items 
FROM   Split('A,B,C,D,E', ',') S; 

SELECT Count(J.jid) AS Count_JId, 
       JN.driid     AS DriID 
INTO   #joined_table 
FROM   tbl_nt J 
       INNER JOIN tbl_nassign JN 
               ON JN.nno = J.nno 
       INNER JOIN dbo.tbl_cstatus JS 
               ON JS.cstatusid = J.cstatusid 
       INNER JOIN dbo.tbl_sstatus ss 
               ON ss.sstatusid = JS.sstatusid 
WHERE  ss.sstatusid != 9 
GROUP  BY JN.driid 

SELECT col1, 
       col2, 
       col3... from table1 
WHERE  ( dbo.Getfilstatus(et.sgdate, et.speed, (SELECT count_jid 
                                                FROM   #joined_table 
                                                WHERE  driid = et.driid), 
         et.igstatus) 
                IN (SELECT value 
                  FROM   #splited_items) )

The above two recommendations are the only things i can see from the query you have shared. — Kishore Sahasranaman, Oct 14 '15 at 12:14
try paginating the records http://stackoverflow.com/questions/2244322/how-to-do-pagination-in-sql-server-2008 — Kishore Sahasranaman, Oct 14 '15 at 12:49

Function with in clause in where condition of SP decreasing the procedure performance

1 Answers1