title: SQL Server : Query performance analysis - Any Optimization ideas?
body: <p>I have read multiple articles that recommends using SQL Server Profiler, so did I.</p>
  
  <p>I have a quite important query which makes the application freeze from time to time. The query includes multiple <code>LEFT JOIN ... ON ... AND (... AND ...) OR (... AND ...)</code>.</p>
  
  <p>I have no authority on the relational model that is being used, so I must work with it.</p>
  
  <p>The query is about getting nearby hazardous products places in case of fire in order to help plan the fire fighters intervention plan. The query works, it only need some optimizations.</p>
  
  <p>Now, I have this query had <code>6635 Reads</code>, <code>296 CPU</code>, <code>0 Writes</code>, <code>281 Duration</code>.</p>
  
  <p>All I can come up with is that the query is too slow and probably too heavy for the server to perform. In other words, it's not optimal at all.</p>
  
  <p>In case it matters, because I believe it does, the query has multiple calculations in its where clause like:</p>
  
  <pre><code>ATN2(SQRT(SIN(RADIANS(@latitude - CASE WHEN LocationType = @locationType THEN Buildings.Latitude ELSE Intersections.Latitude END) / 2 *...
  </code></pre>
  
  <p>I have no budget to buy tools or whatsoever.</p>
  
  <blockquote>
    <p><strong>UPDATE</strong></p>
  </blockquote>
  
  <p>As per request, here's the monstrous query:</p>
  
  <pre><code>SELECT PHM.ID, HP.Name, PHMD.TL, PHMD.QTY
      , PHMD.CT, PHMD.C, PHMD.MU
      , HP.HR, HP.FR, HP.RR, HP.SR
      , PHMD.CP
      , A.HN, A.Alpha
      , CASE WHEN PHM.LT &lt;&gt; @7 THEN TL.D ELSE TL.D + ' ' + TL2.D END AS TLcol
      , C.D AS CD
  FROM PHM 
      LEFT JOIN PHMD ON PHMD.ExID = PHM.ID 
      LEFT JOIN HP ON PHMD.HPID = HP.ID 
      LEFT JOIN BP ON PHM.LT=@5 
          AND BP.ID = PHM.BPID 
      LEFT JOIN A ON (PHM.LT=@4 
              AND A.ID = PHM.AID) 
          OR (PHM.LT=@5 
              AND BP.AID = A.ID) 
      LEFT JOIN I ON I.ID = PHM.IID  
      LEFT JOIN TL ON (PHM.LT&lt;&gt;@7 
              AND A.TLID = TL.ID) 
          OR (PHM.LT=@7 
              AND I.TLID1 = TL.ID)  
      LEFT JOIN TL AS TL2 ON PHM.LT=@7 
          AND I.TLID2 = TL2.ID 
      LEFT JOIN C ON TL.CID=C.ID 
      LEFT JOIN B ON A.ID = B.ExID 
      LEFT JOIN B ON B.ID = B.BID 
  WHERE EXISTS (SELECT * FROM PHMD WHERE PHMD.ExID=PHM.ID)
      AND (((PHM.LT=@4 AND PHM.AID=@8)    
          OR (PHM.LT=@5 AND PHM.BPID IN (SELECT ID FROM BP WHERE AID=@8)) 
      OR (PHM.LT=@6 AND PHM.BID IN (SELECT BID FROM B WHERE ExID=@8))) 
       AND PHM.DV = (
              SELECT MAX(PHMLD.DV) 
                  FROM PHM AS PHMLD 
                  WHERE PHMLD.LT = PHM.LT 
                      AND ISNULL(PHMLD.AddressID,0) = ISNULL(PHM.AID,0) 
                      AND ISNULL(PHMLD.SuiteID,0) = ISNULL(PHM.SID,0) 
                      AND ISNULL(PHMLD.BPID,0) = ISNULL(PHM.BPID,0) 
                      AND ISNULL(PHMLD.IID,0)=ISNULL(PHM.IID,0) 
                      AND ISNULL(PHMLD.BID,0)=ISNULL(PHM.BID,0)) 
                      OR 6371010 * (2 * ATN2(
                              SQRT((SIN(RADIANS(@1 - CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LAT ELSE I.LAT END)/2) 
                                  * SIN(RADIANS(@1 - CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LAT ELSE I.LAT END)/2) 
                                  + COS(RADIANS(CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LAT ELSE I.LAT END)) 
                                  * COS(RADIANS(@1)) 
                                  * SIN(RADIANS(@2 - CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LNG ELSE I.LNG END)/2) 
                                  * SIN(RADIANS(@2 - CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LNG ELSE I.LNG END)/2)))
                              , SQRT(1-(SIN(RADIANS(@1 - CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LAT ELSE I.LAT END)/2) 
                                  * SIN(RADIANS(@1 - CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LAT ELSE I.LAT END)/2) 
                                  + COS(RADIANS(CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LAT ELSE I.LAT END)) 
                                  * COS(RADIANS(@1)) 
                                  * SIN(RADIANS(@2 - CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LNG ELSE I.LNG END)/2) 
                                  * SIN(RADIANS(@2 - CASE WHEN PHM.LT&lt;&gt;@6 THEN B.LNG ELSE I.LNG END)/2))))
                          ) &lt;= @3)
  </code></pre>
  
  <blockquote>
    <p><strong><code>SET SHOWPLAN_XML ON</code> UPDATE</strong></p>
  </blockquote>
  
  <p>Please visit the following link for the execution showplan.
  <a href="http://pastebin.com/k6GxxSKF" rel="nofollow">http://pastebin.com/k6GxxSKF</a></p>
  
  <p>Thanks for you kind help! =)</p>