| Author |
 Topic  |
|
|
tpiazza55
Posting Yak Master
162 Posts |
 Posted - 12/29/2006 : 08:18:02
|
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
|
to run the query without the insert delete takes a 0.01 s
its run for 10 min with the inserts |
 |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 12/29/2006 : 08:33:13
|
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" |
|
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 12/29/2006 : 09:04:42
|
| had that originally -- couldnt figure out how to get the delete statement to work with the insert and the select as the values |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 12/29/2006 : 09:08:29
|
Post proper INSERT and SELECT statement here and we can provide some help.
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 01/02/2007 : 09:44:29
|
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
|
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 01/02/2007 : 09:51:16
|
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" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/02/2007 : 10:42:56
|
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 |
|
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 01/02/2007 : 12:48:20
|
| doesnt that run the same query twice? one to get the temp table and once to get the id values |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/02/2007 : 13:07:56
|
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 |
|
|
|
tpiazza55
Posting Yak Master
162 Posts |
Posted - 01/05/2007 : 09:59:11
|
that got it working thanks
|
|
|
| |
Topic |
|
cursor question
Forum Locked
