string compare again .. :)

Question

string compare again .. :)

Viewing 4 posts - 16 through 18 (of 18 total)

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

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

Alan Burstein wrote:

Just as a side note, depending on how often your columns are updated/modified, you can always split the string ahead of time using a permanent tally table like this:

Agreed. I said similar in this post from above...

https://www.sqlservercentral.com/forums/topic/string-compare-again#post-3776562

Alan Burstein wrote:

Note the execution plan

It would be helpful if you just posted the execution plan and tell us what we're supposed to take note of. 😀

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Jeff Moden wrote:

Alan Burstein wrote:
Just as a side note, depending on how often your columns are updated/modified, you can always split the string ahead of time using a permanent tally table like this:
Agreed. I said similar in this post from above...
https://www.sqlservercentral.com/forums/topic/string-compare-again#post-3776562
Alan Burstein wrote:
Note the execution plan
It would be helpful if you just posted the execution plan and tell us what we're supposed to take note of. 😀

Just fixed it. Added a couple plans. Also re-added the unique clustered index for the Indexed view which was missed copying/pasting.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Jeff Moden wrote:

Agreed. I said similar in this post from above...

I missed this.

The difference is that I did the split using an indexed view. This way the split only happens once, then once more, as needed, when the a new string is added to the strHold table. I don't think people take advantage of "pre-splitting" strings for stuff like this where the same string is split each time the contents need to be read.

Tally tables + Indexed Views - a very fun topic. 🙂

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

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

Alan Burstein wrote:

Jeff Moden wrote:
Agreed. I said similar in this post from above...
I missed this.
The difference is that I did the split using an indexed view. This way the split only happens once, then once more, as needed, when the a new string is added to the strHold table. I don't think people take advantage of "pre-splitting" strings for stuff like this where the same string is split each time the contents need to be read.
Tally tables + Indexed Views - a very fun topic. 🙂

That's actually an awesome idea. I can also see an application for what I call "poor-man's Full Text" for full name and address lookups. Great idea Alan.

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