Select Statement Vs Loop
-
We can use select statement for retrieving particular set of values or we can use cursors with loop. By the select statement retrieval is much more faster than that of cursors. why? because internally SQL engine covert the select statement into loops and then fetch records from the tables. Then why that loop is faster than our loop?
-
Chiranjib - your understanding of data retrieval is not correct - read up on table scans, index scan & index seek and that'll explain how data is retrieved at various times depending on indexes, where clause filters, joins etc..
**ASCII stupid question, get a stupid ANSI !!!** -
SELECT can perform some operations on parallel threads, a cursor forces operations to be serialized on one thread.
Cursors consume extra resources on the server and require more overhead. Creating a cursor requires building a temp table to hold either all the cursor data or at least the keys of all the rows in the cursor (depending on cursor type).
SELECT returns the set of rows at one point in time. Cursors are expected to exist for a longer period of time (certainly longer than one statement), so the server might have to (depending on the type of cursor you use) keep track of rows which have been updated, deleted, or inserted by another user after your cursor was opened. All of these other operations are therefore slower as long as the cursor is open.
SELECT releases locks on the data very quickly (usually), while cursors require more locks held for extended period so there is more potential for blocking.
There is some overhead in setting up the operations required to execute a statement. Doing this once for SELECT is more efficient than doing it many times in a loop for one row at a time.
-
I'd go further than that. His understanding of relational database is wrong. The goal of a database is to hide the data retrival task/methods from the user. The user only has to ask for the data and not worry how to database is storing/retrieving it. You should really let the db do his job and concentrate on the best way to ask for the data as Sushila already said (to avoid scans and bookmark lookups...)
You can read more on this page : http://www.itworld.com/nl/db_mgr/05072001/
This is the rule that really applies to this question :
Rule 8: Physical Data Independence
The user is isolated from the physical method of storing and retrieving information from the database. Changes can be made to the underlying architecture ( hardware, disk storage methods ) without affecting how the user accesses it.
-
I tried to list some of the obvious reasons why looping through a cursor will be slower than a SELECT, but I forgot to warn you that you risk running afoul of the CursorsAreEvil religion.
-
It's not a religion, it's a fact and we're religious about it
. Anyways cursor should be banned (almost) from the sql developpers tools arsenal. Only an experienced admin will know when it's necessary to use one, either because there's no other way or because it's faster than the set based approach (albeit, extremely rare cases). -
ha ha! Yes indeed! You may cursor and loop all you want but if you run afoul of the fanatical members of the CursorsAreEvil religion (or those that are religious about the fact) you may well consider your db days numbered..
This alone should make users of loops and cursors quake in their shoes...
**ASCII stupid question, get a stupid ANSI !!!** -
This is most appropriate here, especially if you don't wanna stop using them :
A quote from rudy komacsar
time for:
exec sp_update_resume
possibly time for:
exec sp_distribute_resume
-
The guy asked why a cursor is slower and deserved some facts, not to be told "your understanding is not correct" or "your understanding is wrong".
I may be a cardinal in the CursorsAreEvil religion myself, but I don't think I'll convert anyone without explaining some of the reasons for it.
-
Scott - PAX!
I was specifically responding to "internally SQL engine covert the select statement into loops and then fetch records from the tables...."
**ASCII stupid question, get a stupid ANSI !!!** -
He specified his loops over sql server internal loops. There's no notion of set based approach in the question. Maybe I didn't understand the question correctly... wouldn't be the first time
.The real answer here is learn to use set based approach when working with sql servers.
-
I think his comments were directed to me
. -
I think it was to both of us...I said his understanding was incorrect...you said it was wrong....
let's not start an "I said you said but he said..." war!
**ASCII stupid question, get a stupid ANSI !!!** -
Yes, I meant both of you, but it was not supposed to be a flame. Sometimes I come off as more serious than I intend.
The original question was why are cursors slower, and it showed a limited understanding of the inner workings of SQL. I thought enlightenment would be more useful than criticism (albeit mild, well-intentioned criticism).
Tell him why they are slower. THEN tell him not to use them, or face our wrath.
-
Maybe I need to use more smileys.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply