Using Cursors over PHP loops

Question

A curious question posed itself when I was planning a new batch of additions to The Application(TM) (as in, the company-spanning behemoth). Having a result set for each row of which there needs to be a procedure called, is it more efficient to do the whole thing in MySQL by creating a procedure that loops a CURSOR and calls a procedure, or simply to fetch the set in PHP and do it "manually"? The procedure would run few times for a short duration (~1s for each call, around 2 - 3 calls per procedure, a few times per day).

The reason I'm asking this is that I'm wondering if anyone has any experience that CURSORS would be less efficient than the overhead of sending the data between MySQL and PHP multiple times. The other concern is that it uses one of the core tables, so I would like to minimize hits to it.

Yes, so far it's a stored procedure, with average expected runtime of around a second. — Naltharial
– Naltharial, Commented Sep 16, 2011 at 7:51

Ray · Accepted Answer · 2011-09-16 21:29:00Z

1

You could create a stub class that implements the PHP Iterator interface (http://php.net/manual/en/class.iterator.php). When you instantiate the class you implement with the Iteretor interface it can hold the connection and keep track of the place of the CURSOR. When you create a new instance of this object, you can run the initial query and get the initail cursor. Then, when you use a foreach on the object, it will call the objects custom implementation of the next() method which you'll code to to move the CURSOR ahead and return the current value(s) as needed for the loop.

answered Sep 16, 2011 at 21:29

Ray

41.6k22 gold badges110 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naltharial Over a year ago

While not exactly the performance answer I was looking for, your answer did provide a fresh input on my problem, so thank you.

Collectives™ on Stack Overflow

Using Cursors over PHP loops

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related