SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 cursor question
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

tpiazza55
Posting Yak Master

162 Posts

Posted - 12/29/2006 :  08:18:02  Show Profile
i have a stored procedure that is taking forever to run.

it joins 5 tables and returns 5k results which i then need to insert into another table and then delete from another table

couldnt think of a way to do it without a cursor

what im worried about is whether its running the query over and over again -- can someone confirm its not or tell me a quicker way to do it

have the following:

Declare c1 CURSOR FOR
SELECT bunch of stuff FROM mytable

INNER JOIN
INNER JOIN etc

get 5k results

OPEN c1

Declare variable

FETCH NEXT FROM c1

INTO @variable

WHILE @@FETCH_STATUS = 0

BEGIN
insert statement
delete statement
END

CLOSE c1
DEALLOCATE c1
GO

tpiazza55
Posting Yak Master

162 Posts

Posted - 12/29/2006 :  08:22:51  Show Profile
to run the query without the insert delete takes a 0.01 s

its run for 10 min with the inserts
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 12/29/2006 :  08:33:13  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
Why you need cursor for this? Why not do it directly?

Insert into DestTable(col1, col2, col3, ...)
Select col1, col2, col3,.. from SourcTable
Join Table2 on ...
Join Tabl3 on ...


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 12/29/2006 :  09:04:42  Show Profile
had that originally -- couldnt figure out how to get the delete statement to work with the insert and the select as the values
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 12/29/2006 :  09:08:29  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
Post proper INSERT and SELECT statement here and we can provide some help.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/02/2007 :  09:44:29  Show Profile
what i want to do is this

insert into table_insert(value1, value2)
(select value1, value2 from table_exclude)

delete from table_exclude where id = "the id from the select of the insert statement)

basically i want to do an insert and then delete the record from the tablethe select statement came from
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 01/02/2007 :  09:51:16  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
You can temporarily insert IDs of the data to be inserted in the temporary table and make use of temporary table in DELETE statement.

create table #temp
(
  [ID] int
)

Insert #temp
select [ID] from table_exclude Where ...

insert into table_insert(value1, value2)
(select value1, value2 from table_exclude Where ...)

Delete E
From table_exclude E Join #Temp T
on E.[ID] =  T.[ID]
 
Drop table #temp


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/02/2007 :  10:42:56  Show Profile
If you want to be sure that both, or neither, work you need something like:

create table #temp
(
  [ID] int
)

Insert #temp
select [ID] from table_exclude Where ...

BEGIN TRANSACTION
insert into table_insert(value1, value2)
(select value1, value2 from table_exclude Where ...)
IF @@ERROR <> 0 GOTO GotError
Delete E
From table_exclude E Join #Temp T
on E.[ID] =  T.[ID]
IF @@ERROR <> 0 GOTO GotError
COMMIT 
GOTO DoneTrans

GotError:
ROLLBACK

DoneTrans:

Drop table #temp

Kristen
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/02/2007 :  12:48:20  Show Profile
doesnt that run the same query twice? one to get the temp table and once to get the id values
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/02/2007 :  13:07:56  Show Profile
Yes - personally I would get all the data I needed into #TEMP, use that for the INSERT, and then JOIN #Temp to table_exclude for the DELETE.

The issue that I expect Harsh is addressing is to be sure that all the data / identities for the INSERT and the DELETE are the same - and NOT possibly variable in case something else has inserted some new rows into table_exclude between the INSERT and the DELETE - ensuring that an identical set of data is INSERTed into table_insert as is DELETEd from table_exclude

Kristen
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts

Posted - 01/05/2007 :  09:59:11  Show Profile
that got it working thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000