Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You can usually optimize queries by:</p> <ul> <li><p>picking a <strong>good</strong> clustering key on your tables - good being <code>narrow, unique, static, ever-increasing</code>. <code>INT IDENTITY</code> is a classic good key - GUID's are a horribly bad example (since they lead to excessive index fragmentation - read Kim Tripp's <a href="http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx" rel="nofollow noreferrer">GUIDs as Primary and/or clustering key</a> for lots more details)</p></li> <li><p>making sure all foreign key columns in the child tables are <strong>indexed</strong> so that JOINs and lookups are performed faster</p></li> <li><p>selecting as little columns as you really need (you seem to be doing this just fine)</p></li> <li><p>trying to <em>cover the query</em>, e.g. create indices on the tables involved that have all the necessary columns - either directly as index columns, or as <em>included</em> columns (SQL Server 2008 and onwards)</p></li> <li><p>possibly adding additional indices to speed up range queries, and/or help with sorting/ordering</p></li> </ul> <p>Looking at your queries and table definitions:</p> <ul> <li><p>I don't seem to see any primary keys - add those!</p></li> <li><p>you would have to make sure to have foreign key indices on <code>pp.production_line</code> (assuming <code>t.production_line</code> is the primary key of the other table)</p></li> <li><p>you should see if you can find a good index to handle the range query on <code>t.UTC</code></p></li> <li><p>you should check if it makes sense to create an index on <code>production_plan2</code> to contain all the columns (<code>order_id, pp.prod_start, pp.prod_end</code>) </p></li> <li><p>you should check if it makes sense to create an index on <code>temperatures2</code> to contain all the columns (<code>UTC, temperature_1</code>) </p></li> </ul> <p><strong>Update:</strong> you can capture the actual execution plan by enabling that option from the SSMS toolbar:</p> <p><img src="https://i.stack.imgur.com/2EsYR.png" alt="enter image description here"></p> <p>or from the menu under <code>Query &gt; Include Actual Execution Plan</code></p>
    singulars
    1. This table or related slice is empty.
    1. POInefficient SQL query with DATETIME calculations. How to optimize?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmarc_s
    1. USmarc_s
    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
    1. COThanks for comments and the article on GUID as primary keys. Unfortunately, the design of the application is older and I have no influence here. But I will pass the information to the one who may be interested. The `t.production_line` cannot be a primary key for the temperatures. The `production_line` is a kind of identification of one of the few physical machines.
      singulars
      1. PO
      1. USpepr
    2. COI have updated the question. The indexes helped. However, the second problem remains. I did save the execution plan. When zipped, it takes about 4 KB. What there any usual place to upload files at StackOverflow?
      singulars
      1. PO
      1. USpepr
 

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