Solved

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

Posted on 2007-11-19
7
649 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 20315277
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
ID: 20315297
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
ID: 20315430
Lowfatspread,

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

Thanks!
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 15

Accepted Solution

by:
JimFive earned 250 total points
ID: 20315663
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
ID: 20318171
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
ID: 20322043
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
ID: 20322127
No, your problem is with SQL Cursors not ODBC Cursors.
--
JimFive
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

729 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