Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Without having the data this is very difficult to work on, but I created the tables and duplicated the procedure to get a rough idea on the query plan and potential problems.</p> <p>First noticable thing, the part of the query written as :</p> <pre><code>SELECT DISTINCT Aid FROM Authors EAE WHERE EAE.[Year] &gt;= @year AND EAE.Email IS NOT NULL AND EAE.Email != ' ' </code></pre> <p>Is going to table scan, you have Year as your partitioning key, but within each partition there is no index supporting the email clauses in the query. As a side note the EAE.Email != ' ' might not give you quite what you expect. If ' ' != '' print 'true' else print 'false' That will give false for most systems. (Based on ansi padding)</p> <pre><code>FROM Articles ED INNER JOIN Authors EAD ON EAD.ArId = ED.ArId WHERE EAD.Aid = [YearAuthors].Aid AND ED.ClassNumber = @classNumber </code></pre> <p>ED.ClassNumber will have no supporting index, causing a clustered index scan.</p> <p>In the final select statement : INNER JOIN Authors EA ON EA.Aid = #TT.Aid </p> <p>This has no supporting index on the #TT side and doesn't appear to be one on the Authors Table side.</p> <pre><code>WHERE EA.Email IS NOT NULL AND EA.Email != ' ' </code></pre> <p>this has no supporting index, causing a scan.</p> <p>There are a lot more issues in there, with a considerable number of sort's appearing that probably will disappear with suitable indexes - You will have to sort out some of the basic indexing on the tables and then get a new query plan / set of problems and iteratively fix the plan - you will not fix it in a single 'silver bullet' shot.</p>
    singulars
    1. This table or related slice is empty.
    1. POVery Slow Sql Server Query
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAndrew
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POVery Slow Sql Server Query
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    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. VTUpMod
    1. This table or related slice is empty.
 

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