Substring Function in Where Clause

Question

Substring Function in Where Clause

Viewing 4 posts - 31 through 33 (of 33 total)

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

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

eseosaoregie (4/23/2010)
Thanks jeff. Will try it this weekend and let you know how it runs. Much appreciated

Since you PM'd me several times on the subject and half the weekend has gone by, I thought I'd ask if the latest changes I made suited 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)

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 2

Hey jeff, sorry for not getting back to you. I have been really busy this weekend, but I am going to run the changes this evening. I will post either tonight or tomorrow morning.

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 3

Hi Jeff,

Managed to run the amendments this afternoon and found considerable improvement in performance. Whole thing loaded in just under 2min 30sec. Network traffic is high during that period so if it is run at when as planned it may be quicker.

I am currently running 2 parallel test environments, one in sql 2K and the other in 2005. I would like to replicate this query in the 2K environment. How would I amend the query as you can have neither persisted columns or CTEs in 2K.

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

eseosaoregie (4/26/2010)
Hi Jeff,
Managed to run the amendments this afternoon and found considerable improvement in performance. Whole thing loaded in just under 2min 30sec. Network traffic is high during that period so if it is run at when as planned it may be quicker.
I am currently running 2 parallel test environments, one in sql 2K and the other in 2005. I would like to replicate this query in the 2K environment. How would I amend the query as you can have neither persisted columns or CTEs in 2K.

The answer is simple... just convert the CTE's to Derived Tables (sub queries in the FROM clause) and drop the word PERSISTED.

Just to save folks time in the future, you might want to let them know when code has to run in a mixed environment.

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