469,097 Members | 1,549 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

Improving the Performance (Millions of Records!)

17
Table1
--------------------------------------------------------
a(varchar) | b(varchar) | c(varchar) | d(float)
---------------------------------------------------------
has value | has value | null | has value

---------------------------------------------------------

There are arround 13 million records in this table.
The combination of a and b is unique.

Table2
------------------------------------------------
a(varchar) | b(varchar) | c(float)
------------------------------------------------
has value | has value | has value

-------------------------------------------------

There are arround 13 million records in this table.
The combination of a and b is unique.


I have a simple procedure to put the c value of table2 into c value of table1.
The procedure looks like this
------------------------------------------------------------

create procedure my_procedure as
begin
declare @a nvarchar(255),@b nvarchar(255)
declare @c nvarchar(255)
declare c1 cursor dynamic
for select a,b,c from table2
open c1
fetch next from c1 into @a,@b,@c
while(@@fetch_status = 0)
begin
update table1
set c = @c
where a = @a and
b = @b
fetch next from c1 into @a,@b,@c
if @@error <> 0
print 'exception 1 ' + @@error
end
close c1
deallocate c1
end
-----------------------------------------------------------------------------------------------------

I have created index of type clustered the preformance of my procedure should increase. But still i am afraid to run the procedure as it took 17 hours to update 2000 matching records. Let me explain the problem very clearly.
There are 2 tables:


I have created Clustered index on the column a of both tables(note: column a has more repeating values)

I have to transfer the value in column d in Table2 to column c of Table1
Dec 5 '06 #1
4 2119
almaz
168 Expert 100+
If you want to write efficient code than:

first recommendation: do not use cursors unless it is absolutely necessary;
Second recommendation: do not use cursors...;
...
n-th recommendation: do not use cursors...

Your query can be rewritten with a simplest update statement:

Expand|Select|Wrap|Line Numbers
  1. create procedure my_procedure as
  2.   update table1
  3.   set c = table2.c
  4.   from table2
  5.   where table1.a = table2.a and table1.b = table2.b
Dec 5 '06 #2
ssrirao
17
thanks...

there is one case in which i have to use cursor
I have to get a unique value y from table x,
insert 10 values for each value of y into table a.
so a procedure with,
a cursor to get the value from table x
cursor1 = select y,z from x
while loop till @@fetch_statu = 0
{
insert into a (b,c) values (values from cursor1,c)
c++
if c = 10
set c=1
fetch from cursor1
}
something similar to this!!!
Dec 8 '06 #3
almaz
168 Expert 100+
thanks...

there is one case in which i have to use cursor
...
I didn't got the whole problem but as I see it still can be done without cursors:

Expand|Select|Wrap|Line Numbers
  1. declare @IDs table(ID int identity (1,1))
  2.  
  3. declare @i int set @i = 0
  4. while(@i<10)
  5. begin
  6.     insert @IDs default values
  7.     set @i = @i + 1
  8. end
  9.  
  10. insert a (b, c)
  11. select x.y, IDs.ID
  12. from x cross join @IDs IDs
Dec 8 '06 #4
ssrirao
17
thanks ....this worked faster
Dec 13 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

29 posts views Thread by Olaf Baeyens | last post: by
1 post views Thread by Robin | last post: by
4 posts views Thread by Hagen Rehr | last post: by
16 posts views Thread by Dylan Parry | last post: by
5 posts views Thread by Rahul B | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.