Validation queries running too long

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 2000 Forums

Transact-SQL (2000)

Validation queries running too long

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-30 : 18:46:11

I have a table that contains approx 200 thousand records that I need to run validations on. Here's my stored proc:


CREATE PROCEDURE [dbo].[uspValidateLoadLeads]
@sQuotes char(1) = null, @sProjectId varchar(10) = null, @sErrorText varchar(1000) out
AS                                                                                                                                                                   BEGIN
DECLARE @ProcName sysname, @Error int, @RC int, @lErrorCode bigint, DECLARE @SQL varchar(8000)

IF @sQuotes = '0'
BEGIN
     UPDATE dbo.prProjectDiallingList_staging 
     SET sPhone = RTrim(LTrim(Convert(varchar(30), Convert(numeric(20, 1), phone))))
END
ELSE 
BEGIN 
     UPDATE dbo.prProjectDiallingList_staging
     SET sPhone = phone
END 

--2. Remove quotes
UPDATE dbo.prProjectDiallingList_staging
SET sphone = REPLACE(sphone,'"' , '')

--3. Remove decimal, comma, dashes, parenthesis
UPDATE dbo.prProjectDiallingList_staging
SET sphone = replace(replace(replace(replace(replace(replace(sphone,'.',''),',','' ),'-',''), ' ',''), '(', ''), ')', '')

--4. Update failed Validation column if not 10 digits
UPDATE dbo.prProjectDiallingList_staging
SET sFailedValidation = 'X' 
WHERE(Len(RTrim(LTrim(sPhone))) <> 10)

--5. Dedup
UPDATE a 
SET a.sFailedValidation = 'X'
FROM dbo.prProjectDiallingList_staging a (nolock)
INNER JOIN dbo.prProjectDiallingList_staging b
ON a.sPhone= b.sPhone 
WHERE(a.iList_StagingID > b.iList_StagingID)

--6. Update failed Validation column if not numeric
UPDATE dbo.prProjectDiallingList_staging
SET sFailedValidation = 'X' 
WHERE(IsNumeric(RTrim(LTrim(sPhone))) = 0)

--7. Update time zones
UPDATE s
SET s.sTimeZone =z.sTimeZone
FROM dbo.prProjectDiallingList_staging s (nolock)
LEFT OUTER JOIN dbo.prPhoneTimeZone z
ON left(rtrim(ltrim(s.sphone)),3) = z.sPhoneAreaCode

--8. Insert into dialing table only records that have not failed the validation
INSERT dbo.prProjectDiallingList(iPrProjectId, sPhoneNumber, sTimeZone) 
SELECT @sProjectId,sPhone, sTimeZone 
FROM dbo.prProjectDiallingList_staging
WHERE ISNULL(sFailedValidation,'1') = '1'

UPDATE d
SET d.bProcessReporting = 1
FROM dbo.prProjectDialling d
WHERE d.iPrProjectId = @sProjectId
END

When I execute this stored proc it runs for more than 5 minutes. Is there anything i can do to speed it up? Maybe there is a faster way of writing these queries?

Thanks,
Ninel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-30 : 18:51:14

include a where condition that only update if the condition is matched


--2. Remove quotes
UPDATE dbo.prProjectDiallingList_staging
SET sphone = REPLACE(sphone,'"' , '')
where sphone like '%"%'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-30 : 19:48:02

It looks like you are running multiple updates that could be combined into one update.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 09:43:42

Something like this. First write a function that only extracts digits

CREATE FUNCTION dbo.fnGetDigitsOnly
(
	@Phone VARCHAR(30)
)
RETURNS VARCHAR(30)
AS

BEGIN
	DECLARE @Index TINYINT,
		@Temp VARCHAR(30),
		@Char CHAR

	SELECT	@Index = 1

	WHILE @Index <= LEN(@Phone)
		SELECT	@Char = SUBSTRING(@Phone, @Index, 1),
			@Temp = CASE WHEN @Char >= '0' AND @Char <= '9' THEN ISNULL(@Temp, '') + @Char ELSE @Temp END,
			@Index = @Index + 1

	RETURN @Temp
END

Then optimize the SP as

CREATE PROCEDURE dbo.uspValidateLoadLeads
(
	@sQuotes char(1) = null,
	@sProjectId varchar(10) = null,
	@sErrorText varchar(1000) out
)
AS

SET NOCOUNT ON

DECLARE	@ProcName sysname,
	@Error int,
	@RC int,
	@lErrorCode bigint,
	@SQL varchar(8000)

UPDATE	dbo.prProjectDiallingList_staging 
SET	sPhone = dbo.fnGetDigitsOnly(phone)
WHERE	PATINDEX('%[^0-9]%', phone) > 0

UPDATE	dbo.prProjectDiallingList_staging 
SET	sPhone = phone
WHERE	PATINDEX('%[^0-9]%', phone) = 0

UPDATE	a
SET	a.sFailedValidation = 'X'
FROM	dbo.prProjectDiallingList_staging a
WHERE	LEN(ISNULL(a.sPhone, '')) <> 10
	OR EXISTS (SELECT b.* FROM dbo.prProjectDiallingList_staging b WHERE b.sPhone = a.sPhone AND b.iList_StagingID < a.iList_StagingID)

INSERT		dbo.prProjectDiallingList
		(
			iPrProjectId,
			sPhoneNumber,
			sTimeZone
		) 
SELECT		@sProjectId,
		a.sPhone,
		z.sTimeZone 
FROM		dbo.prProjectDiallingList_staging a
LEFT JOIN	dbo.prPhoneTimeZone z ON z.sPhoneAreaCode = left(a.sphone, 3)
WHERE		ISNULL(a.sFailedValidation, '1') = '1'

UPDATE	dbo.prProjectDialling
SET	bProcessReporting = 1
WHERE	iPrProjectId = @sProjectId

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources