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?
 
Scott PletcherConnect With a Mentor Senior 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
 
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
 
ralmadaConnect With a Mentor Commented:
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
 
LowfatspreadConnect With a Mentor Commented:
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
 
sqlcuriousAuthor Commented:
thanks!
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.