November 17, 2008 at 9:25 am
I am trying to remove several lookups in an SSIS package by moving these into 1 stored proc to reduce the hit on the db and to reap the reward of better performance, re-using query plans etc.
One of these left joins is on a UserName varchar field. In order to do perform the join I need to perform the following on each side of the join, Upper(Rtrim(Ltrim(username))). I am using a derived table as 1 input to incorporate where statements as well as the following code Upper(Rtrim(Ltrim(username))) AS UserName.
Then I come to the join itself ON DT.UserName. = CAST(UPPER(LTRIM(RTRIM(SSI.UserName))) AS nvarchar(256)) . This, as you can imagine doesn't perform well.
Do you guys have any suggestions what I should be looking to do here?
Thanks
November 17, 2008 at 9:37 am
Are you sure that you need to upper case the username? This would only be required if the collation on the column or database is case sensitive. I would verify that first.
In most cases - you don't need to rtrim a column for comparison since SQL Server will remove spaces for the comparison. This again is determined by the collation - and I would suspect that it is not needed.
So, that leaves us with the left trim - which you should not allow in the table in the first place. Verify whether or not you have any user names in either table that contain leading spaces, and if so - perform an update and remove the spaces.
Once the above is done - you should see improvement in your performance.
Now, if you cannot do any of the above to correct the data in the tables - you could fix the data by creating an indexed view and creating an index on the computed column for you username without the extra spaces.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 18, 2008 at 3:02 am
Jeff, thanks for you advice.
The collation used is SQL_Latin1_General_CP1_CI_AS. So this is case insensitive. If the join handles the RTRIM then as you state that leaves the LTRIM to deal with. I cannot update the tables so I will have to create an indexed view that encapsulates the LTRIM statement on the username and use this as the index.
Does this sound right?
November 18, 2008 at 3:06 pm
Yes - that sounds right. I would verify whether or not I actually needed the ltrim - which you can do easily enough with a like statement checking for the first character as a space.
You might not even need the indexed views if there are no rows where those columns begin with a space.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply