query taking a lot of time

WITH ISO_DR(PolicyID,  CompanyID, Persistency) AS
            (SELECT P.policyID, P.companyID, (SELECT effectiveDate from dbo.Policy where policyID =(select dbo.fnOriginalPolicyID(@policynum))) as PERSISTENCY
                  FROM Hallmark..Policy P WITH (NOLOCK))

Is there a better way to write this?
sqlcuriousAsked:
Who is Participating?
 
jogosCommented:
Basicly for each resultrow of the hallmark..policy table you do a select in (same/other as question of lowfatspread) a table that must return a specific value.

My reading this part of code will always returen the same value, one value
It could better be executed on it's own to assign its result in a variable and use that variable in the select.
declare @effectivedate as datetime
SELECT @effectivedate= effectiveDate from dbo.Policy where policyID =(select dbo.fnOriginalPolicyID(@policynum))
WITH ISO_DR(PolicyID,  CompanyID, Persistency) AS
(SELECT P.policyID, P.companyID,@effectivedate as PERSISTENCY 
                  FROM Hallmark..Policy P WITH (NOLOCK))

Open in new window

That would be a gain that counts.


In case there is a link between the 2 tables it can be rewriten as a join , now chosen for inner join and because I don't see the link with a dummy join-condition.
SELECT P.policyID, P.companyID,  a. PERSISTENCY 
                  FROM Hallmark..Policy P WITH (NOLOCK)
inner join (SELECT effectiveDate from dbo.Policy where policyID =(select dbo.fnOriginalPolicyID(@policynum))) as a on 1 = 1

Open in new window

0
 
LowfatspreadCommented:
please post the actual query you are using ... you've only posted the cte
0
 
LowfatspreadCommented:
also post the definition of dbo.fnOriginalPolicyID
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LowfatspreadCommented:
is this a cross database query?

or are the policy tables actually the same one?
0
 
jogosCommented:
sorry, forgot the ;  before the WITH
0
 
jogosCommented:
Anny feedback?
0
 
sqlcuriousAuthor Commented:
thnks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.