Rbar VS. Set Based Programming for SQL

RBAR vs. Set based programming for SQL

Set-based programming is based upon the mathematical concept of a set, and has operators that work on a whole set at a time. Procedural (RBAR) programming is based more on the traditional computer concepts of files and records. So to increase the salary of all employees in department X by 10%:

Set-based:

UPDATE employees SET salary = salary * 1.10 WHERE department = 'X';

Procedural (extreme example, pseudo-code):

OPEN cursor FOR SELECT * FROM employees;
LOOP
   FETCH cursor INTO record;
   EXIT WHEN (no more records to fetch);
   IF record.department = 'X' THEN
      UPDATE employees
      SET    salary = salary * 1.10
      WHERE  employee_id = record.employee_id;
   END IF
END LOOP
CLOSE cursor;

In the procedural version, only one employee row is being updated at a time; in the set-based version, all rows in the "set of employees in department X" are updated at once (as far as we are concerned).

Not sure this adds anything to what you will have already read in your links, but I thought I'd have a shot at it!

How slow are cursors really and what would be better alternatives?

I've changed out cursors and moved from over 24 hours of processing time to less than a minute.

TO help you see how to fix your proc with set-based logic, read this:
http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

Performance difference insert into select vs select and loop for insert

There is a SO answer that touches on this subject here:
RBAR vs. Set based programming for SQL

I'd say that the first approach is much faster than the second as it is set-based (as mentioned by marc_s).

Also, the second method involves switching to SQL and then back again for each iteration of the loop. I am not a Postgres user but in Oracle this would cause a performance overhead in the context switching alone, depending upon the number of records involved it could be very significant.

The first method is also the simplest and in most environments the simplest method is the easiest to support.

To answer your question though, the first method is faster than the second.

Is it possible to execute SQL stored in a column?

I don't think this is possible. You could tackle this problem in much the way you suggest in the question, by using dynamic sql. There is no TSQL or compiled query type you could use to short circuit they SQL compilation part of the process. If there was, you'd have to maintain some sort of schema binding to get any benefit.

Unless you can change the way this works, I can only suggest you do your best to minimise the number of rows that you must perform the "interpretation process" upon. Do all the independent filtering first.

---

Now that you've expanded your question ...

I'd suggest you write some code that will construct one statement that incorporates all the inline SQL operations you want to perform and targets your table of decimal values.

This way you can perform the operation as a set based operation rather than some sort of RBAR approach.

For each style SQL query

If pig_id unique column(e.g. with data type int) you can use loop without CURSOR

DECLARE @id int = (SELECT MIN(pig_id) FROM farm) 
WHILE (@id IS NOT NULL)
BEGIN  
  SELECT bacon, ham, pork, (face + guts + brains + testicles) AS 'sausage'
  FROM farm
  WHERE pig_id = @id

  SELECT @id = MIN(pig_id) FROM farm WHERE pig_id > @Id
END

OR

DECLARE @id int = 0
WHILE 1 = 1
BEGIN
  SELECT @id = (select min(pig_id) from farm where pig_id > @id)     

  IF @id IS NULL
  BREAK
  ELSE

  SELECT bacon, ham, pork, (face + guts + brains + testicles) AS 'sausage'
  FROM farm
  WHERE pig_id = @id
  CONTINUE
END

---

Related Topics