Should I use SQL cursors here?

Question

I have recently read about how cursors should be avoided. Well, I would like to know if my usage of them is appropriate.

I am creating a scripting engine that will work online(embedded in a page, server-side) This scripting engine will be used by "advanced" end users of this product. The product works very heavily with the database however, and the scripting language is C-like, but simplified to where it also resembles PHP. For databases I basically want a syntax like this, as it is the most consistent syntax creatable within the language without the end user having to hand write SQL code(if we are going to make them do that, why can't they just skip the scripting engine as its there to make life simpler). The syntax is something like this:

declare DataSet $data("tablename","OtherID="+$oid);
//Dataset(string tablename,string where_clause_addon)
$data["field1"]="the data of field... ";
$data.Next();  
$data["field1"]="The data of the next row";
$data[10]["field1"]="The data of the 10th row";

I control this internally by creating a global cursor for each DataSet (I only use 1 connection in the application) and then letting the global cursor keep track of the current row position(its a SCROLL and UPDATE cursor also). This makes my life much simpler as otherwise I would be forced to write my own SQL controls to combat .Net's sucky DataReader.

Is this usage of cursors an OK one? Note that the page with these scripts will not be world wide accessible, its only for clients(so probably only like 3-10 users at once).

Does anyone see a better method of keeping track of the current variable location? (as these are able to address tables of unknown schema)

Also, would I have any problems with concurrency using cursors like this? (My docs say cursors are global to the connection, and each page request makes a new connection on the spot, so users aren't sharing connections)

Answer 1

"I have recently read about how cursors should be avoided."

They can't be avoided.

"I've read more that cursors shouldn't necessarily be avoided, but rather that cursors are slow and should only be used in certain circumstances."

Correct -- in certain situations.

Use SQL statements to do as much as possible. Don't invent your own SELECT processing logic by opening a cursor and doing a bunch of if-statements: use a WHERE clause. Don't invent your own GROUP-BY processing by opening a cursor and reading each row: use a GROUP BY clause. Don't invent your own join algorithm by using nested cursor loops: use a proper JOIN clause.

Don't invent your own SQL using cursors. Use SQL.

Generally, the "don't use cursors" folks are saying "Don't reinvent SQL algorithms by using a cursor". This is not a broad, vague "don't use cursors". It's very specific advice: "Learn SQL, don't work around gaps in your knowledge by writing cursor loops."

So long as you're not reinventing an existing SQL algorithm, there are many things for which you must use cursors.

Don't fetishize over "avoiding cursors". Do fetishize over doing as much in "pure SQL" as is reasonable.

Don't engage in premature optimization as you wring your hands over cursor/no-cursor programming. Simply write the best SQL you can and benchmark performance early and often.

If you can't figure out the best SQL, ask here for the best SQL -- without fetishizing over cursors.