Launch storedquery for each record

[Winbug]

hi
I have a scenario where I have created a stored procedure wich task is
to search in my database with certain criterias. For each record from
this search, I want to run a couple of other stored procedure.


Here is some code:

decare $myid nvarchar(15)

select id, desc, customer from project where customer = 'cust1'
------------------------------------------
--HERE IS CODE MISSING--
------------------------------------------
--for each of the records returned, I want to run 4 stored procedures
with the id as parameter.
begin
execute StoredProcedure 1 $myid
execute StoredProcedure 2 $myid
execute StoredProcedure 3 $myid
execute StoredProcedure 4 $myid
end

The problem is I don't know how to get hold of the result set and loop
through. Maybe you can help me.

regards
Roger

[Hugo Kornelis]

Re: Launch storedquery for each record

On Wed, 21 Nov 2007 11:49:33 -0800 (PST), Winbug wrote:

>hi
>I have a scenario where I have created a stored procedure wich task is
>to search in my database with certain criterias. For each record from
>this search, I want to run a couple of other stored procedure.
>
>
>Here is some code:
>
>decare $myid nvarchar(15)
>
>select id, desc, customer from project where customer = 'cust1'
>------------------------------------------
>--HERE IS CODE MISSING--
>------------------------------------------
>--for each of the records returned, I want to run 4 stored procedures
>with the id as parameter.
>begin
>execute StoredProcedure 1 $myid
>execute StoredProcedure 2 $myid
>execute StoredProcedure 3 $myid
>execute StoredProcedure 4 $myid
>end
>
>The problem is I don't know how to get hold of the result set and loop
>through. Maybe you can help me.
>
>regards
>Roger

Hi Roger,

From a performance point of view, this is not a good way to build your
application. You should rewrite the four stored procedures to operate on
all qualifying data at once, as SQL Server is optimized for processing
set-based queries.

However, there may be reasons that this is not feasible. The cost of
rewriting the procs may be high, and if your amount of data is low,
performance might not be a consideration. In that case, you can use a
cursor to process the results of a query row by row. There are many
examples in Books Online, so if you just use the index to find DECLARE
CURSOR, you should be all set.

If performance does matter and you still want to use a cursor, than you
might wish to read my recent blog posts on the performance effects of
various cursor options. You can find it at

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

Re: Launch storedquery for each record

Hugo Kornelis (hugo@perFact.R EMOVETHIS.info. INVALID) writes:

If performance does matter and you still want to use a cursor, than you
might wish to read my recent blog posts on the performance effects of
various cursor options. You can find it at

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-

optimization-options.aspx

As the hour is late, I didn't have the time to read it all, but I will
save that for later. But I found the conclusions interesting. I always
go for STATIC cursors myself, mainly because then I know that nothing
unexpected will happen. (Actually I really prefer INSENSITIVE as that is
ANSI, but alas it cannot be combined with LOCAL.)

I also note that WHERE CURRENT OF is still bad. I tried WHERE CURRENT OF
when I first got play with SQL 6.0, but ran into permission issues, and
I also noticed a scan in the query plan. And since updating through the
PK is dead simple, why bother with anything else?

One battle I have to fight in my shop is with colleagues who think that a
"poor man's cursor" is better. And maybe sometimes it is. But having a
loop where you do SELECT MIN() on a non-indexed temp table with 100000
rows is definitely not.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Hugo Kornelis]

Re: Launch storedquery for each record

On Wed, 21 Nov 2007 22:22:57 +0000 (UTC), Erland Sommarskog wrote:

(snip)

>One battle I have to fight in my shop is with colleagues who think that a
>"poor man's cursor" is better. And maybe sometimes it is. But having a
>loop where you do SELECT MIN() on a non-indexed temp table with 100000
>rows is definitely not.

Hi Erland,

Maybe it is, but I doubt it. After investigating the effect of options,
I wouldn't be surprised to find a way to beat a cursor with the default
options, since they are SLOW - but I have yet to see a "poor man's
cursor" that outperforms a _properly optimized_ cursor.

If your colleagues ever show you a way that they think beats a cursor,
please share it with me. Might make neat blog fodder.

Hmmm, maybe I'll do a sequel to the cursor episode anyway. The
misunderstandin g is common enough to warrant some attention on my blog.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

Re: Launch storedquery for each record

Hugo Kornelis (hugo@perFact.R EMOVETHIS.info. INVALID) writes:

Maybe it is, but I doubt it. After investigating the effect of options,
I wouldn't be surprised to find a way to beat a cursor with the default
options, since they are SLOW - but I have yet to see a "poor man's
cursor" that outperforms a _properly optimized_ cursor.

Yes, just saying DECLARE cur CURSOR may lead to problem. I don't have
had much problems with performance on SQL 2000 and later, but that is
mainly because I was burnt enough on 6.5. These days I make my cursors
STATIC/INSENSITIVE as a matter of routine.

But another issue that I've seen people run into with dynamic cursors is
that rows keeps coming back to you, so that your cursor never terminates.

If your colleagues ever show you a way that they think beats a cursor,
please share it with me. Might make neat blog fodder.

Well, if you index your loop column it's a different matter, and while
have to admit that there have been occasions I've written that sort of
loops myself. But that is probably maninly due to that a cursor declaration
and all takes up some more space in the code. But from a performance point
of view, there is a certain overhead in setting up a static cursors, since
rows has to be copied. But if you have that many rows to make this overhead
noticable, then you are probably in dire need of a set-based solution.

Hmmm, maybe I'll do a sequel to the cursor episode anyway. The
misunderstandin g is common enough to warrant some attention on my blog.

Yes, people see "don't use cursors" and then implement their loops in
a different way, and thus go from bad to worse.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Winbug]

Re: Launch storedquery for each record

Thanks Hugo
I'll have a look at cursors.
Since my job is only going to run once a day I am not concerned about
performance. The queries are broken up because they are used here and
there in other stored procedures.

Roger

[Winbug]

Re: Launch storedquery for each record

It' all good.
Cursors worked and did the job.

Roger