Solved

large stored procedure versus small stored procedure

Posted on 2010-11-15
5
465 Views
Last Modified: 2012-05-10
I am trying to determine if it is better to build one large stored procedure with many parameters, or several smaller procedures with fewer parameters.  Besides the question of performance, I have a concern about being able to provide feedback to the user to know what is going on if the procedure takes a long time to run.  For example, if I wrote the code in vb.net and embedded it the program, I would keep sending messages to a status strip as each step of the process was started and/or completed.  I don't know how I can do this with a stored procedure if I had one long procedure instead of several shorter ones.

0
Comment
Question by:jsaacson
  • 3
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
gbanik earned 250 total points
ID: 34137339
If your concern is to just send status messages to the client about the progress, it can be done from a procedure irrespective of a long or multiple short ones. Your objective rather should be... Is there any reusable code that I must split into multiple procedures... can I optimize the long procedure... etc.
0
 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 250 total points
ID: 34137371
0
 

Author Comment

by:jsaacson
ID: 34137501
I am concerned about returning status messages, as I do not know how to feed back a message from a stored procedure to my vb.net program and show it to the user without waiting for the stored procedure to end.  

There is reusable code, as the process will gather data from the same tables but for different periods (ie current year, prior year).

0
 
LVL 13

Expert Comment

by:gbanik
ID: 34137971
Check the last link... this is what u want. Note that the status messages are asynchronous in nature.
0
 

Author Comment

by:jsaacson
ID: 34138003
I have been looking at them and I think it is beginning to make sense to me.  I am going to play with this for a while and see how it works.

I appreciate the help you both have given.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

810 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