Quicker Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Quicker Update Query

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-14 : 16:19:05

I currently am working woth a lagacy db, and regullay have to get data from that db to use in a different db.
One of the issues i have is that the legacy system didnt format the data. Currently I am running a query to update the records prior to using them.

Update Legacy_Table
Set DestSortCode = CASE ISNULL(DestSortCode, '') WHEN '' then NULL ELSE RIGHT('000000' + REPLACE(DestSortCode,'-',''), 6) END
,DestAccountNumber = CASE ISNULL(DestAccountNumber, '') WHEN '' THEN NULL ELSE RIGHT('00000000' + DestAccountNumber , 8)END
,DestAccountName = CASE ISNULL(DestAccountName, '' ) WHEN '' THEN NULL ELSE UPPER(LEFT(dbo.CheckBACSValidChar (DestAccountName) + SPACE(18), 18)) END
,DestBankRef = CASE ISNULL(DestBankRef, '') WHEN '' THEN NULL ELSE UPPER(LEFT(dbo.CheckBACSValidChar (DestBankRef) + SPACE(18), 18)) END
WHERE LedgerKey = @LedgerKey AND IMPSequence = @IMPSequenceIN

ALTER function [dbo].[CheckBACSValidChar](@TestString varchar(20))
Returns Varchar(max)
Begin
DECLARE @str2 VARCHAR(100)
DECLARE @RTNSTR Varchar(max)
Set @str2 = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 &-./'
declare @i int
Set @i = 0
SET @RTNSTR = '';
While @i < = len(@TestString)
BEGIN
if Charindex(substring(@TestString,@i,1),@str2) > 0
SET @RTNSTR = @RTNSTR + substring(@TestString,@i,1)
ELSE
SET @RTNSTR = @RTNSTR + SPACE(1)
SET @i = @i + 1
End
RETURN @RTNSTR
End

Currently with 241000 records it is taking nearly 60 seconds, can anyone suggest a way to reduce this time

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-14 : 17:24:16

It is probably the while loop in the function that is killing your performance. Can you use a variation of what Madhivanan is proposing here?

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

In his approach he uses master..spt_values. I think master..spt_values has only about 2000 or so numbers, so if your string is longer than that, you would need to use a numbers table.

Kristen
Test

22859 Posts

Posted - 2012-01-14 : 18:39:22

While of less significance, the output will never be more than the size of @TestString will it?

Thus using "Varchar(max)" for the Return string, and for @RTNSTR, is excessive, and using Varchar(MAX) rather than Varchar(8000) may be detrimental to performance.

Better still would be to set the maximum to something reasonable, e.g. the same size as @TestString, rather than "maximum system allows".

I would not concatenate "good" characters one-by-one, I would find the bad ones and then replace them (e.g. using STUFF(...) ), in situ, with a space. Much less character twiddling.

If most/many of the calls to CheckBACSValidChar will NOT find any work to do - i.e. no rogue characters - you could shot-circuit the function so that "clean" strings return immediately:


ALTER function [dbo].[CheckBACSValidChar](@TestString varchar(20))
Returns Varchar(max)
Begin
IF @TestString NOT LIKE '%[^-a-zA-Z0-9 &./]%'
-- @TestString is already "clean"
RETURN @TestString
END
DECLARE @str2 VARCHAR(100) 
DECLARE ...

that may give the quickest "fix", performance-wise

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources