October 31, 2007 at 10:00 am
It's been awhile since I've posted something so I thought I'd dust of my keyboard. I have a query that works fine, but it bothers me because I think their is some better way to write it. I know it's not always a good idea to use functions in Where clauses and joins, but this is what I've done and have to work with.
There are two tables
TA_2007
TA_2007_ID varchar(50)
...
TA_2007_D
AW_ID varchar(20)
AW_TYPE varchar(20)
...
TA_2007.TA_2007_ID is stored in the following format ID*TA_ID*YEAR. So an example would be 1234567*EBA01*2007. The ID portion is always 7 characters and never will be more. TA_ID can be anywhere between 5 and 8 characters. And the year is always 4 characters.
The TA_ID portion is the same as TA_2007_D.AW_ID.
SELECT
TA1.TA_2007_ID,
TA2.AW_ID,
TA2.TA_TYPE
FROM
TA_2007 AS TA1
LEFT JOIN
TA_2007_D AS TA2
ON SUBSTRING(TA1.TA_2007_ID,9,(charindex('*',TA1.TA_2007_ID,9)-9)) = TA2.AW_ID
So is there a better way to write this?
Thanks!
October 31, 2007 at 10:15 am
How about creating an indexed view that has the calculated column.
Then create an index on that column and use the view in your query?
When the column is updated SQL should update the calculated value. Therefore it won't have to recalculate it when you do the join. (i stand to be corrected...)
It will also be easier to read.
October 31, 2007 at 10:25 am
Agreed - the function in the ON clause will force a table scan. Split the calculation out to a computed columns, then index the new computed column, and join on that. Should be better.
----------------------------------------------------------------------------------
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?
October 31, 2007 at 11:10 am
That makes sense to me. Good idea. I'll try putting this in an indexed view.
Thanks for the suggestions.
October 31, 2007 at 11:12 am
If the view is too cumbersome - then put the computed column in the actual table definition, and index based on that. That's what I was actually supporting (not so much the view as the indexing of a computed column).
----------------------------------------------------------------------------------
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?
October 31, 2007 at 11:26 am
That would be a good option, however I'm a little hesitant to modify the table structure. This is a third party application and they will every now and then release modification to their tables. Also, modifing the table could cause their tech support to rethink the level of support they give us. If this was more of a homebrewed system I'd probably be all over that option.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply