Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 4,231 through 4,245 (of 66,712 total)

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

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 1

RBarryYoung (5/10/2009)
Not as I recall. My recollection is that we came up with an alternate way to do it.

Other than using Maxdop, I don't remember coming up with an alternative. Heh, of course, I sometimes can't remember where the heck I put my glasses, either. 🙂 I'll start looking for both... :hehe:

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

RBarryYoung (5/9/2009)
First and foremost, of course is the string-limit of 8K in 2005. And as noted previously, although Adam Machanic's book does claim to have a workaround for this, I have never seen an actual working (non-crashing) example that used it.

Are you saying that your tried the code in my book and it crashed? That would be the first report I've received from anyone that it didn't work fine. Care to elaborate on exactly what you did and how you tested? I would hate to "claim to have a workaround" that doesn't work.

--
Adam Machanic
whoisactive

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

Jeff Moden (5/10/2009)
RBarryYoung (5/10/2009)
Not as I recall. My recollection is that we came up with an alternate way to do it.
Other than using Maxdop, I don't remember coming up with an alternative. Heh, of course, I sometimes can't remember where the heck I put my glasses, either. 🙂 I'll start looking for both... :hehe:

I haven't been able to find the post you're talking about yet, Barry. I did, however, find another of Gail's posts where she said the method only concatenated one row instead of all the rows. Heh... and in the true spirit of cooperation (thus, answering my own quest for faulty code to prove the claim), I also was able to make a bit of code where that was true using an odd method of performing the string aggregation...

[font="Courier New"] USE Northwind

DECLARE @MAV VARCHAR(8000)

--===== This odd method only processes one row and COALESCE is to blame

SELECT @MAV = COALESCE(@MAV+', '+ TerritoryDescription, TerritoryDescription)

FROM dbo.Territories

ORDER BY TerritoryDescription

SELECT @MAV

SELECT @MAV = NULL

--===== Simply changing COALESCE to ISNULL fixes the problem

SELECT @MAV = ISNULL(@MAV+', '+ TerritoryDescription, TerritoryDescription)

FROM dbo.Territories

ORDER BY TerritoryDescription

SELECT @MAV

SELECT @MAV = NULL

--===== Using the more classic form of string aggregation fixes it, too.

SELECT @MAV = COALESCE(@MAV+', ', '') + TerritoryDescription

FROM dbo.Territories

ORDER BY TerritoryDescription

SELECT @MAV

SELECT @MAV = NULL

[/font]

We can now badmouth the method (but, only a little) because we have code to prove it. 😛 We also have the code to fix the problem. :hehe:

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Adam Machanic (5/10/2009)
RBarryYoung (5/9/2009)
First and foremost, of course is the string-limit of 8K in 2005. And as noted previously, although Adam Machanic's book does claim to have a workaround for this, I have never seen an actual working (non-crashing) example that used it.
Are you saying that your tried the code in my book and it crashed? That would be the first report I've received from anyone that it didn't work fine. Care to elaborate on exactly what you did and how you tested? I would hate to "claim to have a workaround" that doesn't work.

"Crashed" meaning the query failed, throwing an ugly error anytime it went parallel. (a couple of months ago, don't remember the exact error)

As I said before, the solution was described in parts, so I may have put it together wrong or introduced a bug when I translated it to VB. In any event, I could not figure out how to get it to work, other than by suppressing parallel execution.

I can post the code here if you want or send me your Email (mine is in my profile here), and I will Email it to you.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 5

And there was I trying to be all gentle and non-controversial about the MAV thing.

Way to go Jeff! It was like reading my own thoughts - just more eloquent and with coloured code. 😀

So yeah, what he said. 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

GSquared SSC Guru Points: 260824 More actions · Answer 6

Jeff Moden (5/10/2009)
Paul White (5/10/2009)
Given the limitations of FOR XML, I even prefer Paul Nielsen's approach! At least it doesn't die horribly if the wrong character is found in the input - whatever its other faults.
I wonder what the heck Paul Nielsen is actually talking about. ORDER BY has worked correctly for MAV's for a very long time. I don't have SQL Server 6.5 or 7 to prove it anymore, but here's the code for 2k..
[font="Courier New"]DECLARE @MAV VARCHAR(8000)
--===== Sort by description
SELECT @MAV = ISNULL(@MAV+', ','') + RTRIM(TerritoryDescription)
   FROM dbo.Territories
  ORDER BY TerritoryDescription
SELECT @MAV
SELECT @MAV = NULL
--===== Sort by ID
SELECT @MAV = ISNULL(@MAV+', ','') + RTRIM(TerritoryDescription)
   FROM dbo.Territories
  ORDER BY TerritoryID
SELECT @MAV[/font]

Jeff: I don't have the code any more, but I've seen the Order By fail in 2k. Was one of the databases I was working on two years ago. When I first learned that trick, I tried it on a bunch of things, and in a few cases, Order By ended up just getting the final value, as if it didn't have the coalesce and all that. Took the Order By out, and it worked. Was very consistent. Could add it in and just get the last result, take it out and get the unordered concatenated string, back and forth.

Unfortunately, I don't have the details available any more. That server hasn't been online since the company that owned it went out of business in '07.

Same code worked just fine in 2k5, no error there. Just had the odd behavior in 2k.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Hmm, I seem to recall that this issue in 2000 was if the ORDER BY contained an expression, instead of just a column.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 8

Technical info in THE THREAD!?!

tsk, tsk

That's for real threads. 😉

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

Steve Jones - Editor (5/11/2009)
Technical info in THE THREAD!?!
tsk, tsk
That's for real threads. 😉

Thanks Steve.

😛

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]

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

I was starting to get a bit paranoid about the well being of THE THREAD. But hopefully that will change now.. 😛

-Roy

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

I can't believe that this thread is still around and kicking.

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

Jonathan Kehayias (5/11/2009)
I can't believe that this thread is still around and kicking.

It's gonna be around for a long time yet .....

Steve, what datatype is used to for the number of pages and piosts? Int or BigInt?

😀

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]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

Thanks, Steve.

I agree, Roy.

You are an unbeliever, Johnathan?

Glad to see you're thinking ahead, Alvin.

Hey Lynn, do you think we'll make 5K this month?

P.S. Did anybody else see the Star Trek movie over the weekend? I thought it was great.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

I wanted to see it but couldnt. Maybe this week. I love Star Trek.. 🙂

-Roy

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 15

I think this thread will never die.

The data type is an int, and I hope we don't approach that limit!

didn't see Star Trek. Was going to Sat, but my wife got delayed with stuff and my back was sore, not sure I could sit in those seats for 2+ hours.