Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It seems that subquery is executed for each row. At the same time query not seem as heavy for 900K records.</p> <hr> <p><strong>Added:</strong></p> <p>After some experiments I found following. Interesting that query plans for</p> <pre><code>update top (100) t set t.recent_5_min = (select MIN(value) from t t2 where t2.date between t.date - 5 and t.date - 1) from t t </code></pre> <p>and</p> <pre><code>update top (500) t set t.recent_5_min = (select MIN(value) from t t2 where t2.date between t.date - 5 and t.date - 1) from t t </code></pre> <p>are noticeably different. In the second case (and seems in original query also) Sort operator appears in the query plan performing sort over the <code>value</code> taking enormous resources.</p> <p>I tried following manual pivot/unpivot/aggregate technique, that transform query causing Constant Scan operator to be used instead of Sort, which is way better in this case:</p> <pre><code>;with cte as ( select t.date, t.recent_5_min, m.minVal from t left join t t1 on t1.date = t.date - 1 left join t t2 on t2.date = t.date - 2 left join t t3 on t3.date = t.date - 3 left join t t4 on t4.date = t.date - 4 left join t t5 on t5.date = t.date - 5 cross apply (select min(val) from (values (t1.value), (t2.value), (t3.value), (t4.value), (t5.value)) f(val)) m(minVal) ) update cte set recent_5_min = minVal </code></pre> <p>For me it passed just for a few seconds for generated 900K rows.</p> <p>The following work also, but takes longer time and more reads:</p> <pre><code>declare @t int select @t = 100 update top (@t) percent t set t.recent_5_min = (select MIN(value) from t t2 where t2.date between t.date - 5 and t.date - 1) from t t </code></pre> <p>For <code>t2.date between t.date - 240 and t.date - 1</code> it took about a minute for me.</p>
    singulars
    1. This table or related slice is empty.
    1. POOptimize correlated update
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USi-one
    1. USi-one
    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
    1. COit is indeed, it basically executes between for each row, but given that betweens on clustered index should be quite fast I dont' understand why it take longer than an hour...
      singulars
      1. PO
      1. USuser1514042
    2. CO@user1514042, well, OK, I have a better solution now, please see updated answer
      singulars
      1. PO
      1. USi-one
    3. COSee [Trick to Optimize TOP clause in SQL Server](http://www.mssqltips.com/sqlservertip/2053/trick-to-optimize-top-clause-in-sql-server/).
      singulars
      1. PO
      1. USNikola Markovinović
 

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