June 16, 2009 at 1:22 pm
Hi, I have many procs that uses the new feature- MERGE statement of SQL 2008. But on the code there has been multiple uses of UPPER, LTRIM and RTRIM functions for ON (serach condition) of MERGE statement. Below is the actual code for on of them, and i have this in many places
MERGE [SAERPOS].[DBO].[DS_Y] AS DEST USING ( SELECT [_TYPE]
,[_FZ]
,[MODE]
,[NUMBER]
,[ENDING_DATE]
,[BEGINNING_DATE]
,[LIST_PRICE]
,[NET_PRICE]
,[SPECIAL_CODE1]
,[SPECIAL_PRICE1]
,[SPECIAL_CODE2]
,[SPECIAL_PRICE2]
,[SPECIAL_QUANTITY]
,[STATUSID]
,[ENTRYDATE]
FROM [POSI].[DBO].[TMP_FTA]
WHERE [STATUSID] = @READYSTATUS ) AS THESOURCE ( [_TYPE], [_FZ], [MODE], [NUMBER], [ENDING_DATE], [BEGINNING_DATE], [LIST_PRICE], [NET_PRICE], [SPECIAL_CODE1], [SPECIAL_PRICE1], [SPECIAL_CODE2], [SPECIAL_PRICE2], [SPECIAL_QUANTITY], [STATUSID], [ENTRYDATE] )
ON
(
UPPER(LTRIM(RTRIM(DEST.[_TYPE]))) = UPPER(LTRIM(RTRIM(THESOURCE.[_TYPE])))
AND UPPER(LTRIM(RTRIM(DEST.[_FZ]))) = UPPER(LTRIM(RTRIM(THESOURCE.[_FZ])))
AND UPPER(LTRIM(RTRIM(DEST.[MODE]))) = UPPER(LTRIM(RTRIM(THESOURCE.[MODE])))
AND UPPER(LTRIM(RTRIM(DEST.[NUMBER]))) = UPPER(LTRIM(RTRIM(THESOURCE.[NUMBER])))
) WHEN NOT MATCHED THEN INSERT ( [_TYPE], [_FZ], [MODE], [NUMBER], [ENDING_DATE], [BEGINNING_DATE], [LIST_PRICE], [NET_PRICE], [SPECIAL_CODE1], [SPECIAL_PRICE1], [SPECIAL_CODE2], [SPECIAL_PRICE2], [SPECIAL_QUANTITY], [STATUSID], [ENTRYDATE] )
VALUES
(
THESOURCE.[_TYPE]
,THESOURCE.[_FZ]
,THESOURCE.[MODE]
,THESOURCE.[NUMBER]
,THESOURCE.[ENDING_DATE]
,THESOURCE.[BEGINNING_DATE]
,THESOURCE.[LIST_PRICE]
,THESOURCE.[NET_PRICE]
,THESOURCE.[SPECIAL_CODE1]
,THESOURCE.[SPECIAL_PRICE1]
,THESOURCE.[SPECIAL_CODE2]
,THESOURCE.[SPECIAL_PRICE2]
,THESOURCE.[SPECIAL_QUANTITY]
,THESOURCE.[STATUSID]
,THESOURCE.[ENTRYDATE]
) WHEN MATCHED THEN UPDATE
SET [ENDING_DATE] = THESOURCE.[ENDING_DATE]
,[BEGINNING_DATE] = THESOURCE.[BEGINNING_DATE]
,[LIST_PRICE] = THESOURCE.[LIST_PRICE]
,[NET_PRICE] = THESOURCE.[NET_PRICE]
,[SPECIAL_CODE1] = THESOURCE.[SPECIAL_CODE1]
,[SPECIAL_PRICE1] = THESOURCE.[SPECIAL_PRICE1]
,[SPECIAL_CODE2] = THESOURCE.[SPECIAL_CODE2]
,[SPECIAL_PRICE2] = THESOURCE.[SPECIAL_PRICE2]
,[SPECIAL_QUANTITY] = THESOURCE.[SPECIAL_QUANTITY]
,[STATUSID] = THESOURCE.[STATUSID]
,[ENTRYDATE] = THESOURCE.[ENTRYDATE] ;
Now,
Also, I know we alwys try to avoid use of functions in our where clause.
So, How does this use of functions in ON of MERGE statement reacts as performance. Does it behave the same way of any other WHERE clause ( we all know what we talking abut).
If there is a better way for not doing this or it is not worth the work ?
Also, If i want to avoid those functions, How can I do that with MERGE.?
June 16, 2009 at 8:11 pm
Unless you know for a fact that the server or the columns in the given table are case sensitive, just remove all instances of UPPER. This isn't Oracle. 😛
Unless you know for a fact that the columns involved are CHAR columns instead of VARCHAR columns, the just remove all instance of RTRIM.
There's a very high chance that you will not have any leading data with leading spaces. Do a couple of checks on the columns to see if there are. If there aren't, then just remove all instances of LTRIM.
For comparison purpose, and empty string of '' is the same as ' ' and ' '. '' does not mean NULL in SQL Server like it does in other RDBMS's.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2009 at 7:48 am
Jeff Thanks, You have been alwus a gret help.
Out of all 4 columns used on On conditions, 3 are nchar of length 2, 2, and 12. StatusID is small int. So If I am thinking rt here, I can simply remove all functions for int datatype column, used in our Serach ON condition. ALso after few test, I think I can remove all RTRIM functions.
Here this is what I did
DECLARE @CHAR nchar(12)
SET @CHAR = 'Apple ORange '
IF @CHAR = 'APPLE Orange'
PRINT 'YES'
ELSE
PRINT 'NO' I think if it is nchar datatype spaces to the right are ignored.
However, I am not sure abut LTRIM,
because when I compare ' A' with 'A' it gives me NO
I checked out all others tbls and other merge statement and found out that in all cases we have nchar datatype of 2 4 10 12 0r 14 and smallint.
So what whould u suggest here?
Also, If u have done some test, How does it behave using functions in "ON" for MERGER sta. Like When you use functions in ur where clause of T-sql, U usually see Index Scan rather than Index seek. Does this happens with MERGE ?
Thanks
June 20, 2009 at 6:47 pm
Sorry for the delay. I agree that if you have any leading spaces, you'll need to do one of two things... so far as I'm concerned, the best thing to do first would be to find out what the root cause of having leading spaces in the data is, fix it, and the repair all of the data.
Otherwise, you'll probably need to use option 2 which would be to use LTRIM in the WHERE or ON clause and we all know what happens there.
For MERGE, I'll have to say that I've not used it yet because I don't have 2k8. My inclination would be to say that it will follow the same rules as INSERTs, UPDATE's, DELETE's, and SELECT's.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply