jwcolby
jwcolby at colbyconsulting.com
Wed Dec 2 09:51:31 CST 2009
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 Shamil Salakhetdinov wrote: > Hi John -- > > I must say I have never done that "dynamic" t-SQL via C# - I guess it should > be doable: using SMO or even SqlCommand's .ExecuteNonQuery(?) it should be > possible to create SPs and UDFs, and then execute them as usual... > > BTW, UDFs, which return tables are very often a good substitute for all > kinds of temp tables and dynamic SQL... > > I wonder what for all that "dynamism" there? > Do you have constantly changing database structure? > Do you have constantly changing customer requests, which do not allow you to > create a set of stable parameterized SPs and UDFs, and for small(?) changing > part of queries use LINQ for SQL or ADO.NET EF? > > Thank you. > > -- > 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 1:54 AM > To: VBA > Subject: [dba-VB] C# - Direct data manipulation > > As previously mentioned, over the last couple of years I have built up a set > of stored procedures > which perform TSQL. I then run sets of these stored procedures to perform > processes. The TSQL is > always dynamic. > > As an example a stored procedure might build a table, with boilerplate field > names and data types, > but a passed in database and table name. Inside of the stored procedure I > build up the correct SQL > to create the table in the database specified, with the name specified. > Another stored procedure > might create in index, with boilerplate field names, but a passed in > database and table name. Again > the stored procedure builds up the TSQL statement to create the index in the > correct db and table. > > I already have these stored procedures debugged and working and have been > using them in sets to > perform "big picture processes" for quite some time, but the whole "execute > a stored procedure" > using a command object, parameter objects in a parameter array, interpreting > the parameters etc just > seems so... 1950s. I feel like I am flipping switches on the front panel of > a computer in a 50s > sci-fi movie as a tape reel spins madly in the background. > > Now that I am moving to C# I am wondering if this is required any more. Can > C# execute TSQL > directly somehow? Can I assemble this boiler plate plus database / table > kind of thing into a TSQL > statement directly in C# and then somehow tell SQL Server to execute that > TSQL? I understand the > "SQL optimization" thing but it seems like (not sure here) that because the > TSQL is dynamically > constructed in the stored procedure, the whole "optimization" thing goes > right out the window. If I > can execute TSQL directly from C#, what can I get back? Rows affected? > Error codes? And how? > > As it stands I am happily flipping switches, tape reels whirring, exporting > data for address > validation. This is good, I am happy, but ... >