Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I did some testing on all of the solutions provided here in the other answers, my original "Hairy Recursive CTE" option and for the sake of completeness a simple cursor based approach. To my great surprise the cursor option performed the best by a clear margin in all my tests (1K Rows, 10KRows, 50K Rows, 500K Rows)</p> <p>Here are the average times for each approach for 10K records:<br> <strong>Hairy Recursive CTE:</strong> 3 minutes 55 seconds<br> <strong>CROSS APPLY (Ben Dempsey):</strong> 21-25 seconds<br> <strong>SUBSELECTS (Tim Khouri):</strong> 19-21 seconds<br> <strong>CURSOR:</strong> 1-2 Seconds</p> <p><strong>Here is my cursor based solution:</strong></p> <pre><code>Declare @temp TABLE( DocID INT PRIMARY KEY NOT NULL, SortOrder INT NOT NULL, PageCount INT NOT NULL, BegPg int, EndPg int ) Insert into @temp (DocID,SortOrder,PageCount) SELECT top 50000 docid, ROW_NUMBER() OVER (ORDER BY DOCID),Pages FROM tblDocuments DECLARE @PC int SET @PC=1 DECLARE @FetchPageCount int DECLARE @FetchDocID int DECLARE myCursor CURSOR FOR SELECT DocID, PageCount FROM @temp ORDER BY SortOrder OPEN myCursor FETCH NEXT FROM myCursor INTO @FetchDocID,@FetchPageCount WHILE @@FETCH_STATUS = 0 BEGIN UPDATE @temp SET BegPg=@PC, EndPg=@PC+ @FetchPageCount-1 WHERE (Docid=@fetchDocid) SET @PC = @PC + @FetchPageCount FETCH NEXT FROM myCursor INTO @FetchDocID,@FetchPageCount END CLOSE myCursor DEALLOCATE myCursor SELECT * FROM @temp </code></pre> <p>Who would have guessed it? Maybe cursors aren't always evil.</p> <p>A word of warning: Lest you be tempted to replace the update to the "WHERE CURRENT OF myCursor" syntax, it performed much slower than using the current version with a where clause, although still faster than most of the other approaches.</p>
    singulars
    1. This table or related slice is empty.
    1. PONeed some tSQL Wizardry: SQL Update Based on Running Total
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJohnFx
    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. VTUpMod
    1. COI would be interested to see the results from an actual table instead of the Table variable which is in memory. Make sure you put an index on SORTORDER (optionally include PAGECOUNT in the index). Oops NM, just noticed you did get it from an actual table, but check the index. If im bored maybe ill try it at home :)
      singulars
      1. PO
      1. USBen Dempsey
    2. CORunning totals is one of the few cases where cursors can improve performance over set-based methods (note I said can, not will) , they are worth trying when you need a running total. I'm glad you tried all aproaches as that is the only what to know what will work best with your particular db design and server specs.
      singulars
      1. PO
      1. USHLGEM
 

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