Advantages and Disadvantages of Cursor?
- 15 Answers
- 97859 Views
- Polytechnic, TCS, Zenith, I also Faced
- E-Mail Answers
Answer Posted / nikunj padaliya
Advantage of Cursor:
Row-by-row operations: Cursors are best used when performing
row-by-row operations that can't be accomplished with
set-based operations (i.e, when you need to fire a stored
procedure once per row in a table).
Efficiency: The query optimizer automatically selects the
appropriate query plan, so the developer does not need to
design a complex algorithm to access the required data.
Adaptability: As data changes or indexes are added or
dropped, the query optimizer automatically adapts its
behavior by using alternative plans.
Fewer errors: Instead of the developer handling data and
algorithms in the application, the SQL Server Compact 3.5
Database Engine natively supports the required operations.
--------------------------------------------------
Disadvantage of Cursor:
Resources consumed by cursors: it occupies memory from your
system that may be available for other processes. Poorly
written cursors can completely deplete available memory.
Speed and performance issues: Cursors can be faster than a
while loop but they do have more overhead. If your cursor
will not be updating the base tables, use a FAST_FORWARD
cursor for optimal performance. The problem with cursor
speed is that, in many cases, the operation can be more
efficiently written as a set operation or perhaps in a while
loop. It's these cursor rewrites that lead to the impression
that cursors are evil – or cursed.
Another factor affecting cursor speed is the number of rows
and columns brought into the cursor. Time how long it takes
to open your cursor and fetch statements. If it's lengthy,
look carefully at your cursor logic; see if you can remove
columns from the declare statement, and change your where
clause in the declare statement to only return rows the
cursor needs. If the fetch statements themselves are lengthy
or consuming too much IO or CPU, look at the cursor declare
statement and ensure you have optimal indexes in place on
your base tables or temporary tables.
Wrong tool for the wrong task: Cursors are frequently the
wrong tool for the wrong task. They're used for
quick-and-dirty programming when a developer does not have a
good understanding of set operations or they're used for the
wrong task entirely.
Subtle errors:
Cursors sometimes introduce subtle errors. We already
looked at a few:
1.Failing to check the value of @@Fetch_Status
2.Improper indexes on the base tables in your results set or
FETCH statement
3.Too many columns being dragged around in memory, which are
never referenced in the subsequent cursor operations
(probably the result of legacy code)
4.WHERE clause that brings too many rows into the cursor,
which are subsequently filtered out by cursor logic.
| Is This Answer Correct ? | 44 Yes | 13 No |
Post New Answer View All Answers
Does table partitioning improve performance?
What is compression - row-level and page-level compression?
A user is a member of the public role and the sales role. The public role has select permission on all the tables. The sales role does not have select permission on some of the tables will the user be able to select from all tables?
What are parameterized reports? What are cascading parameters in ssrs reports?
What is the difference between DataRow.Delete() and DataRow.Remove()?
What command would you use to add a column to a table in sql server?
What is the use of keyword with encryption.
To automatically record the time on which the data was modified in a table, which data type should you choose for the column?
What is report rendering ?
What are the source of constraints?
What is raid and what are different types of raid levels?
What the different types of Replication and why are they used?
How to find the version of sql server? : sql server database administration
Explain ranking functions?
What is a trigger and types of a trigger?
