Alternative of Subqueries in where clause

Hi there i have started working on project and come across a view which contains lots of sub-queries in where clause. I know that subqueries make the performance very slow and it is obvious in my situation as it is taking long time to return rows. Can you please help me.

My code for the view is
SELECT DISTINCT UnitCode AS Unit, Unit_Name, Month, DataCategory, Field, CaseManagerValue, ProviderValue, PatientID, MessageText, CaseManager, ProviderName
FROM         vw_Validation_Error AS V
WHERE     (LoadDate =
                          (SELECT     MAX(LoadDate) AS LDate
                            FROM          vw_Validation_Error AS V2
                            WHERE      (UnitCode = V.UnitCode))) AND (Month =
                          (SELECT     MAX(Month) AS Mon
                            FROM          vw_Validation_Error AS V3
                            WHERE      (UnitCode = V.UnitCode) AND (LoadDate = V.LoadDate))) AND (subcode = 1) AND (Priority <= 5) AND (AdmissionID =
                          (SELECT     MAX(AdmissionID) AS Admission
                            FROM          vw_Validation_Error AS V2
                            WHERE      (PatientID = V.PatientID) AND (DOA = V.DOA))) AND (DOA =
                          (SELECT     MAX(DOA) AS DOA
                            FROM          vw_Validation_Error AS V2
                            WHERE      (PatientID = V.PatientID)))
ORDER BY Unit, PatientID, Field, DataCategory

Open in new window


Kindest regards
Ali ShahSQL DeveloperAsked:
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.

ste5anSenior DeveloperCommented:
The problem are not the sub-queries per se. Imho it's the fact that you query views.
Have you created indices which cover the sub-queries?

Can you post the actual execution plan XML?

btw, please use always the table alias names.
sdstuberCommented:
Something like this might help depending on the usage
You only need to read the view once, but depending on usage, you might need to read all of it which could be expensive.

SELECT DISTINCT unitcode AS unit,
                unit_name,
                month,
                datacategory,
                field,
                casemanagervalue,
                providervalue,
                patientid,
                messagetext,
                casemanager,
                providername
  FROM (SELECT unitcode,
               unit_name,
               month,
               datacategory,
               field,
               casemanagervalue,
               providervalue,
               patientid,
               messagetext,
               casemanager,
               providername,
               subcode,
               priority,
               RANK() OVER(PARTITION BY unitcode ORDER BY loaddate DESC, month DESC) r1,
               RANK() OVER(PARTITION BY patientid ORDER BY doa DESC, admissionid DESC) r2
          FROM vw_validation_error v)
 WHERE subcode = 1 AND priority <= 5 AND r1 = 1 AND r2 = 1

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
sameer2010Commented:
Try this
SELECT DISTINCT UnitCode AS Unit, Unit_Name, Month, DataCategory, Field, CaseManagerValue, ProviderValue, PatientID, MessageText, CaseManager, ProviderName
from(
SELECT UnitCode AS Unit, Unit_Name, Month, DataCategory, Field, CaseManagerValue, ProviderValue, PatientID, MessageText, CaseManager, ProviderName,
row_number() over(partition by PatientID order by DOA desc,AdmissionID desc,Month desc, LoadDate desc) as temprank
FROM         vw_Validation_Error ORDER BY Unit, PatientID, Field, DataCategory) t1
where temprank=1

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
How many records exists in vw_Validation_Error table?
How many records do your actual query returns?
Why do you need the DISTINCT and the ORDER BY clauses? Those 2 clauses usually kills performance.
ste5anSenior DeveloperCommented:
I think sdstuber is right, I haven't realized that these sub-queries use all the same view as the main query.

Thus follow the RANK() or ROW_NUMBER() path as sdstuber wrote. When possible implement those columns already in the view itself.
Ali ShahSQL DeveloperAuthor Commented:
Thanks a lot yes it works like a charm and the difference is amazing
PortletPaulEE Topic AdvisorCommented:
I would also check that DISTINCT is really needed; adding distinct to a query adds a performance overhead.
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
Query Syntax

From novice to tech pro — start learning today.