Slow performance in Stored Procedure

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

SQL Server Development (2000)

Slow performance in Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-10 : 19:47:17

Carlos writes "I'm new to Sql Server and I was wondering if you can help me out.

The following script compares two tables. Table1 is temp table and table2 is the actual table. After comparing both tables if a customer's exists in table1 and table2 and the data changed on any field in table1, then table2 gets updated. If there's no change table2 remains the same. Last, if customer from table1 doesn't exists in table2, then this record gets inserted in table2.
The script works,however when I use it against our real database which it has about 200,000 records, this process takes hours to complete. Is there any way to enhance it?
I'm forced to use this method because I get the whole table dump everyday.
Thanks in advance and I enjoy your site.

declare @id char(10),@name char(10),@email char(40),@zipcode char (40)

declare Cur_Test cursor
for select id,name,email,zipcode from table1

open Cur_Test

fetch next from Cur_test into @id,@name,@email,@zipcode

while @@fetch_status=0

begin

if exists (select * from table2 where id = @id and (name<>@name or @email<>email or @zipcode<>zipcode))

update table2
set id=@id,name=@name,email=@email,zipcode=@zipcode,date_row_mod=getdate()
where id=@id

else

if not exists (select id from table2 where id = @id)

insert into table2
values (@id,@name,@email,@zipcode,getdate(),null)

fetch next from Cur_Test into @id,@name,@email,@zipcode

end

close Cur_Test
deallocate Cur_test
GO"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-10 : 19:58:09

Hi Carlos

Well, your main problem is you are using cursors. You don't need to, you VERY RARELY need to use cursors, and they just slow you down.

Here is a set based solution, it does something very similar to what you are doing, just a set at a time, rather than a row at a time.



--Update table where IDs match but data doesnt
Update table2
SET
	name = t1.name,
	email = t1.email,
	zipcode = t1.zipcode,
	date_row_mod = GetDate()
FROM
	table2
INNER JOIN table1 t1 ON t1.id = table2.id AND (
		t1.name <> table2.name OR
		t1.email <> table2.email OR
		t1.zipcode <> table2.zipcode
		)
		
--now grab anything where ID doesn't match and insert it

Insert into table2 (id, name, email, zipcode, date_row_mod)
SELECT
	table1.id, table1.name, table1.email, table1.zipcode, GetDate()
FROM
	table1
LEFT JOIN table2 ON table2.id = table1.id
	
WHERE
	table2.id is NULL

For some further reading, have a look at this
http://www.sqlteam.com/item.asp?ItemID=3876

Hope that helps

Damian

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-02-10 : 20:04:07

Assumption: Table is of this format:

ID, Name, Email, ZipCode

If this is the case, 2 statements can solve your pains... table1 is the table the data needs to be updated/inserted, table2 is the table with the new/updated information

INSERT INTO table1 (ID, Name, Email, ZipCode)
  SELECT  ID,
          Name,
          Email,
          ZipCode
    FROM  table2
   WHERE  ID NOT IN (SELECT ID FROM table1)
 
  UPDATE  table1
     SET  table1.Name = table2.Name,
          table1.Email = table2.Email,
          table1.ZipCode = table2.ZipCode
   WHERE  table1.ID = table2.ID

Just update all the information and save yourself a headache ;-).

... Onamuji

- Onamuji

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-10 : 20:08:30

Carlos,

You can do all this without a cursor........


--Update data changes...
--There are several ways to right this one.....
update T2
	set T2.id=T1.id,T2.name=T1.name,T2.email=T1.email,T2.zipcode=T1.zipcode,T2.date_row_mod=getdate()
from Table2 as T2
inner join Table1 as T1 on T2.ID = T1.ID
where T2.Name <> T1.Name or T2.email<> T1.email or T2.zipcode <> T1.zipcode


--Insert data not in table2
insert into table2 (id,name,email,zipcode,DateField,OtherField)
Select id,name,email,zipcode,getdate(),null from Table1 T1
where not exists (Select 1 from table2 where ID = T1.ID)

Obviously I haven't tested it but you should get the drift......

HTH

DavidM

Tomorrow is the same day as Today was the day before.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-10 : 20:11:46

quote:

Just update all the information and save yourself a headache ;-).

On 200,000 records?? Ouch....
What if only 1 record needs to be changed?
Nice log-file growth!

DavidM

Tomorrow is the same day as Today was the day before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources