optimising the query

Hi experts, please suggest a better way to write the below query in bold,thanks

 ;WITH CTE_VEX (PolicyID, PolicyNum, LogDate, VehEffDate, VehExpDatE, VehicleID, VehicleLogID, Make, Model, VehYear) AS
            (SELECT
                  P.PolicyID,
                  P.PolicyNum,
                  V.LogDate,
                  CAST(CONVERT(NCHAR(10),IsNull((SELECT MIN(LogDate) FROM Hallmark_Log..Vehicle WHERE VehicleID = V.VehicleID AND ActionType = 'I'),
                        P.EffectiveDatE),101) AS DATETIME) AS VehEffDate,
                  IsNull((SELECT DATEADD(DAY,-1,MIN(LogDate)) FROM Hallmark_Log..Vehicle WHERE VehicleID = V.VehicleID AND ActionType = 'D'),
                        (CASE P.Status WHEN 3 THEN P.CancelledDate ELSE P.ExpirationDate END)) AS VehExpDatE,
                  V.VehicleID,
                  V.Vehicle_LogID,
                  V.Make,
                  V.Model,
                  V.VehicleYear
            FROM Hallmark_Log..Vehicle V WITH (NOLOCK)
                  INNER JOIN Hallmark..Auto A WITH (NOLOCK) ON V.AutoID = A.Autoid
                  INNER JOIN HallmarK..PolicY P WITH (NOLOCK) ON A.PolicyID = P.PolicYID
            WHERE V.AUTOID IN (SELECT  AUTOID FROM Hallmark_Log..Vehicle WHERE VEHICLEID IN (SELECT VEHICLEID FROM HALLMARK..AUTO WHERE POLICYID IN(SELECT POLICYID FROM @PPR))))
 
sqlcuriousAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
You mean something like:
SELECT autoid
FROM   hallmark_log..vehicle
WHERE  vehicleid IN (SELECT vehicleid
                     FROM   hallmark..AUTO
                     WHERE  policyid IN(SELECT policyid
                                        FROM   @PPR))  

Open in new window


Or like:
SELECT autoid
FROM   hallmark_log..vehicle
WHERE  vehicleid = (SELECT vehicleid
                     FROM   hallmark..AUTO
                     WHERE  policyid = (SELECT policyid
                                        FROM   @PPR))  

Open in new window


I really like this SQL Formatter..

http://www.dpriver.com/pp/sqlformat.htm

HTH,

Kent
0
ralmadaCommented:
do you really need that WHERE clause there since you have the joins already? try like this
;WITH CTE_VEX (PolicyID, PolicyNum, LogDate, VehEffDate, VehExpDatE, VehicleID, VehicleLogID, Make, Model, VehYear) AS
(
	SELECT
                  P.PolicyID,
                  P.PolicyNum,
                  V.LogDate,
                  CAST(CONVERT(NCHAR(10),IsNull((SELECT MIN(LogDate) FROM Hallmark_Log..Vehicle WHERE VehicleID = V.VehicleID AND ActionType = 'I'),
                        P.EffectiveDatE),101) AS DATETIME) AS VehEffDate,
                  IsNull((SELECT DATEADD(DAY,-1,MIN(LogDate)) FROM Hallmark_Log..Vehicle WHERE VehicleID = V.VehicleID AND ActionType = 'D'),
                        (CASE P.Status WHEN 3 THEN P.CancelledDate ELSE P.ExpirationDate END)) AS VehExpDatE,
                  V.VehicleID,
                  V.Vehicle_LogID,
                  V.Make,
                  V.Model,
                  V.VehicleYear
            FROM Hallmark_Log..Vehicle V WITH (NOLOCK)
                  INNER JOIN Hallmark..Auto A WITH (NOLOCK) ON V.AutoID = A.Autoid
                  INNER JOIN HallmarK..PolicY P WITH (NOLOCK) ON A.PolicyID = P.PolicYID
           	INNER JOIN @PPR ppr on A.PolicyID = ppr.PolicyID
)

Open in new window

0
LowfatspreadCommented:
like this?

how many rows are you expecting on @ppr?

if its more than a couple ... you may get better results with a normal temp table which you have better indexing options on (and statistics capability)

it may be better to actually combine the dates subquery with the base table select for vehicle using OVER...


what is the actual performance problem you are seeking to solve?
;WITH CTE_VEX (PolicyID, PolicyNum, LogDate, VehEffDate, VehExpDatE, VehicleID, VehicleLogID, Make, Model, VehYear) AS
            (SELECT
                  P.PolicyID,
                  P.PolicyNum,
                  V.LogDate,
                  CAST(CONVERT(NCHAR(10),IsNull(m.idate,P.EffectiveDatE),101) AS DATETIME) AS VehEffDate,
                  IsNull(DATEADD(DAY,-1,m.dDate)
                         ,(CASE P.Status WHEN 3 THEN P.CancelledDate ELSE P.ExpirationDate END)) AS VehExpDatE,
                  V.VehicleID,
                  V.Vehicle_LogID,
                  V.Make,
                  V.Model,
                  V.VehicleYear
            FROM Hallmark_Log..Vehicle V WITH (NOLOCK)
            INNER JOIN Hallmark..Auto A WITH (NOLOCK) 
               ON V.AutoID = A.Autoid
            INNER JOIN HallmarK..PolicY P WITH (NOLOCK)
               ON A.PolicyID = P.PolicYID
            inner join @PPR as x
               on p.POLICYID =x.policyid 
            left outer join
                  (SELECT vehicleid
                         ,MIN(case actiontype when 'I' then LogDate end) as Idate 
                         ,MIN(case actiontype when 'D' then LogDate end) as Ddate 
                     FROM Hallmark_Log..Vehicle 
                    WHERE ActionType in ( 'I','D')
                    group by vehicleid
                  ) as M
              on v.vehicleid=m.vehicleid

Open in new window

0
Scott PletcherSenior DBACommented:
First, make sure @PPR has a PRIMARY KEY of POLICYID (you can designate a pk on a table variable).

Then either INNER JOIN like ralmada suggested or even better just do an EXiSTS():

FROM Hallmark_Log..Vehicle V WITH (NOLOCK)
                  INNER JOIN Hallmark..Auto A WITH (NOLOCK) ON V.AutoID = A.Autoid
                  INNER JOIN HallmarK..PolicY P WITH (NOLOCK) ON A.PolicyID = P.PolicYID
WHERE EXISTS(SELECT 1 FROM @PPR ppr WHERE ppr.PolicyID = A.PolicyID)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlcuriousAuthor Commented:
thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.