Validation queries running too long -- sql server programming

Arnie Rowland
8/30/2006 10:06:25 PM

Ninel,

As I look through the code, I notice eight UPDATE statements operating on
the staging table. With a table size of 200K rows, that means 1.4-1.6
million rows having to be written to the transaction log.

I think it is worth the effort to consider moving most -if not all of the
'clean-up' code into a couple of FUNCTIONs. You may be able to then UPDATE
the table with only two or three passes instead of eight.

Also, there are several self JOINs -on a table this size, without indexes,
that has to be slower than necessary.

In order to assist you in that endeavor, should you so desire, please send
the DDL for the staging table and the production table, as well as
prPhoneTimeZone. Also some sample staging data in the form of INPUT
statements.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

[quoted text, click to view]

ngorbunov
8/30/2006 11:11:40 PM

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

[code]
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
[/code]

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

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200608/1

sql server programming : Validation queries running too long