Proper way to trigger a long running stored procedure (C# / ADO.NET)

Proper way to trigger a long running stored procedure (C# / ADO.NET)

Post by kellygreer » Wed, 30 Apr 2008 00:46:03



What is the proper way to run a long running stored procedure?

How should the code be modified (below) if the stored proc takes 5
minutes to run, and I don't care what the result is.

        public void Test()
        {
            SqlConnection cn = new SqlConnection(someConnStr);
            String sql = "EXEC sp_LongRunningProc";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

On a side note, is there a way that this can also be done in "classic"
ADO?

Thanks,
Kelly Greer

change nospam to yahoo

 
 
 

Proper way to trigger a long running stored procedure (C# / ADO.NET)

Post by Andrew Bader » Thu, 01 May 2008 03:16:50


1. Use an asynchronous process.
2. No, not really.


> What is the proper way to run a long running stored procedure?

> How should the code be modified (below) if the stored proc takes 5
> minutes to run, and I don't care what the result is.

>        public void Test()
>        {
>            SqlConnection cn = new SqlConnection(someConnStr);
>            String sql = "EXEC sp_LongRunningProc";
>            SqlCommand cmd = new SqlCommand(sql, cn);
>            cn.Open();
>            cmd.ExecuteNonQuery();
>            cn.Close();
>        }

> On a side note, is there a way that this can also be done in "classic"
> ADO?

> Thanks,
> Kelly Greer

> change nospam to yahoo

--
--Andy Badera
http://andrew.badera.us/ http://flipbitsnotburgers.blogspot.com/

(518) 641-1280
Google me: http://www.google.com/search?q=andrew+badera

 
 
 

Proper way to trigger a long running stored procedure (C# / ADO.NET)

Post by Cerebru » Thu, 01 May 2008 03:22:35


To add to what Andrew said :

1. Async process = Launch it on a separate thread with a large timeout
value and let your other code proceed upto the point where the result
of the Test() function becomes pertinent. As an aside, if the SP takes
5 min. to run, maybe you should try profiling it to find out exactly
what takes so long.


> What is the proper way to run a long running stored procedure?

> How should the code be modified (below) if the stored proc takes 5
> minutes to run, and I don't care what the result is.

> ? ? ? ? public void Test()
> ? ? ? ? {
> ? ? ? ? ? ? SqlConnection cn = new SqlConnection(someConnStr);
> ? ? ? ? ? ? String sql = "EXEC sp_LongRunningProc";
> ? ? ? ? ? ? SqlCommand cmd = new SqlCommand(sql, cn);
> ? ? ? ? ? ? cn.Open();
> ? ? ? ? ? ? cmd.ExecuteNonQuery();
> ? ? ? ? ? ? cn.Close();
> ? ? ? ? }

> On a side note, is there a way that this can also be done in "classic"
> ADO?

> Thanks,
> Kelly Greer

> change nospam to yahoo

 
 
 

Proper way to trigger a long running stored procedure (C# / ADO.NET)

Post by kellygreer » Thu, 01 May 2008 07:41:42


So essentially the best way to handle these is put them in a T-SQL
Job, and trigger it from the the app.
Seems like the best solution for doing it in a portable way (ADO.NET,
classic ADO, etc...)

Thanks for your thoughts,
Kelly


> To add to what Andrew said :

> 1. Async process = Launch it on a separate thread with a large timeout
> value and let your other code proceed upto the point where the result
> of the Test() function becomes pertinent. As an aside, if the SP takes
> 5 min. to run, maybe you should try profiling it to find out exactly
> what takes so long.


> > What is the proper way to run a long running stored procedure?

> > How should the code be modified (below) if the stored proc takes 5
> > minutes to run, and I don't care what the result is.

> > ? ? ? ? public void Test()
> > ? ? ? ? {
> > ? ? ? ? ? ? SqlConnection cn = new SqlConnection(someConnStr);
> > ? ? ? ? ? ? String sql = "EXEC sp_LongRunningProc";
> > ? ? ? ? ? ? SqlCommand cmd = new SqlCommand(sql, cn);
> > ? ? ? ? ? ? cn.Open();
> > ? ? ? ? ? ? cmd.ExecuteNonQuery();
> > ? ? ? ? ? ? cn.Close();
> > ? ? ? ? }

> > On a side note, is there a way that this can also be done in "classic"
> > ADO?

> > Thanks,
> > Kelly Greer

> > change nospam to yahoo- Hide quoted text -

> - Show quoted text -

 
 
 

Proper way to trigger a long running stored procedure (C# / ADO.NET)

Post by Jamie Frase » Sat, 03 May 2008 00:07:40


No, not a T-SQL job.

A .NET thread / asynchronous delegate which calls ADO => ExecuteNonQuery


>  So essentially the best way to handle these is put them in a T-SQL
>  Job, and trigger it from the the app.
>  Seems like the best solution for doing it in a portable way (ADO.NET,
>  classic ADO, etc...)

>  Thanks for your thoughts,
>  Kelly


>  > To add to what Andrew said :

>  > 1. Async process = Launch it on a separate thread with a large timeout
>  > value and let your other code proceed upto the point where the result
>  > of the Test() function becomes pertinent. As an aside, if the SP takes
>  > 5 min. to run, maybe you should try profiling it to find out exactly
>  > what takes so long.


>  > > What is the proper way to run a long running stored procedure?

>  > > How should the code be modified (below) if the stored proc takes 5
>  > > minutes to run, and I don't care what the result is.

>  > >         public void Test()
>  > >         {
>  > >             SqlConnection cn = new SqlConnection(someConnStr);
>  > >             String sql = "EXEC sp_LongRunningProc";
>  > >             SqlCommand cmd = new SqlCommand(sql, cn);
>  > >             cn.Open();
>  > >             cmd.ExecuteNonQuery();
>  > >             cn.Close();
>  > >         }

>  > > On a side note, is there a way that this can also be done in "classic"
>  > > ADO?

>  > > Thanks,
>  > > Kelly Greer

>  > > change nospam to yahoo- Hide quoted text -

>  > - Show quoted text -