CODEWITHSUNDEEP
sqlsql-server
Prashant Kumar
Prashant Kumar

Reputation: 249

Improve performance of the query

My query is taking very long time.

select distinct JobName,
       ValidationType,
       AppName,
       Result,
       ResultType,
       ErrorWarningDetails,
       CvtStartDateTime 
  from contentvalidationjobdetails with (nolock)
 where appname=@AppName 
       and result=@Result 
       and (cast(cvtstartdatetime as date) > @Date )
       and concat(Jobname,validationtype) not in (
                  select concat(jobname,validationtype) 
                    from Contentvalidationjobdetails with (nolock) 
                   where appname = @AppName 
                         and CVTStartDateTime = (
                          select top 1 teststartdatetime 
                            from contentvalidation 
                           where appname=@AppName 
                                 and Teststartdatetime<@Date
                        order by teststartdatetime desc
                         )
           )

I know that the concat(jobname,validationtype) is taking time. how to handle this.

Upvotes: 0

Views: 42

Answers (1)

StanislavL
StanislavL

Reputation: 57381

Place the query in FROm section to be executed just once (not for each line in WHERE). Add outer join and leave only records which has no joins.

select distinct JobName,
       ValidationType,
       AppName,
       Result,
       ResultType,
       ErrorWarningDetails,
       CvtStartDateTime 
  from contentvalidationjobdetails with (nolock)
       LEFT OUTER JOIN (
                  select concat(jobname,validationtype) cnt
                    from Contentvalidationjobdetails with (nolock) 
                   where appname = @AppName 
                         and CVTStartDateTime = (
                          select top 1 teststartdatetime 
                            from contentvalidation 
                           where appname=@AppName 
                                 and Teststartdatetime<@Date
                        order by teststartdatetime desc) sub ON concat(Jobname,validationtype)=sub.cnt

 where appname=@AppName 
       and result=@Result 
       and (cast(cvtstartdatetime as date) > @Date ))
  HAVING sub.cnt is null

Upvotes: 1

Related Questions