Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1920212223»»»

Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten) Expand / Collapse
Author
Message
Posted Sunday, September 5, 2010 3:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 6,527, Visits: 9,042
WayneS (9/5/2010)
Hugo Kornelis (9/3/2010)
Please go back to page 4 of this topic. One of my posts on that page includes a very fast algorithm that completely avoids undocumented features. And further down, Jeff even posted an optimized version of that code.

Hugo Kornelis (9/4/2010)
My examples on page 11 in this topic ...


Hugo, I have a small request. Instead of posting the page #, could you post the url of the post (in case you're not aware of this, click the post # at the bottom left of the post and up pops a message box with the complete url to the post).

The reason I ask is that the # of posts to display in a page is configurable. If yours is set to 10 per page, then your post on page 4 is between post 31-40. If yours is set to 20, then your post is between 61-80. Mine is set to 50 per page - which is between 151-200.

I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

For me, page 11 would be for posts 550-600, and we're not that high yet.

Just give it a few days...



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #980842
Posted Sunday, September 5, 2010 7:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:06 PM
Points: 5,592, Visits: 9,623
Hugo Kornelis (9/5/2010)
I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

Not a problem. FYI, you can adjust this at http://www.sqlservercentral.com/Forums/EditForumSettings.aspx
For me, page 11 would be for posts 550-600, and we're not that high yet.

Just give it a few days...

.... I hope not! We just broke 200!.


Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #980875
Posted Sunday, September 5, 2010 7:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:00 PM
Points: 38,381, Visits: 35,306
WayneS (9/5/2010)
Hugo Kornelis (9/5/2010)
I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

Not a problem. FYI, you can adjust this at http://www.sqlservercentral.com/Forums/EditForumSettings.aspx
For me, page 11 would be for posts 550-600, and we're not that high yet.

Just give it a few days...

.... I hope not! We're just broke 200!.


Heh... and that's just on the rewrite!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #980877
Posted Monday, September 6, 2010 11:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
Hugo Kornelis (9/5/2010)
Indeed. The check itself changes the options for the optimizer. The method now even appears to be immune to parallel execution problems, since the parallel streams have to be gathered and re-synched before the row numbers can be calculated.

Sadly I fear it is not so. While it is true that the Sequence Project that calculates row numbers must run serially, there is no guarantee that the optimizer will not choose to restart parallelism after that iterator. If the critical Compute Scalar runs in parallel, bad things will happen.

With the current costing model, this is outrageously unlikely in practice because Compute Scalars are barely costed at all (reference 1; reference 2 - both from Conor). This costing arrangement may well change in SQL11.

Nevertheless, I can produce a plan today where parallelism is restarted for the Compute Scalar by choosing a suitably large CPU multiplier for the costing model. I illustrated the method just a few days ago on my blog: http://sqlblog.com/blogs/paul_white/archive/2010/09/01/inside-the-optimizer-plan-costing.aspx

Hugo Kornelis (9/5/2010)
There may be a way to break this method on current versions of SQL Server, but it takes someone smarter then me to find it. And even if you accidentally stumble over it, or if new versions of the optimizer start to wreck this method, you're still safe because of the builtin safety check.

I can conceive of a way to break the safety check, but it requires significant effort from the imagination:

We would need the optimiser to produce a plan that separates the sequence check into a Compute Scalar separate from that which performs the quirky update variable assignments. Further, something like an explicit sort would be needed between those two Compute Scalars, arranged very particularly so that the rows are in sequence at the safety check, but not at the variable-assignment iterator. Such a plan is presently all but impossible (and that may be understating it) but even so...

Paul




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #981196
Posted Monday, September 6, 2010 4:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 8,760, Visits: 10,590
Paul White NZ (9/6/2010)

I can conceive of a way to break the safety check, but it requires significant effort from the imagination:

We would need the optimiser to produce a plan that separates the sequence check into a Compute Scalar separate from that which performs the quirky update variable assignments. Further, something like an explicit sort would be needed between those two Compute Scalars, arranged very particularly so that the rows are in sequence at the safety check, but not at the variable-assignment iterator. Such a plan is presently all but impossible (and that may be understating it) but even so...

Paul

We can make a small code change which I believe guarantees that that particular quirk is impossible. If we modify one piece of your original code
SET     @AccountRunningTotal = AccountRunningTotal = 
CASE
WHEN AccountID = @PrevAccountID
THEN @AccountRunningTotal+Amount
ELSE Amount
END,

to be instead
SET     @AccountRunningTotal = AccountRunningTotal = 
CASE
WHEN Sequence = @Sequence+1
THEN CASE
WHEN AccountID = @PrevAccountID
THEN @AccountRunningTotal+Amount
ELSE Amount
END
ELSE 1/0
END,

then I think that the compound case statement which is the RHS of the assignment has to be evaluated at a single row - allowing the optimiser to split the evaluation of a single scalar expression so that parts of it are evaluated using elements from different rows would quite independently of quirky update render the assignment components of a SET clause so ill-determined as to be useless.
But of course the optimizer is a law unto itself, according to some, so I could be wrong.
edit: had an extra [/quote] in there which jumbled thiongs.


Tom
Post #981258
Posted Monday, September 6, 2010 8:51 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
Very nice, Tom! That would indeed be sufficient to avoid the issue.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #981289
Posted Monday, September 6, 2010 9:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:06 PM
Points: 5,592, Visits: 9,623
Paul White NZ (9/6/2010)
Very nice, Tom! That would indeed be sufficient to avoid the issue.


Hey guys - don't take this wrong. But I just love it when someone says to a guru "hey, what about this?" and the guru does a "OMG - you're absolutely right" type of thing. Just another example of not seeing the forest for the trees, even for the guru (who might have seen most of it, but missed something). Plus, I also love how the community works together to make things better.



Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #981291
Posted Tuesday, September 7, 2010 10:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
Paul White NZ (9/3/2010)
...my strong preference is for INDEX(1) over INDEX(0) for performance reasons: QO may choose to introduce an explicit sort with INDEX(0) since the TABLOCK hint allows for an IAM-ordered scan.

I need to correct myself here - or at least clarify - INDEX(1) does not guarantee order in the case of scans (it does for seeks of course).

Any index scan can be performed in allocation order (rather than index order) if TABLOCK is specified, or if the effective isolation level is READ UNCOMMITTED.

There is an optimizer quirk that means that some plans using INDEX(0) may include an unnecessary sort (which does not apply to INDEX(1)). I will be blogging the details for next week's T-SQL Tuesday (the topic is indexes).

Paul




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #982029
Posted Wednesday, September 22, 2010 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 12, 2015 10:02 AM
Points: 9, Visits: 230
Hi Jeff,
Records aren't always stored physically ordered on a data page. Most of the time they are physically ordered correctly, but the actual order comes from the slot array. I was curious to see whether a different physical order breaks the quirky update.

Turns out it does not break the quirky update:
-- create the table
USE tempdb
GO

CREATE TABLE test
(
id int not null,
value uniqueidentifier,
deltaFromPrevious int,
constraint pk_test primary key clustered (id)
)

INSERT test (id,value)
VALUES
(2,NEWID()),
(4,NEWID()),
(6,NEWID()),
(8,NEWID()),
(10,NEWID())
GO

-- run quirky update
DECLARE @dfp int, @prev int;

UPDATE test
SET @dfp = deltaFromPrevious = id-@prev,
@prev = id;

-- everything looks fine here.
select * from test


-- mess up the physical order:
UPDATE test SET id = 5 WHERE id = 10;
GO
-- dbcc page now tells us that physically the
-- rows are out of order.

-- run the quirky update again:
DECLARE @dfp int, @prev int;
UPDATE test
SET @dfp = deltaFromPrevious = id-@prev,
@prev = id;

-- everything still looks fine here:
select * from test


Post #991095
Posted Saturday, September 25, 2010 3:05 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
Paul White NZ (9/7/2010)
There is an optimizer quirk that means that some plans using INDEX(0) may include an unnecessary sort (which does not apply to INDEX(1)). I will be blogging the details for next week's T-SQL Tuesday (the topic is indexes).

Yeah well I missed T-SQL Tuesday by some margin, but it's up now:
http://sqlblog.com/blogs/paul_white/archive/2010/09/23/a-tale-of-two-index-hints.aspx




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #993231
« Prev Topic | Next Topic »

Add to briefcase «««1920212223»»»

Permissions Expand / Collapse