There Must Be 15 Ways To Lose Your Cursors… Part 2: Just Put It in a S

Question

Post reply

There Must Be 15 Ways To Lose Your Cursors… Part 2: Just Put It in a S

Viewing 15 posts - 181 through 195 (of 316 total)

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

gserdijn Hall of Fame Points: 3283 More actions · Answer 1

Jeff Moden (5/6/2009)
I can only hope that gserdijn got it from me. 😛

Off course!

When you rewrite a cursor to be set based, don't make the same error in logic.

Right again. I humbly admit that I wouldn't have thought of your solution. But the main issue was to write a set-based solution - however bad performing - in order to convince Manie that cursors can be re-written.

(Ah, the beauty of SQL. Just scored my first coffee and already learned something.)

Dutch Anti-RBAR League

ben.rosato SSCommitted Points: 1929 More actions · Answer 2

Jeff Moden (5/6/2009)

[font="Courier New"]  USE AdventureWorks
;WITH cteNames AS
(
SELECT FirstName + ' ' + LastName AS FullName
   FROM AdventureWorks.Person.Contact
  WHERE FirstName + ' ' + LastName LIKE '%[^- .''A-Z]%'
)
SELECT n.FullName,
        SUBSTRING(n.FullName,t.N,1) AS BadChar,
        t.n AS BadCharPosition,
        ASCII(SUBSTRING(n.FullName,t.N,1)) AS BadCharNum
   FROM cteNames n
  CROSS JOIN Util.dbo.Tally t
  WHERE t.N <= LEN(n.FullName)
    AND SUBSTRING(n.FullName,t.N,1) LIKE '[^- .''A-Z]'[/font]

I was playing around with this and can't figure out why the "-" in the where clause has to come before all the other individual characters. If I move it after the . it doesn't produce the same results.

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

gserdijn (5/7/2009)
Jeff Moden (5/6/2009)
I can only hope that gserdijn got it from me. 😛
Off course!
When you rewrite a cursor to be set based, don't make the same error in logic.
Right again. I humbly admit that I wouldn't have thought of your solution. But the main issue was to write a set-based solution - however bad performing - in order to convince Manie that cursors can be re-written.
(Ah, the beauty of SQL. Just scored my first coffee and already learned something.)

Thanks for the feedback. Please don't think that I was badmouthing your good code. You did exactly right and your heart was definitely in the right place with the way you jumped in to help Manie. It was just a prime opportunity for me to state something obvious. 🙂

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

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

ben.rosato (5/7/2009)

Jeff Moden (5/6/2009)
[font="Courier New"]  USE AdventureWorks
;WITH cteNames AS
(
SELECT FirstName + ' ' + LastName AS FullName
   FROM AdventureWorks.Person.Contact
  WHERE FirstName + ' ' + LastName LIKE '%[^- .''A-Z]%'
)
SELECT n.FullName,
        SUBSTRING(n.FullName,t.N,1) AS BadChar,
        t.n AS BadCharPosition,
        ASCII(SUBSTRING(n.FullName,t.N,1)) AS BadCharNum
   FROM cteNames n
  CROSS JOIN Util.dbo.Tally t
  WHERE t.N <= LEN(n.FullName)
    AND SUBSTRING(n.FullName,t.N,1) LIKE '[^- .''A-Z]'[/font]
I was playing around with this and can't figure out why the "-" in the where clause has to come before all the other individual characters. If I move it after the . it doesn't produce the same results.

The reason why is because the dash character is also used to define ranges of characters such as A-Z. The only place it can be included without jumping through a hoop is in the very first character position.

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

UMG Developer SSChampion Points: 13482 More actions · Answer 5

ben.rosato (5/7/2009)
I was playing around with this and can't figure out why the "-" in the where clause has to come before all the other individual characters. If I move it after the . it doesn't produce the same results.

Because if the "-" comes after a character it is being used to specify a range like the "A-Z" section.

ben.rosato SSCommitted Points: 1929 More actions · Answer 6

ben.rosato

SSCommitted

Points: 1929

May 7, 2009 at 11:38 am

#990567

Simple enough reason. Thanks.

Manie Verster SSCertifiable Points: 7152 More actions · Answer 7

Thanks to all for jumping in to help me but that was not quite the reason for me posting the code. I thought Ha! let's see how they figure this one out and was convinced that no-one would be able to do it. Well, with my hat in my hand I have to humbly say: It can be done! Well done gserdijn and Jeff and Jeff, you taught me something today again! I am a bit puzzled with your code though and maybe you can clear something for me.

LIKE '[^- .''A-Z]'

You only put A-Z and not also a-z. How does that work?

I have another cursor that I would like to change to set based and this code might help me to that. I might come back here to ask for some help.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 8

Thanks to all for jumping in to help me but that was not quite the reason for me posting the code. I thought Ha! let's see how they figure this one out and was convinced that no-one would be able to do it. Well, with my hat in my hand I have to humbly say: It can be done! Well done gserdijn and Jeff and Jeff, you taught me something today again! I am a bit puzzled with your code though and maybe you can clear something for me.

LIKE '[^- .''A-Z]'

You only put A-Z and not also a-z. How does that work?

I have another cursor that I would like to change to set based and this code might help me to that. I might come back here to ask for some help.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

peter-757102 SSCertifiable Points: 6877 More actions · Answer 9

Manie Verster (5/9/2009)
Thanks to all for jumping in to help me but that was not quite the reason for me posting the code. I thought Ha! let's see how they figure this one out and was convinced that no-one would be able to do it. Well, with my hat in my hand I have to humbly say: It can be done! Well done gserdijn and Jeff and Jeff, you taught me something today again! I am a bit puzzled with your code though and maybe you can clear something for me.
LIKE '[^- .''A-Z]'
You only put A-Z and not also a-z. How does that work?
I have another cursor that I would like to change to set based and this code might help me to that. I might come back here to ask for some help.

Text comparisons in SQL are done by the rules of a collation...the default collation on installation is case insensitive, so A-Z will also match lowercase characters. You can specify a collation at the server, database and column level as well as override it in comparisons performed in your queries. If you have two fields that have a different collation you have to specify which collation rules to follow while doing the compare. Any indexes not matching the collation used in the comparison cannot be utilized during that comparison and will not be able to help you to speed up the operation. Obviously it is best to stick to one collation and make exceptions only where they are functionally required.

It's worth checking books online on the subject of collations for anyone that has not done so already!

I hope this answer satisfies your question 🙂

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Manie Verster (5/9/2009)
You only put A-Z and not also a-z. How does that work?

Peter gave a pretty good description as to why. To simplify, the default for SQL Server installations is "case insensitive". That means that looking of A-Z, a-z, A-z, or a-Z will all do the same thing. If you have a "case sensitive" installation (ack!) or a case sensitive column (much better), then you'd need for my little LIKE clause to say...

LIKE '[^- .''A-Z[font="Arial Black"]a-z[/font]]'

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

Manie Verster SSCertifiable Points: 7152 More actions · Answer 11

Thanks Jeff and Peter,

That explains it and I also looked it up on books online and now understands it better now.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

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

Very cool. Thanks for the feedback, Manie.

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

FreeHansje SSChampion Points: 11751 More actions · Answer 13

This thread is very educational, and it puts me into trouble: I'm busy looking at all the loops used and trying to make them SET-based. Challencing, but there is more important work to be done.

A question though. I have run Jeff's script against Adventureworks and get Frank Martjnez back. From the WHERE clause I figure that this bad character is outside the scope of the LIKE clause. If I would want to include characters with CHARCODE of 161, 162 and-so-on, how would I add those to the LIKE clause? Just adding something like 'íó'? And while we're at this, can some1 explain how this LIKE clause works? This value: 'nnn''nnn', I would think is not acceptable, it's 2 values (between each pair '') without a comma seperation.

Any1 care to explain this to me?

EDIT: I am awaiting eagerly Barry's next installment of this series!

Greetz,
Hans Brouwer

ganotedp SSC-Addicted Points: 492 More actions · Answer 14

It's sorely tempting to rewrite every cursor into pure SQL (the ETL I inherited is rife with them), but the key is to focus on the "long pole in the tent": only optimize what's hampering business value. Otherwise you're afflicted with CTD -- "Compulsive Tuning Disorder" (a term devised by Gaja Krishna Vaidyanatha in 2001).

http://searchoracle.techtarget.com/news/interview/0,289202,sid41_gci1075628,00.html#

The "curse of the cursors" is the bane of every RDBMS, not just SQL Server!

http://it.toolbox.com/blogs/data-ruminations/the-curse-of-the-cursor-31851

ohack SSCrazy Points: 2043 More actions · Answer 15

This was shaping to be a magnificent series, but is part 3 on the way? I really hope so.

The import routines we use for our crm system all use cursors and we have severe performance problems with housekeeping jobs and imports running almost 24*7. I'd love to convert them all and was hoping this brilliantly clearly written series would teach me how.