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

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