views:

47

answers:

2

Which, query performance wise, is more effective?
Considering T is a table, and PK is the primary key in the table T. Are they different or they are just a matter of choice?

select col1, col2 into :var1, :var2 
  from T 
 where PK = a

...or:

  1. EXEC SQL DECLARE aCursor CURSOR FOR select col1, col2 into :var1, :var2 from T where PK = a;
  2. EXEC SQL OPEN aCursor
  3. EXEC SQL FETCH aCursor

I think declaring a cursor to fetch a single row from a table based on primary key make less sense if the single row could be retrieved directly instead?

+1  A: 

If you are only fecthing a single row, i would NOT use a cursor, that seems like over kill

astander
Yes I agree with you, but the point here is Does it make any sense if other points like performance, effeciveness are considered. I always prefer to NOT USE THE CURSOR if I need to select values based on primary key. The single row will be comes free as the record has bee fetched on the basis of primary key.
Sachin Chourasiya
A: 

SELECT INTO will always be faster than using a cursor.

If you aren't performing anything while the cursor is open, there's no point to using the cursor at all. That's besides the fact that searching by the PK is guaranteed to return a single row, providing the PK value exists in the table.

OMG Ponies
Exactly, I am sure that while selecting the column from the table the corresponding value being selected exist in Primary key column
Sachin Chourasiya