[dba-VB] How do you run SQL Server processes

John,

Mark has it exactly correct.

Use SQL Server to the max of what it can do for you before you go off
creating something in c# to do essentially the same things. Data is what
it does, C# is the GUI.  Very similar to what we have all done with MS
Access over the years.

Now, lets get down to what you are doing...

Running code out of a database table is not efficient.  The optimizer has to
work on it before it can be run. So, the processing will be slower.

It would be better to have the code one or more stored procedures with all the
parameters and run them against small data sets to get them 
optimized. Then they
would be ready for the larger chunks of data.

Each SP can write to a log table and your C# GUI can display the progress by
reading data out of the log tables.

By using the log tables, you can pass start times, records processed and the
time at predetermined numbers of records and which stored proc is doing the
work at the time, end times, and just about any other information you may want,
Including things that cause errors.

SSIS is extremely powerful and the real tool that should probably be used for
what you want to do.  It can do all of the logging, error trapping and handle
passing errored records into a different table for analysis. The C# GUI could
be used to read the output from the SSIS package to do the monitoring.  It is
also relatively simple to sopy SSIS packages and change the parameters around
on them to allow them to be used for different extractions and builds.

Robert

At 01:43 AM 11/5/2009, you wrote:
>Date: Thu, 5 Nov 2009 15:43:49 +0800
>From: Mark Breen <marklbreen at gmail.com>
>Subject: [dba-VB] Fwd:  How do you run SQL Server processes
>To: dba-vb at databaseadvisors.com
>Message-ID:
>         <c7fd28d00911042343x428913fbu6aeb7378b2b7307d at mail.gmail.com>
>Content-Type: text/plain; charset=ISO-8859-1
>
>Hello John,
>
>FWIW, I agree with you that you need a rich environment to do the work that
>you plan to do.
>
>However, I think that Robert is correct in suggestion to try, as much as
>possible to not cross the technology stack with volumes of data when you do
>not have to.
>
>IOW, do all the heavy lifting in the database, and by all means return as
>many parameters or log results back to the front end you are building in C#,
>but try to avoid returning your data when ever possible.
>
>I guess I was suggesting the same with with SSIS.  Where you have to export
>data based on decisions, SSIS is an extremely fast option.
>
>Have you already used EXECUTE command to call an sproc within an sproc?
>  This is a useful means of creating an sproc, that running a set of sprocs,
>and can insert / update a log table as it progresses.  For data only
>actvity, this might be effective.
>
>For all the front end work however, I guess you will make C# sing, like only
>you can, so work away, and let TSQL do what it is good at, all the rest
>should not involve enormous volumes of data, so the technology bridge will
>be irrelevant.
>
>Good luck,  you know you are lucky.  Most Database people never get to read
>about a db as big as what you have :)
>
>On last thing, in SQL Server, Cursors are a dirty word, I never see them
>used.  However, when you have low volumes of iteration (thousands or a few
>tens of thousands), cursors are a very powerful way of enumerating tasks.
>
>Eg, lets imagine you had a table of states, and you needed to perform ten
>seperate sprocs for each state.  You could use a cursor to enumerate the
>states table and pass in the state name to the ten different sprocs.
>
>You could log between each iteration, and you could have the whole thing
>running in 30 minutes.  In this sort of context, I agree with Robert, that
>TSQL can give you Raw power quickly and easily.
>
>C# could be watching your log table and your 'Status Update' table and
>simply returning 1 or 2 records from the db to C# per minute, whereas within
>your sproc that is enumerating it might be handling millions of records each
>two minutes.  Those millions would never cross the stack divide of DB Server
>to .Net.  Again this sharing of processing power is probably what Robert was
>referring to.
>
>BTW I recently started using FileZilla and find it super.
>
>Good Luck,
>
>Mark
>
>
>
>Mark