Are the posted questions getting worse?

Question

Post reply

Are the posted questions getting worse?

Viewing 15 posts - 5,311 through 5,325 (of 66,000 total)

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

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

Heh... in case anyone is wondering if I simply disappeared on this, the answer is "No". It's just that the freakin' cursor is taking me so long to write. I have to wash my mouth out every 5 minutes because I can't stand the taste. 😛

I'll be back with the typical million row solution shortly.... hang in there.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Back in a sec... virus software update kicked in. Wouldn't make for a fair test.

--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)
Intro to Tally Tables and Functions

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

The following code creates a million row multiple account checkbook, does the "Fire Hose" cursor running total thing 5 times and follows that with the "Pseudo Cursor" method 5 times. After that, the graphic I captured from that code running on Profiler on my poor old 7 year old P4 1.8GHz 1GB Ram desktop computer running 2k5 DE sp2. As usual, the details are in the embedded comments in the code.

[font="Courier New"]--===== Create the test table and the necessary indexes

--===== Do this in a nice safe place

USE TempDB

--===== Create a "check book" table with a ratio of deposits

-- to withdrawals and constraints on the amounts for both.

-- This time, we include transaction dates and we don't

-- care about the order of the transactions. We also

-- include different account numbers.

-- (All Versions)

--==============================================================================

--===== Conditionally drop the test table

-- (so we can rerun the test code as needed)

IF OBJECT_ID('TempDB..CheckBook') IS NOT NULL

DROP TABLE CheckBook

--===== Declare some obviously named variables

DECLARE @StartDate DATETIME,

@EndDate DATETIME, --Upto and NOT including this date

@Days INT, --This will be the "range"

@Counter INT --Used in the While Loop to make page splits

--===== Preset the start date, end date, and calculate the range

-- in days. Since we don't want the end date to be

-- included, we DIDN'T add 1 to the range.

SELECT @StartDate = '01 Jan 2000',

@EndDate = '01 Jan 2010',

@Days = DATEDIFF(dd,@StartDate,@EndDate)

--===== Create and populate the table on the fly.

-- Produces approximately 10,000 rows for each of 1,000 accounts

SELECT TOP 1000000

IDENTITY(INT,1,1) AS CheckBookID,

ABS(CHECKSUM(NEWID()))%1000+1 AS AccountNumber, --1000 accounts

CAST(--= Roughly every tenth row (avg) will be a deposit

CASE ABS(CHECKSUM(NEWID())) % 10 -- 0-9 (10 digits)

WHEN 0 --Occurs 1 out of ten times on average

THEN RAND(CHECKSUM(NEWID())) * 500 + 1000 --Deposits

ELSE RAND(CHECKSUM(NEWID())) * (-100) --Withdrawals

END

AS DECIMAL (9,2)) AS Amount,

ABS(CHECKSUM(NEWID())) % @Days + @StartDate --Random DateTime

AS TransactionDate

INTO dbo.CheckBook

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2

--===== Create the initial deposit(s) for each account.

-- Everybody gets at least 1 and maybe more on the

-- first day (all first day trans are deposits)

UPDATE CheckBook

SET Amount = RAND(CHECKSUM(NEWID())) * 500 + 1000

FROM CheckBook tt

INNER JOIN

(--==== Find the initial date for each account

SELECT AccountNumber,

MIN(TransactionDate) AS MinTransactionDate

FROM CheckBook

GROUP BY AccountNumber

) firstdate

ON tt.AccountNumber = firstdate.AccountNumber

AND tt.TransactionDate = firstdate.MinTransactionDate

--===== Create the PK like many folks would do it to prevent

-- fragging the table on inserts. Pretty tough on SELECT's, though

ALTER TABLE dbo.CheckBook

ADD CONSTRAINT PK_CheckBook_CheckBookID

PRIMARY KEY CLUSTERED (CheckBookID)

WITH FILLFACTOR = 100 --Actually, this is the default on most systems

--===== Add a nonclustered index to support SELECTs and (ugh!) Cursors

-- that have an ORDER BY

CREATE NONCLUSTERED INDEX IX_CheckBook_AccountNumber_TransactionDate_CheckBookID

ON dbo.CheckBook (AccountNumber, TransactionDate, CheckBookID)

WITH FILLFACTOR = 90

--===== Let's see what the distribution of rows is for each account

-- and what the total number of rows is.

-- This will also see if the index is up to snuff.

SELECT CASE

WHEN GROUPING(cb.AccountNumber) = 0

THEN CAST(cb.AccountNumber AS CHAR(5))

ELSE 'Total'

END AS AccountNumber,

COUNT(*) AS NumberOfRows

FROM dbo.CheckBook cb

GROUP BY cb.AccountNumber WITH ROLLUP

ORDER BY cb.AccountNumber

GO

--==============================================================================

GO 2

--The "Fire Hose" Cursor Running Total Solution

--===============================================================================

--===== Supress the auto-display of rowcounts or the cursor will be even slower.

SET NOCOUNT ON

--===== Create the necessary Temp table to store the result set so we can use

-- the result set as a table if we need to. This will be used for the

-- cursor run. The Psuedo-Cursor run will build one on the fly.

-- The condition drop is just for reruns. It wouldn't be needed

-- in production but it sure helps troubleshooting.

IF OBJECT_ID('TempDB..#cWork','U') IS NOT NULL

DROP TABLE #cWork

--===== Create the cursor result set table using the "zero row" trick

SELECT CAST(CheckBookID AS INT) AS CheckBookID, --Strips of IDENTITY

AccountNumber,

Amount,

TransactionDate,

CAST(0 AS DECIMAL(9,2)) AS AccountBalance

INTO #cWork

FROM dbo.CheckBook

WHERE 1 = 0 --We just want table structure, no rows

--===== Declare local variables for the cursor run

DECLARE @CheckBookID INT,

@AccountNumber INT,

@Amount DECIMAL(9,2),

@TransactionDate DATETIME,

@CurBalance DECIMAL(9,2),

@PrevBalance DECIMAL(9,2),

@PrevAccount INT

--===== Preset the necessary variables

SELECT @CurBalance = 0,

@PrevBalance = 0,

@PrevAccount = 0

--===== Declare and open a cursor

--Haven't confirmed it, but rumor has it that FAST_FORWARD

--isn't quite as fast as ORWARD_ONLY READ_ONLY

DECLARE curRunningTotal CURSOR LOCAL FORWARD_ONLY READ_ONLY

FOR SELECT CheckBookID,

AccountNumber,

Amount,

TransactionDate

FROM dbo.CheckBook WITH(NOLOCK) --Just to help speed on this test

ORDER BY AccountNumber, TransactionDate, CheckBookID

--===== Open the Cursor and Fetch the first row (Lordy I hate saying that!! ;-))

OPEN curRunningTotal

FETCH NEXT FROM curRunningTotal

INTO @CheckBookID,

@AccountNumber,

@Amount,

@TransactionDate

--===== Now, loop through the cursor and insert the rows into

-- the work table including calculations for the

-- running total

WHILE @@FETCH_STATUS = 0

BEGIN

--===== Do the necessary math for balances that restart

-- when the AccountNumber changes. The also preps

-- the @Prev* variables for the next record.

SELECT @CurBalance =

CASE

WHEN @AccountNumber = @PrevAccount

THEN @PrevBalance + @Amount

ELSE @Amount

END,

@PrevBalance = @CurBalance,

@PrevAccount = @AccountNumber

--===== Insert those calculations as a new row in the result set

INSERT INTO #cWork

(

CheckBookID,

AccountNumber,

Amount,

TransactionDate,

AccountBalance

)

SELECT @CheckBookID AS CheckBookID,

@AccountNumber AS AccountNumber,

@Amount AS Amount,

@TransactionDate AS TransactionDate,

@CurBalance AS AccountBalance

--===== Get the next record (I really hate this ;-))

FETCH NEXT FROM curRunningTotal

INTO @CheckBookID,

@AccountNumber,

@Amount,

@TransactionDate

END --Loop back for the next record.

--======== Clean up the mess from the cursor

CLOSE curRunningTotal

DEALLOCATE curRunningTotal

--===== Add a clustered index to the #cWork table to support SELECT's

CREATE CLUSTERED INDEX IX_#cWork_AccountNumber_TransactionDate_CheckBookID

ON #cWork (AccountNumber, TransactionDate, CheckBookID)

WITH FILLFACTOR = 100

--===== Select the first 2 accounts just to wake us up that it actually finished

-- and so it's easier to see that it actually worked when the account

-- number changed.

SELECT *

FROM #cWork

WHERE AccountNumber <= 2

ORDER BY AccountNumber, TransactionDate

GO 5

--==============================================================================

GO 2

--The "Pseudo-Cursor" Running Total Solution ("Quirky" Update as Phil calls it)

--==============================================================================

--===== Turn the auto-display of rowcounts back on just to give the cursor a

-- chance. ;-)

SET NOCOUNT OFF

--===== Create the necessary Temp table to store the result set so we can use

-- the result set as a table if we need to. The Psuedo-Cursor run will

-- build and populate it on the fly.

-- The condition drop is just for reruns. It wouldn't be needed

-- in production but it sure helps troubleshooting.

IF OBJECT_ID('TempDB..#pcWork','U') IS NOT NULL

DROP TABLE #pcWork

--===== Create the cursor result set table using the "zero row" trick

SELECT CAST(CheckBookID AS INT) AS CheckBookID, --Strips off IDENTITY

AccountNumber,

Amount,

TransactionDate,

CAST(0 AS DECIMAL(9,2)) AS AccountBalance --Creates new column

INTO #pcWork

FROM dbo.CheckBook

--===== Add a clustered index to the #psWork table to support SELECT's.

-- This is also the key to the Pseudo-Cursor or "quirky" update

-- method for doing running totals.

CREATE CLUSTERED INDEX IX_#pcWork_AccountNumber_TransactionDate_CheckBookID

ON #pcWork (AccountNumber, TransactionDate, CheckBookID)

WITH FILLFACTOR = 100

--===== Declare local variables for the cursor run

DECLARE @PrevBalance DECIMAL(9,2),

@PrevAccount INT

--===== Preset the necessary variables

SELECT @PrevBalance = 0,

@PrevAccount = 0

--===== Let's rock. Do the running balance using a single

-- update as a Pseudo-Cursor.

UPDATE #pcWork

SET @PrevBalance = AccountBalance = CASE

WHEN AccountNumber = @PrevAccount

THEN @PrevBalance + Amount

ELSE Amount

END,

@PrevAccount = AccountNumber

FROM #pcWork WITH(INDEX(0),TABLOCKX) --Warm fuzzy options

OPTION (MAXDOP 1) --Just to be absolutely safe

--===== Select the first 2 accounts to do the same as the cursor

-- and so it's easier to see that it actually worked when the account

-- number changed.

SELECT *

FROM #pcWork

WHERE AccountNumber <= 2

ORDER BY AccountNumber, TransactionDate

GO 5[/font]

Here's the performance stats from the profiler run I setup to look at the single spid this all ran on...

... and, here's the code that proves they both came up with the same running balances.

[font="Courier New"] SELECT c.*,pc.*

FROM #cWork c

INNER JOIN #pcWork pc

ON c.CheckBookID = pc.CheckBookID

AND c.AccountBalance <> pc.AccountBalance

ORDER BY pc.AccountNumber, pc.TransactionDate[/font]

Like Adam said, I do use these for speed. 😉

--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)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

RBarryYoung

SSC Guru

Points: 143329

June 4, 2009 at 2:50 am

#1004473

Hey, Jeff is back! 😀 Great stuff Jeff.

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Lynn Pettis (5/28/2009)

Okay! Any one in Germany near Frankfurt? Maybe has a teen age (around 18 year old) son or daughter (preferably)?

And yes I am serious. I have a teenage daughter who paid for her own trip to Germany and is currently not having a very good time like she had hoped.

Lynn, if you can get her to London - flights from Frankfurt to London City used to be quite cheap - I'll send my 18-year old daughter down from Manchester. My flat in Leyton is empty at the mo, they could share and explore London together.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Hey Jeff,

Really neat demonstration. Love it. Lots of cool optimizations too.

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

BTW: I use INDEX(1) instead of (INDEX(0)) for the in-place update to ensure I get a linked-list ordered scan (to avoid a sort) rather than the IAM scan that happens with TABLOCK or at READ UNCOMMITTED. For sure, that's not an issue for your script - I think you even optimized for the IAM scan knowing that the table was pretty much guaranteed to be in physical order. Nice one.

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

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95405 More actions · Answer 7

Paul White (6/3/2009)

One thing to mention is that the article is the first of a series

Well, actually it's the second in a series.

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: 95405 More actions · Answer 8

Jeff Moden (6/3/2009)

The following code creates a million row multiple account checkbook, does the "Fire Hose" cursor running total thing 5 times and follows that with the "Pseudo Cursor" method 5 times. After that, the graphic I captured from that code running on Profiler on my poor old 7 year old P4 1.8GHz 1GB Ram desktop computer running 2k5 DE sp2. As usual, the details are in the embedded comments in the code.

Jeff, I have a (simple) question. Is this the million-row test you were whipping up for Adam, or your approach for Itzik's article --- or were you able to handle both of these in one?

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Paul White (6/4/2009)

BTW: I use INDEX(1) instead of (INDEX(0)) for the in-place update to ensure I get a linked-list ordered scan (to avoid a sort) rather than the IAM scan that happens with TABLOCK or at READ UNCOMMITTED. For sure, that's not an issue for your script - I think you even optimized for the IAM scan knowing that the table was pretty much guaranteed to be in physical order. Nice one.

Jeff's technique depends on not getting an IAM scan, not having the data fetched in any order than that of the clustering key (index order scan) and not having the optimiser use any order-mangling operations. If any of those happen, the 'quirky' update can give incorrect values.

That's why the restrictions are no partitioning, no parallelism.

I asked about the IAM scan a while back and apparently it will not happen on updates. Sorry, no public blog I can refer you to, was a conversation with someone on the optimiser team.

Way to tell that it's index order, not allocation order - check the order property of the clustered index scan in the execution plan. Ordered = No is an allocation order scan

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150457 More actions · Answer 10

GilaMonster (6/4/2009)

Jeff's technique depends on not getting an IAM scan, not having the data fetched in any order than that of the clustering key (index order scan) and not having the optimiser use any order-mangling operations. If any of those happen, the 'quirky' update can give incorrect values.

That's why the restrictions are no partitioning, no parallelism.

I asked about the IAM scan a while back and apparently it will not happen on updates. Sorry, no public blog I can refer you to, was a conversation with someone on the optimiser team.

Way to tell that it's index order, not allocation order - check the order property of the clustered index scan in the execution plan. Ordered = No is an allocation order scan

Interesting - knew all that except the no IAM scan in an update plan. Seems odd because I get that sort in my UPDATE plan with an INDEX(0) hint, but no sort with INDEX(1).

Perhaps someone forgot to tell the QO about that behaviour.

I thought TABLOCK/NOLOCK = IAM scan? I wonder why Jeff's hints are as they are then?

BTW - I don't trust the Ordered = No thing, since I read http://www.sqlmag.com/Article/ArticleID/92887/sql_server_92887.html

There's an explicit example there of ordered:false doing a logical order scan.

edit: pesky word 'not' crept in there

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

Paul White (6/4/2009)

Interesting - knew all that except the no IAM scan in an update plan.

Think it was that. I know it was a discussion on 'ordering' in updates related to exactly this query and why it seems to work. (in case you haven't figured it out, I don't like this query form and I do have my reasons).

Can't find the conversation, can't completely recall details (was about 8 or so months ago) and even if I could find the conversation, I wouldn't be able to post it here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 12

Jeff, after you install the hand rails, you'll also need one of these:

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

Paul White (6/4/2009)

Way to tell that it's index order, not allocation order - check the order property of the clustered index scan in the execution plan. Ordered = No is an allocation order scan

Interesting - knew all that except the no IAM scan in an update plan. Seems odd because I get that sort in my UPDATE plan with an INDEX(0) hint, but no sort with INDEX(1).

Perhaps someone forgot to tell the QO about that behaviour.

I thought TABLOCK/NOLOCK = IAM scan? I wonder why Jeff's hints are as they are then?

BTW - I don't trust the Ordered = No thing, since I read http://www.sqlmag.com/Article/ArticleID/92887/sql_server_92887.html

There's an explicit example there of ordered:false doing a logical order scan.

edit: pesky word 'not' crept in there

Here's the reason why I use Index(0) instead of Index(1). From BOL:

If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek.

It's overkill, but it makes people feel better about using the method. Just don't try it with any non-clustered index because they can go "merry-go-round" on you... heh... well, almost never. I'm still doing some experiments on that.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Grant Fritchey (6/4/2009)

Jeff, after you install the hand rails, you'll also need one of these:

BWAA-HAAA!!! Thanks for the help. Looks like I'm gonna need the hand rails after all, huh? 😛

--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)
Intro to Tally Tables and Functions

spaghettidba SSC Guru Points: 105712 More actions · Answer 15

spaghettidba

SSC Guru

Points: 105712

June 4, 2009 at 6:31 am

#1004575

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

-- Gianluca Sartori