SIMPLE COMPARE? This query is taking weeks to run

Last Post 17 Oct 2006 07:05 AM by xfonhe. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
krypto69
New Member
New Member

--
16 Oct 2006 07:28 AM
We have only two tables in a DB, we are only comparing two columns and about 400k records. We are trying to run a compare query against these tables, problem is it is taking weeks to get the results. There has got to be a better way to do this - any help much appreciated. We suspect the 'like' is confusing the optimizer? We tried outer joins, painfully slow. Everything is indexed, etc.

Here's the query:

declare @maxkey int
declare @minkey int
DECLARE @Pkey numeric
DECLARE @InstCode VARCHAR(20)
DECLARE @Clk1 VARCHAR(20)
DECLARE @Clk2 VARCHAR(20)


set @maxkey = (Select max(pkey) from [CompareOpticom] )
set @minkey = (Select min(pkey) from [CompareOpticom] )
set @pkey = (Select min(pkey) from [CompareOpticom] )

WHILE @minkey <= @maxkey --(@@Fetch_Status = 0)
BEGIN
set @InstCode = (Select LTRIM(RTRIM([InstCode])) from [CompareOpticom] where pkey = @pkey)
set @clk1 = (select LTRIM(RTRIM(REPLACE([ClerkNo],' ',''))) from [CompareOpticom] where pkey = @pkey)
set @clk2 = (select LTRIM(RTRIM(REPLACE([ClerkNo2],' ',''))) from [CompareOpticom] where pkey = @pkey)

print @pkey

IF @InstCode = 'SF'
BEGIN
IF (SELECT COUNT(*) FROM PROPERTY WHERE --countynum = 45 and
REPLACE(filenum,' ','') like @Clk1 + '%' )=0
BEGIN
-- insert
INSERT INTO [MissingOptClkFinal]([Cnty], [ClerkNo], [ClerkNo2], [RDate], [InstCode], [RefAmt], [Lot], [Block], [LP_PlatNo], [PlatBk], [PlatPg], [Scr], [Remarks], [Grantor], [Grantee], [KeyDate], [DblZeroCol], [CPCol], [PlatBk2], [PlatPg2], [PlatPg22], [Block2], [Lot2], [RDate2])
SELECT DISTINCT [Cnty], [ClerkNo], [ClerkNo2], [RDate], [InstCode], [RefAmt], [Lot], [Block], [LP_PlatNo], [PlatBk], [PlatPg], [Scr], [Remarks], [Grantor], [Grantee], [KeyDate], [DblZeroCol], [CPCol], [PlatBk2], [PlatPg2], [PlatPg22], [Block2], [Lot2], [RDate2]
FROM [CompareOpticom]
WHERE pkey = @pkey
END
END
ELSE
BEGIN
IF (SELECT COUNT(*) FROM PROPERTY WHERE --countynum = 45 and
REPLACE(REPLACE(clerkno,'-',''),' ','') = @Clk1 + @clk2) = 0
BEGIN
INSERT INTO [MissingOptClkFinal]([Cnty], [ClerkNo], [ClerkNo2], [RDate], [InstCode], [RefAmt], [Lot], [Block], [LP_PlatNo], [PlatBk], [PlatPg], [Scr], [Remarks], [Grantor], [Grantee], [KeyDate], [DblZeroCol], [CPCol], [PlatBk2], [PlatPg2], [PlatPg22], [Block2], [Lot2], [RDate2])
SELECT DISTINCT [Cnty], [ClerkNo], [ClerkNo2], [RDate], [InstCode], [RefAmt], [Lot], [Block], [LP_PlatNo], [PlatBk], [PlatPg], [Scr], [Remarks], [Grantor], [Grantee], [KeyDate], [DblZeroCol], [CPCol], [PlatBk2], [PlatPg2], [PlatPg22], [Block2], [Lot2], [RDate2]
FROM [CompareOpticom]
WHERE pkey = @pkey
END
END

set @pkey = (Select min(pkey) from [CompareOpticom] where pkey >@pkey)

END

xfonhe
New Member
New Member

--
17 Oct 2006 07:05 AM
Look at replacing the WHILE LOOP with set-based methods. The looping construct is much slower than a set-based operation in SQL Server, as the SQL Server engine is optimized for set-based operations.
You are not authorized to post a reply.

Acceptable Use Policy