473,558 Members | 2,874 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Improving the Performance (Millions of Records!)

17 New Member
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_s tatus = 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 2386
almaz
168 Recognized Expert New Member
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 New Member
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 Recognized Expert New Member
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 New Member
thanks ....this worked faster
Dec 13 '06 #5

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

Similar topics

29
1768
by: Olaf Baeyens | last post by:
Because of historical reasons, I have both C# and C++ managed/unmanaged code mixed together in my class library. But I prefer to port code to C# since it compiles faster and the syntax is much more readable so I can do more in less time. The big question now, will I gain/lose performance, given the fact that I create pure managed code, if...
1
1137
by: Robin | last post by:
For an asp.net project that is deployed to a load balanced web servers, are there any performance changes that can be made in .Net runtime or IIS 6? Also are there any additional tips for reviewing the asp.net (VB) code for improving performance?
3
1905
by: Hadley Willan | last post by:
Hi all, I am using some views now to put together a particular format for my Java client factory to produce Java Beans from the database. Because we support internationalisation we are representing values as an id then storing their multiple languages in unicode to support the same repesentation at the database. This format is:
4
1341
by: Hagen Rehr | last post by:
Hello Newsgroup, the following Code reads 60.000 records from an Access Datatable: static void Main(string args) { String connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb"; OleDbConnection conn = new OleDbConnection(connString); conn.Open();
16
1660
by: Dylan Parry | last post by:
Hi, I used SQL Server 2005 on my development machine, and whilst this machine isn't as powerful as the live server, it does at times seem a little slower than I would expect. So I've been wondering if there is any way for me to tune the machine so that SQL Server is better able to make use of the resources? I am using WS2003. Short of...
5
6697
by: Massimo | last post by:
The iussue: Sql 2K I have to keep in the database the data from the last 3 months. Every day I have to load 2 millions records in the database. So every day I have to export (in an other database as historical data container) and delete the 2 millions records inserted 3 month + one day ago. The main problem is that delete operation take...
0
1203
by: anchiang | last post by:
Hi All, I need to do bulk processing several tables that contain few millions records to generate report. there is 4 tables say A, B, C, D A is the transaction table, I need to retrieve data from here where the date is between 1/1/200x to 31/12/200x. but the date is not indexed. about 3million rows to process Table B is customer details....
5
2427
by: Rahul B | last post by:
Hi, We have been migrating to DB2 and it has been the trend that the application has become somewhat slow. It could be because of the application problems or it could be because i am not very aware of how to try to improve the performance of the DB2. The only thing i am aware is about the RunStats utility. Can somebody give me various...
0
7629
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7835
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7914
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6183
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5455
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2045
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
869
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.