Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 6,031 through 6,045 (of 66,712 total)

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

Chad Crawford SSChampion Points: 11610 More actions · Answer 1

Alvin Ramard (6/24/2009)
jcrawf02 (6/24/2009)
Yes, I know they're zeroes. That's to counteract the average being too high when the code isn't present in the utilization at all.
Anyway, the zeroes are in both methods, so that shouldn't be the problem. (says the guy who can't figure it out):-D
Is this a case of the "average of averages" not being equal to the "average of the total"? You should never assume that those to be equal.

I didn't quite see how you were calculating the utilperk (I couldn’t reproduce it), but I think Alvin nailed your issue.

When you divide the util by membership, you "equalize" the difference in membership in every month - a month with units of 20 and membership of 100 has equal weight in the final average with a month with units of 800,000 and membership of 1,000,000. Both are reduced to a single percentage (e.g. 20% and 80%) then averaged (50%). It allows you to compare months (80 is higher than 20), but ignores the difference in membership.

When you take the raw numbers and average them all together, you are "recognizing" the weight of membership, and "removing" the weight of the month - in this case, (800,000+20)/(1,000,000 + 100) = 79.9994%. The difference in membership is recognized, but the months are not equally weighted.

Which way you do it depends on what you want. Not what result you want (that's not fair and it's how statistics gets a bad rap), but what you are trying to evaluate. The first method gives you the average percentage per month, which you can use to predict future month trends and see if any particular month was significantly higher or lower than the trend. The second method gives you a raw number for the entire period, not accounting for (or “removing”, I think you could say) the month from the equation to give you an average for the whole period. It gives you an overall view, but can't be used to compare month-to-month, because one month with particularly high (or low) membership is given too little (or to much) weight.

Thanks,

Chad

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

Chad Crawford (6/24/2009)
Alvin Ramard (6/24/2009)[hrIs this a case of the "average of averages" not being equal to the "average of the total"? You should never assume that those to be equal.
The first method gives you the average percentage per month, which you can use to predict future month trends and see if any particular month was significantly higher or lower than the trend. The second method gives you a raw number for the entire period, not accounting for (or “removing”, I think you could say) the month from the equation to give you an average for the whole period. It gives you an overall view, but can't be used to compare month-to-month, because one month with particularly high (or low) membership is given too little (or to much) weight.
Thanks,
Chad

Thanks, Alvin and (distant cousin) Chad.

Duh.

Can't see the forest for the idiot. Not sure why I thought the second method removed the weighted impact of the membership also.

Appreciate your assistance!

Jon

p.s. Chad, if you haven't found it before, check out http://www.clancrawford.org/, lots of good info. Haven't figured out whether it's true or not, but family legend says that I'm descended from Colonel William Crawford, who was burned at the stake here in Ohio (see the link for notable members). Enjoy!

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

Does anyone have an idea why row_number() in a trigger does not appear to work ? The exact same code in a batch works fine (substituting a temporary table for the inserted table ) . See http://www.sqlservercentral.com/Forums/Topic741052-338-1.aspx

SQL = Scarcely Qualifies as a Language

LutzM SSC Guru Points: 107049 More actions · Answer 4

Hi folks,

would someone with execution plan background mind to take a look at this post and verify if I'm on the right track or misguiding the OP?

The more interesting part of it is the performance comparison of PIVOT vs. MAX(CASE...). But I think the rough time measurement is not really useful since to me it looks like there are missing indexes.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Carl Federl (6/24/2009)

Does anyone have an idea why row_number() in a trigger does not appear to work ? The exact same code in a batch works fine (substituting a temporary table for the inserted table ) . See http://www.sqlservercentral.com/Forums/Topic741052-338-1.aspx%5B/quote%5D

The posted script works fine for me on both 2005 (9.0.4211 Dev) and 2008 (10.0.2531 Dev).

The exact code I ran, and the output follows.

CREATE TABLE dbo.cnsmr_accnt (

[cnsmr_id] VARCHAR (50) NOT NULL,

[cnsmr_accnt_id] VARCHAR (50) NOT NULL ,

[case_feed] VARCHAR (10) NOT NULL

)

GO

CREATE TRIGGER [dbo].[trig_test_insert]

ON [dbo].[cnsmr_accnt]

INSTEAD OF INSERT

AS

SET NOCOUNT ON

IF 0 = (SELECT COUNT(*) FROM inserted ) RETURN

DECLARE @case_feed VARCHAR (10)

SET @case_feed = '7'

-- case_feed not '7' union case_feed is '7'

INSERT INTO dbo.cnsmr_accnt

(cnsmr_id , cnsmr_accnt_id , case_feed )

SELECT inserted.cnsmr_id + '-'

+ CAST( 100 + ROW_NUMBER() OVER( ORDER BY inserted.cnsmr_id ) AS VARCHAR(4) )

, inserted.cnsmr_accnt_id

, inserted.case_feed

FROM inserted

WHERE inserted.case_feed = @case_feed

INSERT INTO dbo.cnsmr_accnt

(cnsmr_id , cnsmr_accnt_id , case_feed )

SELECT inserted.cnsmr_id

, inserted.cnsmr_accnt_id

, inserted.case_feed

FROM inserted

WHERE inserted.case_feed @case_feed

GO

BEGIN TRAN

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('12', '45683','7')

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('13', '45684','7')

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('14', '45685','3')

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('15', '45686','7')

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('16', '45687','2')

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('17', '45688','7')

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('18', '45689','6')

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('19', '45690','7')

INSERT INTO cnsmr_accnt (cnsmr_id , cnsmr_accnt_id , case_feed) VALUES ('20', '45691','7')

SELECT * FROM dbo.cnsmr_accnt

ROLLBACK

[font="Courier New"]cnsmr_idcnsmr_accnt_idcase_feed

12-101456837

13-101456847

14456853

15-101456867

16456872

17-101456887

18456896

19-101456907

20-101456917

[/font]

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

Paul, did not see this post until after I replied but we are getting the same result. I am going to start a new post thread.

SQL = Scarcely Qualifies as a Language

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

Carl Federl (6/24/2009)
Paul, did not see this post until after I replied but we are getting the same result. I am going to start a new post thread.

Cool. So are you saying that isn't the expected output? Could you post a link to your new thread here so I don't miss it?

Thanks

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Carl,

So I've read the thread a little more thoroughly now and hacked together something that appears to work.

See the original thread.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 9

For those that use the FireFox web browser (you can stop reading now Jeff:-)) - what do you use for blocking ads? The one I used wrecked havoc on this web site.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

WayneS (6/24/2009)
For those that use the FireFox web browser (you can stop reading now Jeff:-)) - what do you use for blocking ads? The one I used wrecked havoc on this web site.

The standard popup blocker is all I use. I've never had a need for an actual Ad Blocker. I have a much better solution for any site whose ad's are so prevalent or annoying that I would want to block them... 🙂

[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 11

HA! So have you all seen the featured article[/url] today?

I think the stand out phrase for me is "I will be using the power of XML" :w00t:

Reminds me of:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Hey Paul, did you ever get a chance to decipher my compressed SQL? 🙂

[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 13

RBarryYoung (6/24/2009)
Hey Paul, did you ever get a chance to decipher my compressed SQL? 🙂

No more than running it so far - Real Life got partly in the way last evening, then I forgot and ended up posting far too much on the forums. I am paying for that today in my inbox (notifications). Hopefully tonight I'll get chance, but definitely over the weekend if not - I want to have a proper look at it rather than a rushed half-hour 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Cool. No rush, just curious... 😉

[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]

spaghettidba SSC Guru Points: 105732 More actions · Answer 15

WayneS (6/24/2009)
Gianluca Sartori (6/23/2009)
WayneS (6/23/2009)
Well, I let him know a solution is available, but... well, see for yourself... http://www.sqlservercentral.com/Forums/FindPost740549.aspx
LOL! What's your hourly fee, Wayne? I need to get the grass cut... 😀
How's that saying go? If you have to ask, you can't afford it...

Maybe I could, but the main problem is that it's always raining... The grass grew almost to my knees...

I don't' remember such a rainy and (relatively) cold summer start. 18° C in June, in Italy? Never seen this before.

-- Gianluca Sartori