Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Thu Nov 5 09:22:02 CST 2009
Hi John, <<< I am hoping to eventually make the entire process "table driven" where I can store (in a table) the name of the sp, the parameters in a child table etc. >>> Using table-driven process could be not quite right in C#/.Net world, which proposes many other options to implement generic solutions - I'd just start with "hardcoded" solutions using calls to a set of small "generic" classes/methods executing custom (MS SQL) database processing, when that set of methods/functions stabilizes I'd try to generalize/"generic-ize" it if that will be needed at all... What is the purpose of your "going generic" for this sets of tasks/customer? Isn't it "just for fun" of generic programming? Would that "going generic" make your programming more effective? Developing/debugging/testing generic custom "table-driven"/"other-metadata media driven" solutions is always (much) more time consuming than "hardcoded" ones, and supporting "table-driven" generic custom solutions, especially in the context of constantly changing customer requirements also promise to be rather time consuming - I mean generic "table-"/metadata-driven solutions could only(?) pay back well in the case they are used on many deployment sites by many people. I can be wrong, just talking from my experience, which is not universal of course... I'd better propose to use metadata descriptions/specs (stored in db tables or (XML) files), if you're so bored to write repetitive code, to generate "hardcoded" solutions, then make quick fixes to this generated hardcode to satisfy your customers' current requests, then (in free time) adjust code generator, then when everything will stabilize (will that ever happen?) develop truly generic "table-driven" solution. You can also consider using F# and Windows Workflow Foundation (WF)... The following presentation seems to be useful to get true picture of "generic vs. specific" solutions trade-offs: http://www.infoq.com/presentations/Generic-Specific-Tradeoffs-Stefan-Tilkov Thank you. -- Shamil P.S. FYI: AFAIKR MS has got created Domain Specific Language (DSL) team to develop DSL tools somewhere in year 2004 and they planned to come with first release in one(?) year but they aren't yet there (?)... -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, November 04, 2009 6:12 AM To: VBA; Dba-Sqlserver Subject: [dba-VB] How do you run SQL Server processes I have processes that I run which use sets of stored procedures. For example two of the processes export / import records from large tables for address validation. The addresses need to be sorted in Zip5/Zip4/Address order in order to make the external validation process as fast as possible so I create a table that pulls millions of records, often tens of millions of records, into a table (created on-the-fly) in sorted order, then pulls out 2 million records at a time and BCPs them out for processing in an external program. The inverse occurs when the records finish processing, 2 million record chunks are imported back into a temp table, then each 2 million record chunk is appended to a main table. Once back in SQL Server I have processes that build clustered and non-clustered indexes, run sha-1 hash functions to generate hash keys, update address valid codes etc. I will run this entire project at least once a month (to keep the addresses valid as people move), and I do so for many tables - a table of about 65 million addresses, another of 21 million, another of 11 million etc. I store all of my stored procedures in a dedicated database that pretty much just contains the SPs and a few UDFs as well as a couple of logging type tables. At the moment I use an Access database to run the individual stored procedures in specific order. I am just embarking on a project to move that functioning but not very efficient Access database to C#. I want to be able to have better monitoring of SP completion, records processed (counts), time to complete each SP etc. I found a pretty nice set of C# classes that allow creation of "stored procedure" objects, with parameter objects, then stored into a collection as the SPs (class instances) are assembled and finally the whole collection of SPs executed. I created a test database of a million names / addresses and today I started testing my existing SPs on this small data set. In the next few days I expect to build out a preliminary "port" of the existing Access VBA code. I am hoping to eventually make the entire process "table driven" where I can store (in a table) the name of the sp, the parameters in a child table etc. and then have a supervisor pull out the records and execute the SPs based on what is in the table. This would allow me to create the SPs and then use a C# application (data entry forms) to organize the SPs, add / delete SPs from the process and so forth. That is down the road of course, for now I will have hard coded sequences - as I already do in supervisor SPs as well as in hard coded Access VBA modules. As a programmer, doing this supervisor level in C# feels more natural to me (than doing it inside of SQL Server), and gives me the flexibility to do what I want in code external to the SQL Server database being manipulated. I want to be able to do things external to the database processes, things like monitor the directories that the BCP processes export to / from, move files popping out of a bcp process off to a VM on a different server, monitor those VMs for files coming out of their processes, moving the files back over to the SQL Server directories, and trigger BCP processes that import the data back in to SQL server. I need to be able to FTP order and data files to clients, and get (FTP) files from process bureaus. So it FEELS to me like using a language such as C# to build a system outside of SQL Server is the way to go. BTW I stumbled across something called SMO (SQL Server Management Objects) which makes it really easy to get at the SQL Server object model from C#. I can apparently see the entire tree structure that is a server/database/tables/etc. and do I know not what (yet) with those objects. Looks pretty cool though. The database is a large part of what I do but it is not the whole picture and I have been handcuffed by my lack of ability to effectively do these external parts. But I am a programmer at heart. I want to know how you do this kind of stuff. I know that running such "systems" of stored procedures tied to external processes must be quite common and I am wondering what you guys do for supervising your processes. If anyone doing anything remotely similar would take the time to explain how you do it, I would be most appreciative. Thanks, -- John W. Colby www.ColbyConsulting.com _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4573 (20091104) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru