Subject Re: Stored procs and array parameters...
Author Svein Erling Tysvær
--- In, "Greg At ACD" wrote:
> Thx for your response :)
> The strings are in a memory linked list in my C++ client
> application. They are retrieved from another source completely
> (filesystem file name, for example), and then used as an indexed
> reference in the database.
> My application currently (for example) walks thru this list and call
> a stored proc for each entry which returns information relating to a
> record in the database related to this string, or if there is no
> information, creates the new record. The information is returned as
> a one record result set from the stored procedure.
> Since this list can contain thousands of strings, the SP call is
> make 1000 times. Over the network... this can be slow.
> My preference is to call the stored procedure once with the list of
> strings, and have it do this work, and return all 1000 records as a
> result set of the stored procedure. To me, this would seem to be
> more efficient.
> ... But perhaps I'm wrong in this assessment...
> Greg

Well, then I guess I probably cannot help (I'm not saying it is
impossible, just that I cannot think of how to do it), but one thing
you wrote earlier puzzles me:

> > > In one situation, my client application (VC++, SQLAPI interface)
> > > calls a particular stored procedure once for each element in an
> > > array of strings. Of course, each call has some overhead (create
> > > SQL, prepare (parse) over network, execute, package results,
> > > send back over nework, repeat), so if I could call an SP that
> > > would take the array of strings as input, it would likely be
> > > more efficient... :)

What do you mean by 'prepare over network'? I'd write a select
statement selecting from the stored procedure, prepare it once, and
then just change parameters for every iteration of the loop. Preparing
can be pretty time consuming, so using parameters (without preparing
for every execution) could possibly make a huge difference in speed.