December 16, 2014 at 1:05 pm
The regulators have naming conventions. Then, on top of that the name appends a H or D at the end of the name to determine if it qualifies for the optional "Horizontal" or "Directional". The answer for "does it contain an H or D?" is true or false.
Why the last 3? Of course there are two more options for letters and no national naming requirement order! LOL
So at the end of a name it could be HenryTSD or HenryDST.
After running an analysis on tens of thousands of records, all H or D fall in the last 3 characters of the Name field.
There was a couple of cases where a name's last three characters ended with a 'd' or 'h'.
What I need in my scalar function is an efficient way to check for both (uppercase) H OR D in the left 3 characters.
This only finds a single character -
Select wells.ID_Wells, charindex('H', right(Wells.Well_Name, 3))
from Wells
This doesn't work -
Select wells.ID_Wells, charindex(in('H', 'D'), right(Wells.Well_Name, 3))
from Wells
This doesn't seem *efficient*
Select ID_Wells
from Wells
where ((charindex('H', right(Well_Name, 3))))>0 or ((charindex('D', right(Well_Name, 3))))>0
The last choice using an OR made the function run close to 10 times slower.
even worse was using the Like with a wild card inside the left statement (with an OR)
Added Full Text search to the field Well_Name - no difference.
December 17, 2014 at 8:44 am
DOH! What a Nubie! Can't believe how bad I messed up the Select statement.
By not adding the second ID_Wells = @ID_Wells parameter to the simple query
The query was doing exactly what I asked, Returning every record regardless of the primary ID.
This not only returns the proper data, it the select statement with this included now runs in about 3 seconds instead of over a minute.
Lack of sleep and too many distractions. Yesterday, my VPN into SQL Server kept loosing connection. Still no excuse for such a mistake.
Set @CountResult =(SELECT COUNT(*)
FROM Wells
WHERE (ID_Wells = @ID_Wells) AND (RIGHT(Well_Name, 3) LIKE '%H%') OR
(ID_Wells = @ID_Wells) AND (RIGHT(Well_Name, 3) LIKE '%D%')
)
December 17, 2014 at 9:14 am
Noobie in deed 😛
DECLARE @CountResult int, @ID_Wells int = 1
Set @CountResult =(SELECT COUNT(*)
FROM (VALUES(1, 'SomethingH'),
(1, 'Somethingd'),
(1, 'SomethingD'),
(1, 'SometHing'))Wells(ID_Wells, Well_Name) --TestData
WHERE ID_Wells = @ID_Wells
AND RIGHT(Well_Name, 3) COLLATE LATIN1_GENERAL_BIN LIKE '%[HD]%'
)
SELECT @CountResult
December 17, 2014 at 10:33 am
Thanks for that!!
Searched the internet for that solution. The search terms just never got me there.
It looks so simple now that you have pointed it out.
At least I got something positive to use in the future out of this post! 🙂
December 17, 2014 at 11:30 am
Yes, it's intended to look simpler as the conditions are reduced by half. Note that I changed the collation, basically because I have a case insensitive collation and because binary collations are faster than others.
It's a good thing that you were able to learn something from this. 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply