Cursors on MySQL - Good or Bad [closed]

Question

I have always heard people saying bad about using cursors and this is especially in Microsoft SQL Server as they are very slow.

Is this the case with Cursors on MySQL as well? Does cursors in MySQL reduce performance as well? Can someone please advice on the usage of cursors in MySQL?

Burrton

This depends a lot on what alternative you have. – Gilley

alternatives? If you are asking with respect to decision on a different database type, then the answer is - none... :-( – Burrton

No. Alternatives as in 'if cursors are too slow for you, what else can you use?'. For example you might fetch data from database using some scripting language, process it there, and put pack to database (if required). This might, or might not be faster than a cursor - depending on many factors. – Gilley

Well its a cloud environment that I am talking about (Amazon) and any traffic from and to my servers are calculated. Now the routine that I am referring to could be processing a huge iterative task. I could use a java program that can fetch the records and do an iterative insert. But am not sure if it is worthwhile giving it a try to create a cursor to perform this task, as the whole task is actually done in one call, with one database connection saving from a lot of database resources. Running it from a scripting program, I am gonna be wasting a lot of network and db connections to do this. – Burrton

Answer 1

Most modern databases (including MySQL) are designed to perform set based operations. The problem with cursors is that they perform row based (or procedural) operations. Because of this you will almost always see a performance hits when you are using cursors to do a job that can be done without cursors on a modern DBMS.

Take a look at this article, which does a decent job going over the two. It is written with SQL Server in mind but most of the concepts apply.

Givens

Wow... What a... great link! I will try doing my best trying to understand “Set based” and “Procedural” approaches in SQL and why “Set based” approach is better. I believe I can earn a certificate after learning all that. – Trave

Answer 2

Just create and fill a temporary table. That is how most RDBMS's implement cursors anyway.

Pistachio

Answer 3

Cursors by nature are Iterative - they are definitely going to be slower irrespective of any database type. You should therefore do whatever to avoid them and try to find solutions using SQL queries. They are however there for problems which cannot be solved with queries - so use them only when absolutely necessary.

Hardboard

"definely slower" compared to WHAT? – Trave

umm compared to non-cursur based solution - "direct sqls", if there is one possible. i didnt knew if it wasnt this obvious – Hardboard

Oh, pardon, I don't know what 'direct sqls' are. Therefore I can't give any answer are they better or worse if compared each between. – Trave