Are the posted questions getting worse?

Question

Post reply

Are the posted questions getting worse?

Viewing 15 posts - 5,341 through 5,355 (of 66,712 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Hey friends, please help me out here. I'd really like to have an understanding of why this changed.

Thanks!

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

Lynn Pettis (6/4/2009)
Oh, please tell me exactly what I need to do. I have NO IDEA how to look things up in BOL.

And this guy has 385 points.... :w00t:

-Roy

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Paul White (6/4/2009)
The ultimate demo is where the original table already happens to have a suitable clustered index (it does happen!) and an existing column for the running total needs to be populated (though adding a NULLable column is fast too). This results in a plan with one clustered index scan, a top operator (for halloween protection), a compute scalar (for the maths), and a clustered index update. It doesn't get much better than that. On my (2GHz single-core Pentium M) laptop, that scenario completes in just 4,348 logical reads and 2,391ms (with a warm cache).

I agree... that's where I was going to go with this but wanted to meet Adam's requirements. There was some really cool things I was going to show about the Clustered Index but guess folks will just have to wait for the "book" now. 😛

Quite shocking to see a cursor authored by Mr RBAR though, even if it was for demonstration purposes. 😉

Yowch! Couldn't agree more. I think I actually pulled a muscle writing that (ugh!) cursor. 😛

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

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 4

Lynn Pettis (6/4/2009)
Oh, please tell me exactly what I need to do. I have NO IDEA how to look things up in BOL.

Was that Mr. Echo from before?

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

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

Adam Machanic (6/4/2009)
OK, that's a pretty good case. I'll have to revisit my own example and try to figure out why I'm not seeing such an extreme difference. By the way, when I run your example on different servers, the perceived difference changes dramatically; on one brand new server, with loads of RAM and CPU power, the cursor is only twice as slow. On my desktop machine, the UPDATE runs in 12 seconds and I killed the cursor after 10 minutes.

Heh. I agree. It does vary quite a bit as do Tally table splits. Just a casual observation... The key words are "the cursor is only twice as slow".

I was able to improve the cursor in your example by doing a few things, like making sure to include the Amount column in the clustered index on CheckBook, and creating the index on the temp table first, rather than afterward (the data is inserted in that order anyway, so fragmentation shouldn't be an issue). I expected that wrapping the whole thing in a transaction would further speed it up, but I was surprised to see that it made no real difference.

I was actually going to post several renditions of both methods but the post was already real long and it was getting late. I absolutely agree that forming the clustered index prior to cursor population in this case is the right way to go. If folks don't trust the Pseudo-Cursor UPDATE method, then squeeze all the performance out of the cursor that you can and that's a great way to do it.

With regard to the UPDATE, something you might want to play with instead of relying on the hints is using a CTE and forcing the data to be ordered. This will not actually cause a sort to occur unless you don't have a supporting index. And I did test to make sure that if you do not have a supporting index, a sort WILL in fact occur. This should give you a better guarantee of ordered processing than the INDEX and TABLOCKX hints.

I believe that you'll find that the index hint is absolutely NOT necessary. In fact, the only reason I really include it is to help prevent the handrail problem. 😛

To wit, I agree that the ordered CTE works in the absence of a clustered index but you might want to try the ordered CTE in the presence of an clustered index that ISN'T in the correct order for the running total update.

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

Gaby Abed (6/4/2009)
Lynn Pettis (6/4/2009)
Oh, please tell me exactly what I need to do. I have NO IDEA how to look things up in BOL.
Was that Mr. Echo from before?

Actually, No. But it felt appropriate plus I was having a problem posting some code on another thread it served as a test if I was having problems with posting in general (which I didn't).

Adam Machanic SSCoach Points: 15370 More actions · Answer 7

Jeff Moden (6/4/2009)
To wit, I agree that the ordered CTE works in the absence of a clustered index but you might want to try the ordered CTE in the presence of an clustered index that ISN'T in the correct order for the running total update.

I already did. Did you? 🙂

--
Adam Machanic
whoisactive

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

Lynn Pettis (6/4/2009)
Gaby Abed (6/4/2009)
Lynn Pettis (6/4/2009)
Oh, please tell me exactly what I need to do. I have NO IDEA how to look things up in BOL.
Was that Mr. Echo from before?
Actually, No. But it felt appropriate plus I was having a problem posting some code on another thread it served as a test if I was having problems with posting in general (which I didn't).

Then he is apologizing saying he didn't see my post. Hmmm, almost 20 minutes from my post to his ...

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

Lynn Pettis (6/4/2009)
Oh, please tell me exactly what I need to do. I have NO IDEA how to look things up in BOL.

Heh... if you really want to have some fun with him, tell him there are no "fields" in databases. 😛

--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: 1004356 More actions · Answer 10

Adam Machanic (6/4/2009)
Jeff Moden (6/4/2009)
To wit, I agree that the ordered CTE works in the absence of a clustered index but you might want to try the ordered CTE in the presence of an clustered index that ISN'T in the correct order for the running total update.
I already did. Did you? 🙂

No. I've only tried it with a derived table which seems to always follow the clustered index even if it's wrong.

I've not tried the CTE version of that derived table. Thank you for the tip. I'll play with it tonight after work.

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 11

WayneS (6/4/2009)
Hey friends, please help me out here. I'd really like to have an understanding of why this changed.
Thanks!

You can get points quickly if you ask questions here instead of using BOL.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

Alvin Ramard (6/4/2009)
WayneS (6/4/2009)
Hey friends, please help me out here. I'd really like to have an understanding of why this changed.
Thanks!
You can get points quickly if you ask questions here instead of using BOL.

Hey Alvin! Is this really the quote you wanted?? 😉

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Gianluca Sartori (6/4/2009)
Jeff, has your avatar something in common with this?
http://www.youtube.com/watch?v=vF4iWIE77Ts
It's incredible I didn't notice it before!!

That's the 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)

Adam Machanic SSCoach Points: 15370 More actions · Answer 14

Jeff Moden (6/4/2009)
No. I've only tried it with a derived table which seems to always follow the clustered index even if it's wrong.
I've not tried the CTE version of that derived table. Thank you for the tip. I'll play with it tonight after work.

Should work with either a derived table or CTE (although I prefer the latter for readability) -- the key is making sure you use TOP(number), not TOP 100 PERCENT. A change in the optimizer in SQL Server 2005 is that it will optimize TOP 100 PERCENT right out of derived tables, CTEs, or views. The same cannot be done for TOP(number), because the optimizer doesn't know how many rows there might be. So I use TOP(2147483647). I assume you don't need to do this with more than 2 billion rows!

A bit more information here:

http://sqlblog.com/blogs/adam_machanic/archive/2006/10/03/exploring-the-secrets-of-intermediate-materialization.aspx%5B/url%5D

--
Adam Machanic
whoisactive

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

RBarryYoung (6/4/2009)
Hey, Jeff is back! 😀 Great stuff Jeff.

Thanks for the pick-me-up Barry! I was Jonesing for some good SSC. Have been pretty well distracted lately.

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