Launch storedquery for each record
Winbug
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 StoredProcedure1 $myid
execute StoredProcedure2 $myid
execute StoredProcedure3 $myid
execute StoredProcedure4 $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
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
> 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
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, esq...@sommarskog.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
(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
misunderstanding is common enough to warrant some attention on my blog.
Erland Sommarskog
> 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
> misunderstanding 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.
Winbug
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
Cursors worked and did the job.
Roger