August 26, 2009 at 8:17 am
I am trying to reduce the size of a database for a development project and have created a table with 700+ rows based with the following criteria:
CREATE TABLE [DeleteClientID] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RunQ] [bit] NULL
) ON [PRIMARY]
GO
We have a Stored Procedure called ClientDelete that I have been using to remove the desired ClientID's one at a time, but would like to be able to have this run in the background and delete all the ClientID's entered into DeleteClientID. When I run the following query (see below), I get 'The command(s) completed successfully.' bit it does not actually start running through the ClientID's (each ClientDelete takes from 1-10 minutes to run through all of our tables). Any assistance would be appreciated.
/*------------------------ QUERY START ---------------------------- */
BEGIN
DECLARE @my_count int, @client_id varchar(10)
SET @my_count = @@ROWCOUNT
SET NOCOUNT ON
/* ------------------------------------------------------------------------------ */
ONE_MORE_TIME:
IF (@my_count > 0)
GOTO START_ONE_MORE_TIME
ELSE
GOTO ALL_DONE
/* ------------------------------------------------------------------------------ */
START_ONE_MORE_TIME:
BEGIN
SET @my_count = @my_count - 1
BEGIN
SET @client_id = NULL
SET @client_id = (SELECT TOP 1 ClientID FROM DeleteClientID WHERE RunQ = 0)
IF @client_id IS NOT NULL
BEGIN
UPDATE DeleteClientID SET RunQ = 1 WHERE ClientID = @client_id
IF (@client_id IS NOT NULL)
GOTO PROCESS_ONE
ELSE
GOTO PROCESS_ABORT
END
ELSE
BEGIN
GOTO ONE_MORE_TIME
END
END
/* ------------------------------------------------------------------------------ */
PROCESS_ONE:
BEGIN
EXEC ClientDelete @client_id, '43842', '222.222.222.222', 'delete'
GOTO ONE_MORE_TIME
END
/* ------------------------------------------------------------------------------ */
PROCESS_ABORT:
BEGIN
UPDATE DeleteClientID
SET RunQ = 1
WHERE ClientID = @client_id
GOTO ONE_MORE_TIME
END
END
/* ------------------------------------------------------------------------------ */
ALL_DONE:
SET NOCOUNT OFF
END
/* ------------------------------------------------------------------------------ */
/*------------- QUERY END ---------------- */
August 26, 2009 at 8:22 am
I could be mistaken, but it looks to me like this part:
DECLARE @my_count int, @client_id varchar(10)
SET @my_count = @@ROWCOUNT
SET NOCOUNT ON
/* ------------------------------------------------------------------------------ */
ONE_MORE_TIME:
IF (@my_count > 0)
GOTO START_ONE_MORE_TIME
ELSE
GOTO ALL_DONE
Will always skip to the ALL_DONE section, which will turn NOCOUNT OFF and then report that the proc completed successfully.
That's because @@ROWCOUNT should be 0 at this point, unless there's a select or other action before it that would make it something else.
If you can post the code for the ClientDelete proc that it calls, I can probably help you turn the whole thing into a single-pass proc that will do the whole process MUCH more efficiently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 8:34 am
Unfortunately, the ClientDelete stored procedure contains significant information about the database structure for my entire application and I can't post that without breaking security rules.
Could I set @@ROWCOUNT = Select Count(*) from DeleteClientID?
August 26, 2009 at 8:38 am
You'd want to use:
SET @MyCount = (SELECT count(*) FROM DeleteClientID WHERE RunQ = 0);
Use that at the beginning instead of setting to @@ROWCOUNT. That should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 8:42 am
Thanks! That seems to be working. I appreciate the guidance.
August 26, 2009 at 8:44 am
A better solution would replace that whole proc with this:
declare @Client_ID varchar(10);
declare Deletes cursor local fast_forward for
select ClientID
from DeleteClientID
where RunQ = 0;
open Deletes;
fetch next from Deletes
into @Client_ID;
while @@fetch_status = 0
begin
update DeleteClientID
set RunQ = 1
where ClientID = @Client_ID;
exec ClientDelete @Client_ID, '43842', '222.222.222.222', 'delete';
fetch next from Deletes
into @Client_ID;
end;
close Deletes;
deallocate Deletes;
Cursors are generally frowned upon, but when you need to repeatedly call a proc for all values in a recordset, they're a better solution than complex GOTO controls.
Try that, see if it does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply