Solved

Can I convert a cursor to a "Fire Hose" Cursor in my stored procedure?

Posted on 2007-11-19
7
635 Views
Last Modified: 2010-08-05
I have a stored procedure with many cursors in it. Sometimes it runs for 5-7minutes. Rather than re-writing thte stored proc. I would like to change the cursors to act as a "Fire Hose Cursor". I found an article on MSDN about server side cursors and firehose cursors and they said that:  It turns out that ODBC determines whether a server cursor or a Default Result Set should be used during prepare/execute time. If the multiple array binding happens before prepare/execute (row array size > 1), server cursor is used by ODBC. It could even re-prepare if the binding with array size > 1 is left before the next execute. So the solution is to temporarily set the row array size to 1 before the prepare/execute/re-execute and reset it back to the >1 value before fetching. That way, you get a fire hose cursor and the block-fetch behavior.

The following is a sample of one of my cursors(I did not paste the whole thing in here, it actually goes on for 207 fields):

DECLARE cur_total CURSOR FOR
            SELECT distinct r, person_id, inst_id, identity_id
            FROM #temp
            
            OPEN cur_total
            FETCH cur_total INTO @r, @person_id2, @instid2, @identity_id
            
            WHILE (@@fetch_status = 0) BEGIN

DECLARE cur_fields CURSOR FOR
   SELECT DISTINCT field_order, field_name
   FROM #temp
   where person_id = @person_id2
   and   r = @r
   and inst_id = @instid2
   and identity_id = @identity_id
   order by field_order, field_name

   OPEN cur_fields
   FETCH cur_fields INTO @field_order, @field_name
 
   IF (@@fetch_status = 0) BEGIN
         UPDATE #done
         SET field1_name = @field_name,
             field1_value = (select field_value
                         FROM #temp t
                         WHERE t.field_name = @field_name AND t.person_id = @person_id2
                and   t.r = @r and t.inst_id = @instid2 and t.identity_id = @identity_id)
    where person_id = @person_id2
    and   r = @r
    and inst_id = @instid2
    and counter = @identity_id
   END
   
 FETCH cur_fields INTO @field_order, @field_name

   IF (@@fetch_status = 0) BEGIN
         UPDATE #done
         SET field2_name = @field_name,
             field2_value = (select field_value
                         FROM #temp t
                         WHERE t.field_name = @field_name AND t.person_id = @person_id2
                 and   t.r = @r and t.inst_id = @instid2 and t.identity_id = @identity_id)
    where person_id = @person_id2
    and   r = @r
    and inst_id = @instid2
    and counter = @identity_id
   END
   
CLOSE cur_fields
  deallocate cur_fields

FETCH cur_total INTO @r, @person_id2, @instid2, @identity_id
                     
               END


CLOSE cur_total
  deallocate cur_total



Is there some way I can convert my cursor code so that it operates as a "fire hose" cursor?  Maybe by incorporating:

// set row array size to 1 just before execute
 
(SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, 0);

  // set row array size to > 1 just before fetch
 (SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)ROW_ARRAY_SIZE, 0);

Please let me know if something like this is possible?


0
Comment
Question by:TimFred
  • 3
  • 2
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
Hello TimFred,

the server side cursor processing is for the result sets returned by a stored procedure it has nothing to do with the
internal cursors within the stored procedure itself...
 
a brief examination of your code leads me to believe that this cursor is not required...
and should be replace by pure update statements with joins to the tables in question.

a re-design of your stored procedure is the best way forward.

Regards,

Lowfatspread
0
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
The "Server Side" and "Firehose" Cursors that ODBC uses are not the same thing as the Cursor declared in your Stored Procedure.

The ODBC Cursors deal with the resultset returned from your stored procedure after the stored procedure has completed.

So, if your stored procedure takes 5 minutes to execute, nothing you do in VB is going to speed that up.

So First, the obligatory moral statement:  Do NOT use CURSORS in SQL.  Try to rewrite your SP into something set based.

If it turns out that you really need to do this row by agonizing row, create your recordsets in VB and do the processing there instead of in SQL.

--
JimFive
0
 

Author Comment

by:TimFred
Comment Utility
Lowfatspread,

Can you give me an example of how you would convert the cursor example I gave to an update statement?

Thanks!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 15

Accepted Solution

by:
JimFive earned 250 total points
Comment Utility
Assuming that your tables look like:
CREATE TABLE #temp(r char(1),person_id int, inst_id int, identity_id int, fieldorder as int, field_name as varchar(200))
CREATE TABLE #done(r, person_id, inst_id, identity_id, field1_name, field1_value, field2_name,...)

And assuming that #done has been populated thus:
insert into #done(r, person_id, inst_id, identity_id)
      Select distinct r, person_id, inst_id, identity_id) from #temp

You could do:
update #done
      Set field1_name = (SELECT field_name from #temp
                        where #done.r = #temp.r
                        AND #done.person_id = #temp.person_id
                        AND #done.inst_id = #temp.inst_id
                        AND #done.identity_id = #temp.identity_id
                        AND fieldorder = 1),
            field1_value = (SELECT field_value from #temp
                        where #done.r = #temp.r
                        AND #done.person_id = #temp.person_id
                        AND #done.inst_id = #temp.inst_id
                        AND #done.identity_id = #temp.identity_id
                        AND fieldorder = 1),
            field2_name = (SELECT field_name from #temp
                        where #done.r = #temp.r
                        AND #done.person_id = #temp.person_id
                        AND #done.inst_id = #temp.inst_id
                        AND #done.identity_id = #temp.identity_id
                        AND fieldorder = 2),
            field2_value = (SELECT field_value from #temp
                        where #done.r = #temp.r
                        AND #done.person_id = #temp.person_id
                        AND #done.inst_id = #temp.inst_id
                        AND #done.identity_id = #temp.identity_id
                        AND fieldorder = 2)


However, if those assumptions are correct you could populate it in the insert by:

INSERT INTO #done (r, person_id, inst_id, Identity_id, field1_name, field1_value, field2_name...)
SELECT r, person_ID, inst_id, identity_id, MAX(CASE WHEN Fieldorder = 1 then field_name END) as field1_name, Max(CASE WHEN Fieldorder = 1 then field_value END) as field1_value), MAX(CASE WHEN fieldorder = 2 then field_name END) as field2_name ...
FROM #temp
GROUP BY r, person_ID, inst_id, identity_id

Even better, if that is what you need you can create it as a view instead of inserting it into a temp table.

--
JimFive
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
Comment Utility
IS YOUR problem that you need to pivot an input table to update a single row table in another table with 207 field , value combinations ?

if so try this...

(you may also want to investigate the PIVOT/UNPIVOT instructions in sql 2005)

good luck
ISNT YOUR UPDATE THIS REALLY ?
 

UPDATE #done

   SET field1_name = t.field1_name

      ,field1_value = t.field1_VALUE

      ,...

      ,field207_name = t.field207_name

      ,field207_value = t.field207_VALUE 
 

  From #Done as D

 Inner Join 

       ( 

Select person_id,r,inst_id,identity_id 

     ,max(case field_order when 1 then field_name else null end) as Field1_name

     ,max(case field_order when 1 then field_value else null end) as Field1_Value

     ,...

     ,max(case field_order when 207 then field_name else null end) as Field207_name

     ,max(case field_order when 207 then field_value else null end) as Field207_Value

 From #temp

 group by person_id,r,inst_id,identity_id

) as T

    on d.person_id = t.person_id

   and d.r = t.r

   and d.inst_id = t.instid

   and d.counter = t.identity_id
 
 
 

USE THESE CODE SNIPPETS TO GENERATE THE MISSING CLAUSES...
 
 
 

DECLARE @I INT

DECLARE @SQL VARCHAR(8000)

SELECT @I=1,@SQL=''

WHILE @I <=207

BEGIN 

   SET @SQL=@SQL+' ,FIELD'+CONVERT(VARCHAR(3),@I)+'_NAME = T.FIELD'

                +CONVERT(VARCHAR(3),@I)+'_NAME,FIELD'

                +CONVERT(VARCHAR(3),@I)+'_VALUE=T.FIELD'

                +CONVERT(VARCHAR(3),@I)+'_VALUE'

   IF DATALENGTH(@sql) > 7900

   BEGIN 

       PRINT @sql

       SET @SQL=''

   END   

END

pRINT @SQL

SELECT @I=1,@SQL=''

WHILE @I <=207

BEGIN 

   SET @SQL=@SQL+',MAX(CASE FIELD_ORDER WHEN '+CONVERT(VARCHAR(3),@I)+' THEN FIELD_NAME ELSE NULL END'

                +' AS fIELD'+CONVERT(VARCHAR(3),@i)+'_NAME' 

                +',MAX(CASE FIELD_ORDER WHEN '+CONVERT(VARCHAR(3),@I)+' THEN FIELD_VALUE ELSE NULL END'

                +' AS fIELD'+CONVERT(VARCHAR(3),@i)+'_vALUE' 

   IF DATALENGTH(@sql) > 7800

   BEGIN 

       PRINT @sql

       SET @SQL=''

   END   

END

Open in new window

0
 

Author Comment

by:TimFred
Comment Utility
please take a look at this article and let me know if I can implement this to speed up my cursors:

http://blogs.msdn.com/mssqlisv/archive/2006/03/24/560386.aspx
0
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
No, your problem is with SQL Cursors not ODBC Cursors.
--
JimFive
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now