INSERT A Record Based on Count

Question

INSERT A Record Based on Count

Viewing 15 posts - 16 through 30 (of 60 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Jason,

I achieved the same results as you, but they seemed skewed. I dug a little deeper and realized that you created a cluster index on your tally table, which increased performance. I removed the clustered index and your script esitmated 99.1703 subtree cost; however, your script took 2 seconds to run and the CTE took 22.

The tally script spends all of it time in a nested join. I bumped up the record count in the tally table to the same 1,000,000 and got the execution time to about 7 seconds. But the weird thing is the subtree cost is now 681179 and the cpu and operator cost are equally as high, yet it finishes consistently better than the CTE.

I did notice on the CTE the index spool was at like 82% operator cost, 61.4885 subtree cost and had an astounding 548,096 acutal rebinds. I think this is where the CTE bottlenecks.

-Adam

My blog: http://jahaines.blogspot.com

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

Actually, after you asked, I did a bit more testing and I was completely backwards in my statement.
Using the testing code below I got a 3% to 97% comparison.
Where the first code ( Mine ) has a subtree cost of 2.21972 and the CTE version is 68.377.

Guess I gotta say, thanks for the trust in using the Tally table in the first place... well done, Jason.

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

surendra_thokala Newbie Points: 3 More actions · Answer 3

1)with out Using NVL function display the Commission from emp table along with a Message NA,if the commission is NULL

2) Displaying Employee having Same salary

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

Is this a separate request or added requirements to what has been done already?

... and NVL is ORACLE... the equivelant in SQL Server is ISNULL... but why would you not want to use it here?

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

PW-201837

SSC-Insane

Points: 20805

November 12, 2007 at 3:07 pm

#750396

[Edit] Nevermind.

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Well Jeff, it looks like you've got another comparison between the new 2005 T-SQL options and good 'ol fashioned set based operations. Here's another great reason not to jump right in with using the new 2005 stuff without thinking hard and gathering performance metrics first.

By the way, how are you guys getting your code snippets to appear in your posts with scroll bars like that?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Using the "code" and "/code" tags (you need to include square brackets around those words instead of the double quotes... ). Still sucks for the end user, though, because instead of using new line or carriage returns, it uses vertical tabs which must be replaced to keep it from coming out all in one line when you copy/paste to QA...

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 8

Yeah, and I'd really like to see the colors from EM/QA preserved in the posts to. But maybe I'm being picky.....

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

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

Well Jeff, it looks like you've got another comparison between the new 2005 T-SQL options and good 'ol fashioned set based operations. Here's another great reason not to jump right in with using the new 2005 stuff without thinking hard and gathering performance metrics first.

Heh... yeah, huh? I gotta laugh... Sergiy told me that he thought they were mostly "conveniences" for folks... I didn't believe him at first but couldn't test... no 2k5, yet. I took someone's tag line about "DTS and Cursors" being used by folks that didn't really know T-SQL and applied it to one or two things... started some pretty good long threads and got lots of help from some really good folks. Sergiy did a number on OVER(Partition), we ran some tests (some don't agree with the tests, but that's ok, their loss 😉 ), and since that time, more and more of those conveniences are turning out to be resource hogs... we've even beat up on the occasional CLR.

So far, the only thing that looks helpful is CTE's... but even those can be overcome through simple Temp Table usage... in fact, Temp Tables are more useful than CTE's because, unlike CTE's, they can be used by more than 1 SELECT/query in a proc. CTE's can only be used by the query immediately following as if they were a derived table (which is what they really are).

Trying to figure out what my next target should be... guess I really need to install 2k5 before I pick another one... :w00t:

With guys like you, Jason, and a couple of others being aware of potential performance problems like you are, the proofs are getting easier to do. Thanks for the help!

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

Jason, would you mind run the tests again with this index for testdata table?

CREATE UNIQUE NONCLUSTERED INDEX [ixu_testdata_ndex_incl_tenpower_twodigit] ON [dbo].[testData]

(

[nDex] ASC

)

INCLUDE ( [tenPower], twodigit )

and use this index with a query hint?

SELECTndex,

twodigit,

tenpower,

1

FROMdbo.testData as t1 WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))

CROSS JOINmynumbers as t2

WHEREt2.N <= t1.tenPower;

N 56°04'39.16"
E 12°55'05.25"

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 11

Peter,

I'll get that later today 🙂

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Cool... now we're talking some testing!

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 13

Without the index on testdata table, I got 2000 ms and 50000 reads.

With the index, I got 50 ms and 1000 reads.

N 56°04'39.16"
E 12°55'05.25"

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

On which method, Peter?

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 15

For the cross join (and cross apply).

N 56°04'39.16"
E 12°55'05.25"