Which one is best 'CURSOR' or 'TEMP' table, to fetch multiple columns?????

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 - General » Which one is best 'CURSOR' or 'TEMP' table,...

Add to briefcase

25 posts, Page 1 of 3

Which one is best 'CURSOR' or 'TEMP' table, to fetch multiple columns?????

Expand / Collapse

Author

Message

sumith1andonly1

Posted Sunday, November 11, 2012 11:26 PM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

I want to fetch multiple columns from a table 'table' and compare with Table 'table2' .then update the value in 'table1' with values of 'table2'...
These are inside a procedure .

Which one is best Cursor or Temp table????????????

Post #1383507

Posted Sunday, November 11, 2012 11:40 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 222, Visits: 708

Temptable ı think

Post #1383509

Posted Sunday, November 11, 2012 11:41 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:06 AM
Points: 1,051, Visits: 1,442

Neither is better.

If your requirement is simply to compare data between two tables then you can do it as a set based operation without using a Cursor.

I would advise the use of Cursors only if don't have any other Set Based option to do the work.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden Wink

Wink

Post #1383510

sumith1andonly1

Posted Monday, November 12, 2012 12:07 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

My concern is the table 'Table1' is filled with large amount of data in minutes. and i need to update that table after the comparion with the 'table2'.

Insertion and updation may occur in parallel.

Post #1383511

Posted Monday, November 12, 2012 12:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:06 AM
Points: 1,051, Visits: 1,442

You don't need a Cursor to do that..............please post DDL and sample data for both the tables and precisely describe your requirement and someone may come up with a set based solution to your requirement.

Using a cursor/temptable for huge amounts of data will definitely suck.........that could be done in a few seconds using a set based query.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden Wink

Wink

Post #1383513

Posted Monday, November 12, 2012 12:23 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

You can use the UPDATE statement (you can join multiple tables together in an UPDATE statement) or the MERGE statement.

If it's really complex, you can add in a common table expression (CTE) or a temp table, but never ever use a cursor to do this. Especially if you have a lot of data.

But without a concrete scenario, we won't be able to help you out.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1383516

Posted Monday, November 12, 2012 12:30 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

sumith1andonly1 (11/12/2012)

Insertion and updation may occur in parallel.

How come ?
What there must be inserted (new records ) on which updation will be done.
if latency can be affordable you can use service broker OR script scheduled in job and for synchronous approach trigger can be used though again resource intensive concern

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1383518

sumith1andonly1

Posted Monday, November 12, 2012 12:42 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

here is the sample query

DECLARE @Addr1 bigint;

DECLARE @Addr2 bigint;

DECALRE @Time datetime;

declare GET_Details cursor
for
select COl_Addr1,COl_Addr2 ,COl_Time from TABLE2

open GET_Details;

FETCH NEXT FROM GET_Details INTO @Addr1,@Addr2 @Time;

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRANSACTION

UPDATE TABLE1 SET =COl_Addr2
where T1_address =COl_Addr1

COMMIT TRANSACTION

FETCH NEXT FROM GET_Details INTO @Addr1,@Addr2 @Time;

END

CLOSE GET_Details;

DEALLOCATE GET_Details;

Post #1383524

Posted Monday, November 12, 2012 12:51 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

UPDATE t1
SET ColAddr2 = t2.Col_Addr2
FROM
   TABLE1 t1
INNER JOIN
   TABLE 2 t2
   ON t1.T1_Address = t2.Col_Addr1

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1383528

Posted Monday, November 12, 2012 1:04 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:30 AM
Points: 803, Visits: 2,124

Looking at the Query you've posted, this will be VERY VERY slow in a cursor as its doing a RBAR itteration over a dataset, on 20-30 rows you might not notice it, but on a few thousand+ expect to go away make a cup of tea and read the morning paper while you wait for it to complete.

In answer to the original question, neither the Cursor or Temp table are the answer. SQL is a set based query language so it works best when manipulating data in sets.

Looking at what you've written you want to update the Address in all rows in Table1 from those that are stored in Table2.

So the simplest way to do this is a Single Update (I'm guessing at the join and update column as they are missing in your code, but it gives the general idea).

Update Target
Set Address2=Source.Col_Addr2
From Table1 Target
	JOIN Table2 Sourceon Target.Address=Source.Col_Addr1
Where
	Target.Address2!=Source.Col_Addr2

Koen mentioned the Merge statement which in its simplest format is

MERGE Table1 AS Target
USING Table2 AS Source 
	ON (Target.Address1=Source.Coll_Addr1)
WHEN MATCHED THEN
	UPDATE SET Address2=Source.Col_Addr2

One main factor in favour of the using a Merge is that it encourages coders to think more about the sets they are working with as it wont work with If your Source dataset has duplicated rows in it (based on the Join columns)

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1383537

« Prev Topic | Next Topic »

Add to briefcase

25 posts, Page 1 of 3

Permissions

Expand / Collapse