Could I eliminate this cursor?

Question

Could I eliminate this cursor?

Viewing 12 posts - 16 through 26 (of 26 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Good post, Jeff.

I have almost always (90%+) had good results from the estimator, but then as you know, I hardly ever use cursors or recursion.

Now that I think about it though, it's obvious that the Query Plan estimator could not predict such things accurately. It is one of the hallmark proofs of computer science that you cannot write a program that can generally predict when other computer programs will halt (that's from Godel in math, but I forget who proved it in computer science). A query meets the criteria of a "program", so in theory it should not be possible to generally predict a queries run-time.

The estimator does a good job normally with the specific queries that I write because they are usually based around simple join conditions: since the input sets are know quantities and the conditionals are known and stable, it can be estimated reasonably well. However, the more general cases of recursion, WHILE loops, complex or unstable join/subquery conditions, etc. are not predictable.

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

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

Pretty darned good post, yourself, Barry.

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

Thanks, Jeff.

rbarryyoung (3/29/2008)
It is one of the hallmark proofs of computer science that you cannot write a program that can generally predict when other computer programs will halt (that's from Godel in math, but I forget who proved it in computer science).

Oh yeah, it was Turing. Duh.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

rbarryyoung (3/29/2008)
Thanks, Jeff.
rbarryyoung (3/29/2008)
It is one of the hallmark proofs of computer science that you cannot write a program that can generally predict when other computer programs will halt (that's from Godel in math, but I forget who proved it in computer science).
Oh yeah, it was Turing. Duh.

yup - one of the famous "NP" problems.... Wow, THAT's throws me back...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I see you two have been busy... nice testing!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

... and, as we both noticed, it's been fun, too boot! Thanks, Matt.

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

bluepaperbags SSCommitted Points: 1738 More actions · Answer 7

go some data

Party table

-----------------------------------------

instrument_id party_id last_name first_name middle_name name_suffix

31966558329324CLUBBSTACYL

331891810350608CROOKE,BESSIE J EST

331891810350858JACKSONLLOYDD

output

-------------------

instrument_idparty_idsort_orderparty_data

319665583293241(I) CLUBB, STACY L

3318918103506082(I) CROOKE,BESSIE J EST

3318918103508583(I) JACKSON, LLOYD D

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Matt Miller (3/29/2008)
rbarryyoung (3/29/2008)
Thanks, Jeff.
rbarryyoung (3/29/2008)
It is one of the hallmark proofs of computer science that you cannot write a program that can generally predict when other computer programs will halt (that's from Godel in math, but I forget who proved it in computer science).
Oh yeah, it was Turing. Duh.
yup - one of the famous "NP" problems.... Wow, THAT's throws me back...

Speaking of famous "NP" problems - did I mention that someone has a better than linear approach to the "traveling salesman" problem?

remarkable breakthrough here[/url]

😀

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

escaleraroyal (3/31/2008)
go some data
Party table
-----------------------------------------
instrument_id party_id last_name first_name middle_name name_suffix
31966558329324CLUBBSTACYL
331891810350608CROOKE,BESSIE J EST
331891810350858JACKSONLLOYDD
output
-------------------
instrument_idparty_idsort_orderparty_data
319665583293241(I) CLUBB, STACY L
3318918103506082(I) CROOKE,BESSIE J EST
3318918103508583(I) JACKSON, LLOYD D

Is there a question that goes along with this data?

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

Cory E. SSCertifiable Points: 7076 More actions · Answer 10

x-portal (3/31/2008)
i'm on sql server 2000.

Is there a question here? Not a lot of info - and, this is the sql 2005 forum group...

-- Cory

bluepaperbags SSCommitted Points: 1738 More actions · Answer 11

Jeff,

They just asked me to performance tune it.

But it appears the SP insert data if (party_data) > 36. Then it adds up a sort_order, and for new instrument_id the sort_order starts again from 1. This is what I undestand from this SP. I think you can read it and know better. This is my little undestanding from reading the SP.

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

escaleraroyal (3/31/2008)
Jeff,
They just asked me to performance tune it.
But it appears the SP insert data if (party_data) > 36. Then it adds up a sort_order, and for new instrument_id the sort_order starts again from 1. This is what I undestand from this SP. I think you can read it and know better. This is my little undestanding from reading the SP.

You submitted code without testing it for performance and index usage? :blink:

Ok... we can help... but no one can help you tune what we can't see. Post the code you now use, the table definitions, and some sample data... now, before you even try to do that, you really need to click and read the URL in my signature line. We've already had to put data together once to help you... not real interested in putting data together again especially after I asked you to do this once before and you ignored me. Help me help you. 😉

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