April 16, 2008 at 2:54 am
Hi,
I have been reading BOL, and note that the rowcount should not be used, and that future releases will may support it. The recommendation is to use TOP. But how can I be sure that when using TOP to delete, I will get the correct rows. I mean, once I have processed my records, I want to delete from temp table, so I can get the next batch:
WHILE SELECT COUNT FROM #Temp > 1
BEGIN
INSERT NewClients(ClientID) Select top 1000 From #Temp order by 1
Exec usp_RunStuff @CheckID
DELETE top 1000 #Temp --order by 1
END
Currently, I am using:
SET ROWCOUNT @batchsize
SET @rc = @batchsize
WHILE @rc = @batchsize
BEGIN
BEGIN TRAN
INSERT NewClients(ClientID)
SELECT C.ClientID FROM Temp C
LEFT JOIN NewClients NC
ON C.Client = NC.Client
WHERE NC.Client IS NULL
SET @rc = @@Rowcount
EXEC usp_RunStuff @checkMode
COMMIT
END
April 16, 2008 at 5:44 am
Either way you go, to ensure that you're getting the exact same result set you need to use ORDER BY to ensure that the 1000 rows you get is the same. ROWCOUNT or TOP could return different results for the same query without the ORDER BY statement. Once that's in place, TOP should work just fine in the same way that you're working now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2008 at 8:37 am
you could also use the OUTPUT clause:
[font="Courier New"]declare @batch table( CliendId ...)
WHILE SELECT COUNT FROM #Temp > 1
BEGIN
INSERT NewClients(ClientID) Select top 1000 From #Temp
OUTPUT inserted.ClientId INTO @batch
Exec usp_RunStuff @CheckID
DELETE #Temp FROM #Temp T join @batch on ...
END [/font]
still better alternatives would be to either add an identity column to #Temp during its creation and range your inserts based on it; or if ClientID is numeric, range your inserts based on ClientID.
[font="Courier New"]select ..., identity(int) as row_num into #Temp -- add identity while creating the table
create index x0 on #Temp(ClientID|row_num)
...
declare @Last int, @current int, @batch_size int
set @batch_size = 1000
select @current = min(ClientID|row_num), @Last = max(ClientID|row_num) from #Temp
while @current <= @Last
begin
INSERT NewClients(ClientID)
Select ClientId From #Temp
where ClientID|row_num between @current and @current+@batch_size
Exec usp_RunStuff @CheckID
set @current = @current + @batch_size + 1
end[/font]
April 17, 2008 at 1:22 pm
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
DROP TABLE #temp2
SELECT * INTO #temp
FROM sys.columns
SELECT COUNT(*) FROM #temp
SELECT TOP(500) *
INTO #temp2
FROM [#temp] ORDER BY [#temp].column_id
;WITH x AS (SELECT TOP(500) * FROM [#temp] ORDER BY [#temp].column_id)
DELETE FROM x
SELECT COUNT(*) FROM #temp
SELECT COUNT(*) FROM #temp2
SELECT COUNT(*) FROM
(
SELECT * FROM #temp
UNION ALL
SELECT * FROM [#temp2]) AS x
example:
April 17, 2008 at 1:25 pm
FYI, you do need to make sure that the order by's on the insert and the CTE match. If they don't you will delete the wrong rows.
i.e. It should be on a UK of some sort.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply