Query your Stored Procs

Is there a way you can query stored procedures for a string in all of the
stored procs in a database? For example, if you were looking for "LETTERS"
in all of your stored procedures in your Account DB, how would you look for
the string? Please post example if you know.
-Toco-You can search the syscomments which has the source code for all your stored
procedures. A wrapper script can be found at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
Anith|||SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM AccountDB.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%LETTERS%'
http://www.aspfaq.com/
(Reverse address to reply.)
"Toco" <Toco@.discussions.microsoft.com> wrote in message
news:5A3A9543-40A4-460D-BFDC-09C021991BDD@.microsoft.com...
> Is there a way you can query stored procedures for a string in all of the
> stored procs in a database? For example, if you were looking for
"LETTERS"
> in all of your stored procedures in your Account DB, how would you look
for
> the string? Please post example if you know.
> -Toco-

Query with multiple tables Use of JOIN vs WHERE

I have inherited a database, written a few years back and the people who
designed the stored procedures seem to do things differently than I learned
and it seems to work faster, but I cannot figure out why. I check the
execution plans and they appear identical, but the statistics show a HUGE
(to me) difference in reads (147 for the first method and 40 for the second)
. The second method takes about 10 seconds less to return the results. (O
f
course the code is wrapped in a stored procedure - but I was curious about
this "new" way of doing things so I extracted the select statements to do
comparisons).
I'm sure that the explanation is simple and is just an area of SQL coding I
hadn't been exposed to yet.
Any info appreciated,
Nancy
CODE:
It is a simple query to get a count joining 3 tables
I would normally do the query using:
Select count(CODE1)
from Table1
JOIN x_hcfa_cpt
ON
Table1.CLAIMNO = Table2.CLAIM_NUMBER
JOIN x_hcfa_cpt_mas
ON
Table1.TERM_NUMBER = Table3.TERM_NUMBER
where
Table1.TERM_NUMBER = 'asdftcdww'
AND
(Table1.STATUS='false'
or
Table1.STATUS='true')
and
Table1.FLAG='false'
But in the code I inherited they used:
Select count(CODE1)
from Table1, Table2, Table3
where
Table1.X_TERMINAL_NUMBER = 'asdftcdww'
AND
Table1.CLAIMNO = Table2.CLAIM_NUMBER
and
Table1.TERM_NUMBER = Table3.TERM_NUMBER
and
(Table1.STATUS='false'
or
Table1.STATUS='true')
and
Table1.FLAG ='false'> Select count(CODE1)
> from Table1, Table2, Table3
This is non-standard code and should be avoided (especially for outer joins
due to non-conforming behavior, but for inner joins as well). My suggestion
is to re-write the code with JOIN statements, and to avoid ambiguity, I make
it a standard practice to include the type of JOIN, so I would use the INNER
keyword as well (even though it is the default).|||There should be no difference between SQL-92 JOINs (1st statement) and
the older style (2nd statement).
I'm guessing that x_hcfa_cpt and x_hcfa_cpt_mas are Table2 and Table3 in
the first query?
Also, in the first query, the where clause uses Table1.TERM_NUMBER
whereas the 2nd query uses Table1.X_TERMINAL_NUMBER. Could that be the
difference in performance?
Nancy Lytle wrote:

>I have inherited a database, written a few years back and the people who
>designed the stored procedures seem to do things differently than I learned
>and it seems to work faster, but I cannot figure out why. I check the
>execution plans and they appear identical, but the statistics show a HUGE
>(to me) difference in reads (147 for the first method and 40 for the second
)
>. The second method takes about 10 seconds less to return the results. (O
f
>course the code is wrapped in a stored procedure - but I was curious about
>this "new" way of doing things so I extracted the select statements to do
>comparisons).
>I'm sure that the explanation is simple and is just an area of SQL coding I
>hadn't been exposed to yet.
>Any info appreciated,
>Nancy
>CODE:
>It is a simple query to get a count joining 3 tables
>I would normally do the query using:
>Select count(CODE1)
>from Table1
>JOIN Table2
>ON
>Table1.CLAIMNO = Table2.CLAIM_NUMBER
>JOIN Table3
>ON
>Table1.TERM_NUMBER = Table3.TERM_NUMBER
>where
>Table1.TERM_NUMBER = 'asdftcdww'
>AND
> (Table1.STATUS='false'
> or
> Table1.STATUS='true')
>and
> Table1.FLAG='false'
>But in the code I inherited they used:
>Select count(CODE1)
>from Table1, Table2, Table3
>where
>Table1.X_TERMINAL_NUMBER = 'asdftcdww'
>AND
>Table1.CLAIMNO = Table2.CLAIM_NUMBER
>and
>Table1.TERM_NUMBER = Table3.TERM_NUMBER
>and
>(Table1.STATUS='false'
>or
>Table1.STATUS='true')
>and
>Table1.FLAG ='false'
>
>|||On Tue, 13 Sep 2005 16:37:16 -0400, Nancy Lytle wrote:

>I have inherited a database, written a few years back and the people who
>designed the stored procedures seem to do things differently than I learned
>and it seems to work faster, but I cannot figure out why.
(snip)
Hi Nacny,
I'll assume that the different table names and column names are a result
of you renaming some tables and columns when preparing the post, and the
code you actually tested this on didn't have these differences :-)
The difference in the queries are the two different styles of join
notation. In old versions of SQL Server (note that I'm talking real old
here - older than SQL Server 6.5), only the version with the
comma-delimited list of tables is allowed. The more verbose version with
infixed join operators was added later, to adhere to the ANSI standard.
For inner joins, there is absolutely no difference between the two
versions. They are both defined in the ANSI standard, both acccepted by
SQL Server and they will both return the same results. They'll also use
the same execution plan, so that there's no performance difference
either.
For outer joins, things are different. The "old-style notation" (that
uses =* and *= in the WHERE clause to define inner and outer tables) is
ambiguous. It's not defined in the ANSI standard. MS has announced that
it will drop support for =* and *= in a future version. In fact, I
recall reading somewhere that SQL Server 2005 will only accept =* and *=
in the backward compatibility mode.

> I check the
>execution plans and they appear identical, but the statistics show a HUGE
>(to me) difference in reads (147 for the first method and 40 for the second
)
>. The second method takes about 10 seconds less to return the results.
Did you run both tests on an empty cache? I suspect not - and that's
what causes the difference.
Test it like this:
DECLARE @.start datetime
DECLARE @.end datetime
-- Flush all dirty buffers to disk
CHECKPOINT
-- Remove all previously read pages from the data cache
DBCC DROPCLEANBUFFERS
-- Remove all previously compiled execution plans as well
DBCC FREEPROCCACHE
-- Now start the real test
SET @.start = CURRENT_TIMESTAMP
#### ####
#### YOUR QUERY GOES HERE ####
#### ####
SET @.end = CURRENT_TIMESTAMP
SELECT @.start AS StartTime,
@.end AS EndTime,
DATEDIFF(ms, @.Start, @.End) AS "Elapsed (ms)"
If the code that you are testing returns many rows, change the SELECT to
a SELECT ... INTO #temp_table to eliminate the speed of the network and
the display speed of your client from the equation.
I'd be VERY surprised if you still get significant differences if you
test the queries like this. (Small differences are to be expected,
especially if the server you're running this on has other things to do
as well).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo, Trey and Aaron!
I guess there is more sql code I am going to have to change, the SP's (and
they are all named sp_ !) are dotted with uses of comma delimited lists of
tables for joins and usage of *=, not to mention tons of select *'s, and the
sp_ naming convention.
I used Hugo query and that helped me see the real difference between the
two, which is actually very slight and leans toward the use of JOINs.
Thanks again, this is a great group!
Nancy
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1fei1paaf5g8qo9ef509q9cqocdmds466@.
4ax.com...
> On Tue, 13 Sep 2005 16:37:16 -0400, Nancy Lytle wrote:
>
> (snip)
> Hi Nacny,
> I'll assume that the different table names and column names are a result
> of you renaming some tables and columns when preparing the post, and the
> code you actually tested this on didn't have these differences :-)
> The difference in the queries are the two different styles of join
> notation. In old versions of SQL Server (note that I'm talking real old
> here - older than SQL Server 6.5), only the version with the
> comma-delimited list of tables is allowed. The more verbose version with
> infixed join operators was added later, to adhere to the ANSI standard.
> For inner joins, there is absolutely no difference between the two
> versions. They are both defined in the ANSI standard, both acccepted by
> SQL Server and they will both return the same results. They'll also use
> the same execution plan, so that there's no performance difference
> either.
> For outer joins, things are different. The "old-style notation" (that
> uses =* and *= in the WHERE clause to define inner and outer tables) is
> ambiguous. It's not defined in the ANSI standard. MS has announced that
> it will drop support for =* and *= in a future version. In fact, I
> recall reading somewhere that SQL Server 2005 will only accept =* and *=
> in the backward compatibility mode.
>
> Did you run both tests on an empty cache? I suspect not - and that's
> what causes the difference.
> Test it like this:
> DECLARE @.start datetime
> DECLARE @.end datetime
> -- Flush all dirty buffers to disk
> CHECKPOINT
> -- Remove all previously read pages from the data cache
> DBCC DROPCLEANBUFFERS
> -- Remove all previously compiled execution plans as well
> DBCC FREEPROCCACHE
> -- Now start the real test
> SET @.start = CURRENT_TIMESTAMP
> #### ####
> #### YOUR QUERY GOES HERE ####
> #### ####
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.start AS StartTime,
> @.end AS EndTime,
> DATEDIFF(ms, @.Start, @.End) AS "Elapsed (ms)"
> If the code that you are testing returns many rows, change the SELECT to
> a SELECT ... INTO #temp_table to eliminate the speed of the network and
> the display speed of your client from the equation.
> I'd be VERY surprised if you still get significant differences if you
> test the queries like this. (Small differences are to be expected,
> especially if the server you're running this on has other things to do
> as well).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 14 Sep 2005 08:59:40 -0400, Nancy Lytle wrote:

>Thanks, Hugo, Trey and Aaron!
>I guess there is more sql code I am going to have to change, the SP's (and
>they are all named sp_ !) are dotted with uses of comma delimited lists of
>tables for joins and usage of *=, not to mention tons of select *'s, and th
e
>sp_ naming convention.
>I used Hugo query and that helped me see the real difference between the
>two, which is actually very slight and leans toward the use of JOINs.
>Thanks again, this is a great group!
>Nancy
Hi Nancy,
I guess that the "very slight" difference you see falls within the
bounds of statistic inaccuracy. If you repeat the test a few times, you
should see that there really is no difference between the two.
As far as rewriting code, I'd say: find the right path between
religiously rewriting everything (costly, time-consuming, and will
introduce bugs, if only by typo's and copy/paste errors) on the one end,
and leaving working code untouched on the other end.
If you decide to start rewriting where it's needed most, then begin with
the use of =* and *= for outer joins, as they are on the deprecated
feature list. Next should be the sp_ prefix and the use of SELECT *
(both are performance killers in their own ways; both induce a risk of
unexpectedly breaking your code when some change is made elsewhere).
The join syntax for inner joins (i.e. the use of comma-delimited table
list without any =* or *=) should be last on your list, as this is only
a readability improvement (and not all experts would agree that it's an
improvement - there are a few SQL experts who think that the "old style"
join notation is often better, though I'm not one of them).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the suggestions.
I did a complete search and the locations for the *= code is in dynamic sql
written in .asp pages. So, since I am changing the dynamic sql to a stored
procedure, I can get rid of the *= and select * and created proper procedure
naming all at the same time.
I will wait to modify the names of the sp_'s that are already written until
I have a chance to sit down with the other programmers, etc, and we come up
with a plan. My initial thought was to simply recreate the SPs changing
only the name, so we would have essentially 2 sp's that did the same thing,
just one sp_ and one usp_ names. Then we could start cutting over the names
in the code without breaking anything, that couldn't be fixed almost
immediately.
But this is my first time really taking on a task like this, does this sound
like a plan?
Do you have any articles or books to recommend to a new DBA/developer?
Thanks again,
Nancy
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3rvgi15u21emupk0gfqe9pl5dpfk8kgl0s@.
4ax.com...
> On Wed, 14 Sep 2005 08:59:40 -0400, Nancy Lytle wrote:
>
> Hi Nancy,
> I guess that the "very slight" difference you see falls within the
> bounds of statistic inaccuracy. If you repeat the test a few times, you
> should see that there really is no difference between the two.
> As far as rewriting code, I'd say: find the right path between
> religiously rewriting everything (costly, time-consuming, and will
> introduce bugs, if only by typo's and copy/paste errors) on the one end,
> and leaving working code untouched on the other end.
> If you decide to start rewriting where it's needed most, then begin with
> the use of =* and *= for outer joins, as they are on the deprecated
> feature list. Next should be the sp_ prefix and the use of SELECT *
> (both are performance killers in their own ways; both induce a risk of
> unexpectedly breaking your code when some change is made elsewhere).
> The join syntax for inner joins (i.e. the use of comma-delimited table
> list without any =* or *=) should be last on your list, as this is only
> a readability improvement (and not all experts would agree that it's an
> improvement - there are a few SQL experts who think that the "old style"
> join notation is often better, though I'm not one of them).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 15 Sep 2005 09:51:15 -0400, Nancy Lytle wrote:

>Thanks for the suggestions.
>I did a complete search and the locations for the *= code is in dynamic sql
>written in .asp pages. So, since I am changing the dynamic sql to a stored
>procedure, I can get rid of the *= and select * and created proper procedur
e
>naming all at the same time.
Hi Nancy,
Wow, that's a major improvement - getting rid of two major pitfalls at
once!

>I will wait to modify the names of the sp_'s that are already written until
>I have a chance to sit down with the other programmers, etc, and we come up
>with a plan. My initial thought was to simply recreate the SPs changing
>only the name, so we would have essentially 2 sp's that did the same thing,
>just one sp_ and one usp_ names. Then we could start cutting over the name
s
>in the code without breaking anything, that couldn't be fixed almost
>immediately.
>But this is my first time really taking on a task like this, does this soun
d
>like a plan?
Discussing things with the developers is definitely a great idea. As
long as your modifications are invisible to them (such as replacing
dynamic =* crap with non-dynamic OUTER JOINs), you could do you work in
silence (though I'd even recommend communicating your actions in that
case). But if your changes are going toa ffect the developers (and they
will if you intend to eventually remove the badly named stored
procedures), they should be informed, and invited to participate.
But if you are renaming, then I'd just drop the prefix completely. I've
never managed to see the added value of
EXEC usp_MakeMonthlyReport
over
EXEC MakeMonthlyReport

>Do you have any articles or books to recommend to a new DBA/developer?
http://www.aspfaq.com/show.asp?id=2423
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query to find invalid objects?

Is there a system table that holds data on invalid objects/procedures? In
Oracle, I can query dba_objects table where status='INVALID' to find all of
the invalid objects. Is there anything similar in SQL Server?
thanks!
SusanHi Susan
What do you mean by invalid objects?
If you require to know any stored procedure that is referencing another
stored procedure or table etc.. that does not exists then you may want to tr
y
doing a textual search of the source code. There is a sysdepends table for
dependencies, but this is not guaranteed to list everything.
John
"Susan Cooper" wrote:

> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all o
f
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||Can you define what an "invalid" object is? This is not a term used in the S
QL Server world, so it
could mean different things. The short story is "no", there is no such table
..
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all o
f
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||By invalid, I mean that dependencies are broken. I was hoping that SQL
Server stored something saying a procedure will not work if an object it
depends on is dropped or changed, ect. In the Oracle world, these are calle
d
invalid objects and you can query the dba_objects table.
Thanks for the help!
"Tibor Karaszi" wrote:

> Can you define what an "invalid" object is? This is not a term used in the
SQL Server world, so it
> could mean different things. The short story is "no", there is no such tab
le...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
>|||Hi
You may want to look at using SCHEMABINDING with views and functions (which
is the converse of what you wanted!), but for stored procedures it is not
available.
John
"Susan Cooper" wrote:
[vbcol=seagreen]
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are cal
led
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>|||As already posted, the sp_depends and sysdependencies tables (and the 2005 c
atalog view counterpart)
will give you a clue. But it isn't 100 percent for reasons like dynamic SQL,
deferred name
resolution etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:951437A6-A3F4-493D-A17C-4E753FC9E526@.microsoft.com...[vbcol=seagreen]
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are cal
led
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>

Query to find invalid objects?

Is there a system table that holds data on invalid objects/procedures? In
Oracle, I can query dba_objects table where status='INVALID' to find all of
the invalid objects. Is there anything similar in SQL Server?
thanks!
SusanHi Susan
What do you mean by invalid objects?
If you require to know any stored procedure that is referencing another
stored procedure or table etc.. that does not exists then you may want to try
doing a textual search of the source code. There is a sysdepends table for
dependencies, but this is not guaranteed to list everything.
John
"Susan Cooper" wrote:
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
could mean different things. The short story is "no", there is no such table...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||By invalid, I mean that dependencies are broken. I was hoping that SQL
Server stored something saying a procedure will not work if an object it
depends on is dropped or changed, ect. In the Oracle world, these are called
invalid objects and you can query the dba_objects table.
Thanks for the help!
"Tibor Karaszi" wrote:
> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> could mean different things. The short story is "no", there is no such table...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > Is there a system table that holds data on invalid objects/procedures? In
> > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > the invalid objects. Is there anything similar in SQL Server?
> >
> > thanks!
> > Susan
>|||Hi
You may want to look at using SCHEMABINDING with views and functions (which
is the converse of what you wanted!), but for stored procedures it is not
available.
John
"Susan Cooper" wrote:
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
> > Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> > could mean different things. The short story is "no", there is no such table...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> > news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > > Is there a system table that holds data on invalid objects/procedures? In
> > > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > > the invalid objects. Is there anything similar in SQL Server?
> > >
> > > thanks!
> > > Susan
> >
> >|||As already posted, the sp_depends and sysdependencies tables (and the 2005 catalog view counterpart)
will give you a clue. But it isn't 100 percent for reasons like dynamic SQL, deferred name
resolution etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:951437A6-A3F4-493D-A17C-4E753FC9E526@.microsoft.com...
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so
>> it
>> could mean different things. The short story is "no", there is no such table...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
>> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
>> > Is there a system table that holds data on invalid objects/procedures? In
>> > Oracle, I can query dba_objects table where status='INVALID' to find all of
>> > the invalid objects. Is there anything similar in SQL Server?
>> >
>> > thanks!
>> > Susan
>>

Query Time in SQL Server

I am using SQL Server and ASP.NET. I am executing a couple of stored procedures and displaying the results in a datagrid. Since these Stored procedures takes around 2-4 minutes each, I want to display a status bar on the web by displaying the approximate time the user needs to wait before seeing the results.

My question is: Is there a way to find out the approximate EXECUTION TIME of the stored procedure before hand. Also, if that is possible, how do i access the same from the ASP.NET code..

Thanks
SathyaI do not know of a way to determine the approximate execution time of a stored procedure before it runs.

Perhaps you should use the worst-case execution time as your estimate for each?

And also, 2-4 minutes for a query to run is not reasonable. You should strongly consider spending some time trying to optimize these queries.

Terri|||I agree with Terri on this one. 2-4 minutes for a query to run especially as a stored procedure is saying a tremendous amount to the inefficiencies you may have in your design or execution.

Initially, I'd take the time to repair that before continuing further to make your future tasks with your application even more complicated.