SQL Loop Use Case - Not Sure How or Why These Are Useful

Question

What is a good use case for writing a loop in a SQL function? I can see a loop as useful for generating random data, but apart from that, I can only think of a few other reasons.

For example, you may receive a list of data that you need to iterate over and insert new records based on each item.

But beyond that, it's really hard to imagine what a loop would be used for. Part of the difficulty is that a lot of things that you may think to use a loop for, like deleting odd number of rows/updating odd number of rows, you can do in a query.

Answer 1

situations

Loops can be quite useful in many scenarios. I understand the desire to avoid them when set-based solutions are available, but not every interaction with a database can or should be done in a single query.

batching

One thing that may be unpleasant is large data modifications. Increased lock-times, transaction log growth, etc. are inconvenient at best, and may lead to all sorts of things no one wants to deal with:

• Long blocking chains
• Running out of worker threads
• Filling up a drive

This is a good article about them:

• Take Care When Scripting Batches

maintenance

When taking backups, checking for corruption, or updating statistics, you may not want to do them in parallel in many circumstances.

A great example of how loops can be used to deal with this are Ola Hallengren's scripts.

More recently, Ola has added options to parallelize certain activities. This is likely a response to greater hardware capabilities than anything else, though.

sampling

There are times when you'll want to sample or run data collection, and a loop is pretty well-suited to that.

I use them in my stored procedure, sp_HumanEvents, in several places. One of them is to pull data out of Extended Events sessions to log them to tables.

You'll also see loops via cursors in sp_WhoIsActive to populate

• query text
• query plans
• locking and blocking
• agent job names

placemat

Loops and cursors are sometimes the best options for queries as well. Here are a couple examples from other Q&A on this site:

• Rejoining range seek on nullable composite index?
• Most cost efficient way to page through a poorly ordered table?

While they do have a bad reputation for good reasons, most of the time they're just misunderstood.

Answer 2

For example, you may receive a list of data that you need to iterate over and insert new records based on each item.

Even this kind of case most times can be accomplished with a relational solution instead of an iterative one.

The only time I've found using loops in SQL useful is for when I explicitly want to break out my units of work into chunks so that I can control the rate at which they are processed. Generally this would be an adhoc type of scenario.

For example, if I needed to update an entire large Table with a data change that I don't want escalated to a Table level lock, because it is heavily read from, I may find better overall performance for my instance by limiting the length of blocking at one time by breaking the operation out into chunks and looping the query for each chunk. In some database systems you can even issue a delay command between each iteration (e.g. WAITFOR DELAY in SQL Server) to control the length of time to pause between each chunk of work.

But even such a scenario may arguably be obsolete with alternative solutions like proper isolation levels, to reduce / eliminate blocking and deadlocks.

Answer 3

If the logic to be run is implemented in a stored procedure, and that SP has scalar parameters as opposed to table valued parameters, then a loop is necessary.

Answer 4

A practical example of why I used loops in MySQL.

Consider the following table which will have millions of data :

CREATE TABLE `vicidial_list` (
  `lead_id` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `list_id` bigint(14) unsigned NOT NULL DEFAULT 0,
  `phone_number` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`lead_id`),
  KEY `phone_number` (`phone_number`),
  KEY `list_id` (`list_id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

The table engine should be MyISAM because of the ViciDial/Asterisk works on MyISAM and the locks on the tables should be minimal.

It happens that I need to delete from specific list_id which might have 1 million records. I cannot use the following command to delete all the entry from the list_id 1000 , it will lock the table for a not acceptable time.

delete from vicidial_list where list_id=1000; 

I build a SP to delete the rows . The procedure will delete 1000 rows and wait 20 seconds (so other waiting queries on vicidial_list to be executing) before the next delete

DELIMITER //
CREATE PROCEDURE delete_list_id()

BEGIN
main: REPEAT
  DELETE FROM vicidial_list  WHERE list_id=1000 LIMIT 1000;
  SELECT SLEEP(20); 
  UNTIL row_count() = 0 END REPEAT main;
END; //
DELIMITER ;

Answer 5

None really.

Even the example you gave of inserting multiple rows does not need a loop.

Outside of some obscure DBA work involving backups, or multiple servers, and the like, it's almost never necessary and you shouldn't waste your time learning how to write one.

There are also some very rare occasions where some kind of iterative calculation is necessary, and recursive CTEs can be slower than a cursor. An example of one is given here. Once you get the hang of regular SQL, on the rare occasion you find it absolutely necessary to write one then you will find it easy.

Outside of these instances, do not write a loop, and if you find yourself writing one then question it and question again.