The first thing that I would do is improve the readability of your query (slightly) by rewriting it as:
;WITH main AS (
SELECT pat1.PatID, pat1.DocID, pat1.Site, doc.RefDate, doc.Report
FROM PatMapTable AS pat1 WITH (NoLock)
INNER JOIN DocumentView AS doc WITH (NoLock)
ON doc.DocID = pat1.DocID
),
dx AS (
SELECT pat2.DocID, diag.Code, diag.Text
FROM PatMapTable AS pat2 WITH (NoLock)
INNER JOIN DiagnosisView AS diag WITH (NoLock)
ON pat2.Site = diag.Site AND pat2.VisitID = diag.VisitID
)
SELECT main.PatID, main.DocID, main.Site, main.RefDate, main.Report, ad.DocID AS AddID,
ad.RefDate AS AddRefDate, ad.Report AS AddReport, dx.Code, dx.Text
FROM main
LEFT OUTER JOIN DocumentView AS ad WITH (NoLock)
ON main.DocID = ad.ParentID
LEFT OUTER JOIN dx
ON main.DocID = dx.DocID
ORDER BY main.DocID, AddID, dx.Code
That won't help with performance though.
The first step would be to look at the actual execution plan (menu: Query / Include Actual Execution Plan) and check to see if your tables are being scanned or if indexes are being scanned. I suspect the former, in which case the tables lack the indexing you need to speed up your queries.
If you post that execution plan, folks may be able to look at it and make further suggestions.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St