[dba-VB] C# - Direct data manipulation

Hi John --

Thank you for your reply.

Do you use your different databases for data input, or you also have to
update those different databases' tables?

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, December 02, 2009 6:52 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] C# - Direct data manipulation

Shamil,

As I have discussed in the past, the business is doing the exact same
process to many different 
tables.  Because of the size of these tables and the processing involved I
find it useful to store 
each table in it's own database.  These are just lists of names, each list
comes from a different 
source and is called something by the client, something that makes sense to
him.  These lists (a 
table) will always be millions of names / addresses, and often are 50
million or more.  When you 
have 50 million names, the database, with just this one single table will be
10 or 20 gigabytes. 
Once you start processing it can mushroom to 50 gigabytes.  Thus storing a
dozen of these tables in 
a single database just doesn't make sense (to me).

Thus I have a dozen different databases.

I have to apply all of these stored procedures to each one of these
databases once a month.  Thus 
write the stored procedure such that I can pass in a database name and a
table name.  Voila, the SP 
works for all of my databases.  But that means dynamic SQL.  I really don't
have an issue with 
dynamic SQL, although I have witnessed first hand the time required for SQL
Server to do the 
"compile" - creating the execution plan.  If I run the SP a second time with
the same db and table 
name the time to execute drops sharply.

BTW, these lists become the source for "orders" of names.  Each of these
orders also are stored in 
its own database, and I have to execute these same (or similar) SPs against
each of these orders. 
Over the last 4 years I have perhaps a hundred of these order databases.

This is what I do for this client, create these databases, and apply
processes against these 
databases.  The processes are exactly the same across all of the databases,
thus having a stored 
procedure that performs some part of that process but can be applied to any
of these databases makes 
sense (to me).

I have spent the last five years performing these processes entirely
manually, then one by one 
creating a stored procedure to automate one of the steps, then the next
step, and the next.  By now 
I have an entire master library with (let me go check) 42 different stored
procedures that, taken 
together, automate my work for the client.

THAT is the point of the C# side of things.  Automating the automation so to
speak.  Taking these 42 
stored procedures and stringing sets of them together, 7 which perform the
output of data to an 
external program for processing, 6 more that get the processed data back in,
a half dozen more that 
perform some other processing, another set that do order processing, etc.

This is not a bank, or a company or a call center or a manufacturer, or any
of the other "typical" 
SQL Server databases.  It is name / address list processing and creating
orders from these lists. 
Really pretty simple but extremely painstaking.  And like anything else we
do, I can't be making 
mistakes, it must be done in a specific manner, a specific sequence, EVERY
time.

John W. Colby
www.ColbyConsulting.com

<<< snip >>>
 

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4655 (20091202) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru