Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 5,296 through 5,310 (of 66,738 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 1

Jeff, check the MVP offers (private newsgroup). I think SQL Mag will give you a free sub. Heck, you've earned it.

Adam Machanic SSCoach Points: 15370 More actions · Answer 2

Jeff Moden (6/3/2009)They are currect that a cursor would be linear... but that's terribly slow compared to the "Psuedo-Cursor" or "Quirky" update method. And, heh... Before anyone goes on a tear about that method, make sure you include properly written code that also generates at least a million rows of test data and the update actually fails to produce the desired results. Properly written, at this point, means don't do it on a partition, force parallism not to occur, force the usage of the clustered index, no joins allowed during the running aggregation, and force and exclusive tab lock.

I recently tested the "quirky update method" on a project I'm working on. The query needs to do a relatively complex moving running sum (the running period progresses as you move through periods, based on some logic) over 272 million rows. The update isn't that much faster than the cursor I'd come up with--5-7%, depending on what else was running on the box, especially because the rows first need to get shoved into a temp table. And the code is much, much more difficult to read. I decided to go with the cursor, and will flip to a SQLCLR procedure soon, which I expect will give me a much greater improvement. Disregarding whether it's a good idea to use, have you seen this method actually yield substantial (say, 50%+) improvements over properly configured cursors (i.e., LOCAL READ_ONLY)?

--
Adam Machanic
whoisactive

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I subscribe to SQL Server Magazine. I haven't read the article in depth, but a quick scan shows why the cursor-based solution works better than the set-based solution. The code for the set-based solution is using a triangular join. As the number of records increases in the join, the slower it gets.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Adam Machanic (6/3/2009)
Disregarding whether it's a good idea to use, have you seen this method actually yield substantial (say, 50%+) improvements over properly configured cursors (i.e., LOCAL READ_ONLY)?

Heh... how would you use only Read-Only with a cursor and still only be able to return a single result set? Or are you just talking about read-only from the source?

To answer your question on speed, I'll write a test tonight.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Steve Jones - Editor (6/3/2009)
Jeff, check the MVP offers (private newsgroup). I think SQL Mag will give you a free sub. Heck, you've earned it.

Thanks for the tip, Steve. I'll take a look.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 6

I subscribe and have just scanned the article. It is a quality piece of work, and looks to be very thorough (as one would expect).

One thing to mention is that the article is the first of a series - next edition he will be exploring 'other solutions' to running aggregates.

I would be amazed if he doesn't cover the UPDATE-with-variables approach.

For the record, I have defended the UPDATE approach in depth more than once elsewhere, and continue to consider it the best performing solution for SQL2K5, and perfectly reliable with correct code. Bring on the doubters! 😛

A 2K8 CLR aggregate (with an extra parameter to enable reliable ordering even with a parallel plan) may come close in terms of performance, and would become my preferred solution if so. I have been meaning to write CLR aggregates for running totals and string concatenation since finally installing 2K8 a week or so ago. Maybe I'll get chance this weekend, or maybe Adam will save me the effort by writing one first 😎

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Adam Machanic SSCoach Points: 15370 More actions · Answer 7

Jeff Moden (6/3/2009)
Heh... how would you use only Read-Only with a cursor and still only be able to return a single result set? Or are you just talking about read-only from the source?

Read only from the source. Intermediate rows into a temp table. Is there another way to do it? I've written more cursors in the last month than I have in my entire career to date so I'm still getting my bearings 😛

To answer your question on speed, I'll write a test tonight.

This answer surprises me; I thought that given your interest in the technique you would already have an answer here. If not for speed, what's your purpose in using it?

--
Adam Machanic
whoisactive

Paul White SSC Guru Points: 150468 More actions · Answer 8

Adam Machanic (6/3/2009)

This answer surprises me; I thought that given your interest in the technique you would already have an answer here. If not for speed, what's your purpose in using it?

My guess would be that Jeff would rather submit something 'better' than a link to something existing since this can be such an emotional topic!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Paul White (6/3/2009)

Adam Machanic (6/3/2009)
This answer surprises me; I thought that given your interest in the technique you would already have an answer here. If not for speed, what's your purpose in using it?
My guess would be that Jeff would rather submit something 'better' than a link to something existing since this can be such an emotional topic!

You're absolutely spot on, Paul. Words mean nothing in this business. At the end of the day, only proof in code matters. Thank you for your confidence. I also have a couple of new things that will come into play both in the article rewrite and the "book". Thought I'd give folks a sneak preview.

The other reason to do this is that I've recently got a call from OSHA. They told me if one more person rides my hiney about this, I'll have to install handrails on it. I figure a more detailed post may be a short term solution to preventing that eventuality.:-P

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Adam Machanic (6/3/2009)
Jeff Moden (6/3/2009)
Heh... how would you use only Read-Only with a cursor and still only be able to return a single result set? Or are you just talking about read-only from the source?
Read only from the source. Intermediate rows into a temp table. Is there another way to do it? I've written more cursors in the last month than I have in my entire career to date so I'm still getting my bearings 😛

Ok... I was going to shoot at updating the source table but it'll be lot's easier the way you suggest.

Heh... understood about writing cursors... I only do it when I want to show how bad they can really be and I have to lookup how to make a cursor every time. I should "can" one.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Adam Machanic (6/3/2009)
... (the running period progresses as you move through periods, based on some logic) ...

Could you explain this in a little more detail? I am curious what you mean by it.

Paul White SSC Guru Points: 150468 More actions · Answer 12

Lynn Pettis (6/3/2009)
Could you explain this in a little more detail? I am curious what you mean by it.

I took it to mean a moving-sum (like a moving average) perhaps a sum over the last 12 items for example?

I have been known to be wrong however.

/P

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 13

Jeff Moden (6/3/2009)[hrThe other reason to do this is that I've recently got a call from OSHA. They told me if one more person rides my hiney about this, I'll have to install handrails on it. I figure a more detailed post my be a short term solution to preventing that eventuality.:-P

LOL:laugh:

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 14

Jeff Moden (6/3/2009)

Heh... understood about writing cursors... I only do it when I want to show how bad they can really be and I have to lookup how to make a cursor every time. I should "can" one.

I thought you kept those in the s***can?:-D

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 15

WayneS (6/3/2009)
I thought you kept those in the s***can?:-D

Heh... you know me too well. Yep... that's where I keep them... right along with those bloody handrails. 😛

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)