August 5, 2004 at 8:57 pm
I am trying to compare data from two quarters that I have.
Table structure is the same between both tables that I am comparing with the PK being the same between each table as well.
The following is the batch "procedure" that I have "created" based on other threads from the forums.
declare @t int
set @t=1
while @t>0
begin
begin transaction t1
set rowcount 1000
update q2204 set q22004.batch = q12004.batch, q22004.updated = 1
from q2204 inner join q12004
on q22004.phone = q12004.phone
and updated = 0 and q2204.batch is null
set @t=@t-1
commit transaction t1
waitfor delay '00:00:0.2'
end
When I run through this, even for a rowcount of 1, I'm getting no results back for an inordinate amount of time. (a single record being updated via the above took over 11 minutes to accomplish).
The column "updated" has an index and is a character field (currently housing only 0 and 1 based on a yes/no type scenario.
Any insight as to what I am doing incorrect here?
Thanks
August 6, 2004 at 2:13 am
Is Phone your primary key, if not is in indexed?
How may records are there in each table.
Where is your WHERE clause?
August 6, 2004 at 8:04 am
Phone is the PK for both tables.
Roughly 22 Mil rows in Q22004 and ~40 Mil rows in Q12004.
The where clause was left out in the query instead of the and updated = 0 it should have had where updated = 0 (I am running this now to see if it makes a difference though).
August 6, 2004 at 8:21 am
I probably wouldn't try joining a 22million row table to a 40 million row table directly.
I would probably insert a few thousand q12004.batch,q12004.phone records into a temporary table then do the join on that.
Exactly how many represents a few thousand can only be determined by experimenting.
DECLARE @MaxPhone VARCHAR(10)
SET @MaxPhone=''
CREATE TABLE #Tmp(Phone VARCHAR(10),Batch Int)
WHILE @MaxPhone IS NOT NULL
BEGIN
INSERT #Tmp
SELECT TOP 1000 Phone,Batch
FROM dbo.Q12004
WHERE Phone>@MaxPhone
ORDER BY phone
SELECT @MaxPhone = MAX(Phone) FROM #Tmp
IF @MaxPhone IS NOT NULL
<<Your update statement using #Tmp>>
TRUNCATE TABLE #Tmp
END
You may be able to get away without the ORDER BY statement. The query is still not going to be lightening fast.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply