SQL Cursor Query or Two Separate Queries: Which Would Be More Efficient?

I'm working on trying to make a bit of code dealing with a message tree in PHP more efficient. The data is stored in a SQL database, and at present has to do 2 SQL queries per message, one to find the data and one to update the data. I think I have a way to get this done with a single query, but it uses cursors which I've heard are much slower than other SQL methods.

开发者_StackOverflow社区

Does anyone know whether it would still be more efficient to do one SQL query which uses cursors, or would it be better to stick with the current method which uses a select and an update query per message and PHP to tie the queries together?

---

Agree with @Catcall - without seeing the code, it's hard to answer this question meaningfully.

However, in broad terms, the reason people often say cursors are slower than other SQL methods is because some developers use cursors where a set based operation would work.

For instance, if your plan is (in pseudo code, in the absence of actual DB engine or schema)

create cursor RecordsToUpdate
as
select *
from UserTable
where name like 'Codd%'

foreachRecord in RecordsToUpdate
  set record.lastUpdatedDate = today
next

It's (hopefully) obvious that you can achieve the same by executing

update UserTable
set lastUpdatedDate = today
where name like 'Codd%'

However, there are cases where set-based operations just aren't possible, and then - again, broadly speaking - it will be more efficient to create a cursor, execute your logic, and close the cursor - ideally within a stored procedure or something. This approach limits the network overhead of sending data back to your PHP server.

Having said all that - in most cases, you won't notice the difference in real-world scenarios. Also, it's worth pointing out that if your database is heavily loaded, or your query executes slowly (e.g. by iterating over a cursor with millions of records), this approach may create a DB server bottleneck. Broadly speaking, you want to avoid having long-running jobs on your database if at all possible - it's better to split them up into lots of small jobs.