Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It is a problem with your sub-query referencing your outer query, meaning the sub query has to be compiled and executed for <em>each row</em> in the outer query. Rather than using explicit temp tables, you can use a derived table. To simplify your example: </p> <pre><code>SELECT P.Column1, (SELECT [your XML transformation etc] FROM A where A.ID = P.ID) AS A </code></pre> <p>If P contains 10,000 records then SELECT A.ColumnX FROM A where A.ID = P.ID will be executed 10,000 times.<br> You can instead use a derived table as thus: </p> <pre><code>SELECT P.Column1, A2.Column FROM P LEFT JOIN (SELECT A.ID, [your XML transformation etc] FROM A) AS A2 ON P.ID = A2.ID </code></pre> <p>Okay, not that illustrative pseudo-code, but the basic idea is the same as the temp table, except that SQL Server does the whole thing in memory: It first selects all the data in "A2" and constructs a temp table in memory, then joins on it. This saves you having to select it to TEMP yourself.</p> <p>Just to give you an example of the principle in another context where it may make more immediate sense. Consider employee and absence information where you want to show the number of days absence recorded for each employee. </p> <p><strong>Bad:</strong> (runs as many queryes as there are employees in the DB) </p> <pre><code>SELECT EmpName, (SELECT SUM(absdays) FROM Absence where Absence.PerID = Employee.PerID) AS Abstotal FROM Employee </code></pre> <p><strong>Good:</strong> (Runs only two queries)</p> <pre><code>SELECT EmpName, AbsSummary.Abstotal FROM Employee LEFT JOIN (SELECT PerID, SUM(absdays) As Abstotal FROM Absence GROUP BY PerID) AS AbsSummary ON AbsSummary.PerID = Employee.PerID </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POWhy is inserting into and joining #temp tables faster?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USFrans
    1. USFrans
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. COThis doesn't completely fix the issue I'm having. Using LEFT OUTER JOINS and converting to using FOR XML PATH (to get proper nesting) I get a runtime of 1:26. Compared to 0:04 for the temp table method.
      singulars
      1. PO
      1. USJoseph Kingry
    2. COAh, fair enough :)
      singulars
      1. PO
      1. USFrans
    3. CORight, here is what I would do at this point; Break your query down into it's components and load each part up in Query Analyser and select "show estimated execution plan". Then do the same thing for the whole query. It should give you a good idea about which step is slow and how the optimiser is interpreting your request. See if it is one of the subcomponents or only when it is all put together. Look for loops and scans. Most importantly, you will probably find one step that is responsible for almost all the execution time - see if you can optimise that.
      singulars
      1. PO
      1. USFrans
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload