For Stored Procs Gurus

Hi,
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael Murphy
Please do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>