[dba-Tech] SQL Question

[Stuart McLachlan]

I assume that it looks like:

WHILE (1=1)
BEGIN
....
IF..... BREAK;
....
END

It's a common way to  create an infinite loop since SQL doen't have an absolute.  DO_LOOP 
construct. But it s inefficent because   (1=1) has to be evaluated on  every iteration.

Again,  without knowing what's in the BEGIN... END loop, it's hard to tell, but from how long it 
takes, I'd suspect that it is using a cursor, stepping through a large recordset  and deleting 
records one at a time.  This is often called RBAR programming ( "Row By Agonising Row") 
and is notoriously slow with large recordsets.  Far better to try using one or a series of set 
based deletions i.e DELETE FROM.... WHERE...

It may be worth googling SQL RBAR for lots of discussion on the subject. :-)


 
On 16 Jul 2018 at 8:02, Susan Harkins wrote:

> From what I've read, it's for testing -- a placeholder of sorts. I'd
> delete it and try again. 
> 
> Susan H. 
> 
> 
> We are running a script to delete a few million records from a CRM SQL
> Server. This bit is confusing us "While 1 =1" . We can't understand
> how this would ever evaluate to anything other than 1 =1?
> 
> IF EXISTS (SELECT name from sys.indexes
> WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
>       DROP Index
>       AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
> GO
> CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
> ON [dbo].[AsyncOperationBase]
> ([StatusCode],[StateCode],[OperationType]) GO
> 
> while(1=1)
> begin
> 
> We also ran the full script for 34 hours and it ran successfully but
> turns out left 22million records undeleted all of which meet the
> deletion criteria!
> 
> It's a Microsoft script. Confusing.
> 
> Martin
> 
> 
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
>