Francisco Tapia
fhtapia at gmail.com
Thu May 20 10:22:16 CDT 2010
I think it boils down to speed and accuracy of data. A view will always generate a truthful representation of the available data.. so if you add new records or as John does, processes them through his validator scheme, then the results of the View will be different from his original resultset, and what he needs is an exact copy of the original order, thus the physical tables. secondly i think it also boils down to speed... if his original table is a 70million row table and you've already extracted the results to a 2million row table, it's much faster to quickly re-generate the report for your client from the 2m table than the 70m, saving you time, and producing the exact results... I think he's said in the past that he may be asked to provide resultsA... then maybe asked for that same resultset agian a 2nd time.. or to generate a derivative from resultsA to generate a resultAsubB as an example... in any case it seems like a shame to not be able to provide all available records if new demographic data is suddenly made available... but that's just me. -Francisco http://sqlthis.blogspot.com | Tsql and More... On Thu, May 20, 2010 at 7:51 AM, David Lewis <David at sierranevada.com> wrote: > Hi John: A few questions about how you do things: > > If I understand your first example correctly, you have database per client > (is this one large table, or is it a full-blown relational db with many > tables, etc.?). You extract subsets of records repeatedly from that large > table. To do this you build a query (the view), which you use to populate a > table (SubTable1). From that table you select a subset (SubTable1_a), and > later you may select another subset (SubTable1_b). Each of these subsets > you persist as physical tables. > In this example, why build the physical table (SubTable1)) when the virtual > table (the view) will do the same thing? The subsets of the table > (SubTable1_a and _b) that are actually sent on to the client can be thought > of as 'WHERE xyz=123' clauses to the view that was originally used to > populate SubTable1, no? If you kept a record of the where clauses, with > other pertinent information, such as date, client, etc., you could reproduce > the recordsets at will, and would not need to physically store the data, > neither as SubTable1 or as its children _a or _b. > > I guess the essence of my question is why is it necessary to build a > physical table, when you have a virtual table? > > d lewis > ------------------------------ > > Message: 2 > Date: Wed, 19 May 2010 16:18:48 -0400 > From: jwcolby <jwcolby at colbyconsulting.com> > Subject: [dba-SQLServer] dynamic sql > To: Access Developers discussion and problem solving > <accessd at databaseadvisors.com>, Sqlserver-Dba > <dba-sqlserver at databaseadvisors.com> > Message-ID: <4BF447A8.2050004 at colbyconsulting.com> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Guys, > > I use dynamic SQL a lot. I have received feedback from others on these > groups that "there is never > a valid reason for dynamic SQL", there is always a safer "better" way to do > this. > > I would like to lay out what I do and get some feedback on exactly how I > would do what I am doing > without using dynamic SQL. > > A couple of examples. > > I build an entire database for an order for my client. Each database has > some fairly specific > stuff, as well as data for that order, which I may need to use again for > another order. IOW, I > select X records, send a small subset of those records off to a client, > then months down the road > may need to send another subset of those records. > > Each order is based on selection criteria, and that criteria is different > for each order. So I > build a table on-the-fly. I have a common set of fields, then I have the > selection fields. I then > pull all of the data from a view into that custom table. > > So I have a view. I use code to get the names of the fields in that view > and I build a table which > has exactly the same fields as the view contains, using dynamic SQL. I use > the view to then append > the data into the table. Since the table has the same fields as the view, > it basically just appends > right in. Using dynamic SQL I am able to build any select query I need and > build a table which > exactly matches that view. > > Another example. I have many different databases which contain millions of > name / address / > demographic fields. For example I have a table of 20 million people who > have children, with fields > for whether they children in specific age brackets. I have another > database with 12 million records > of people who have cats and / or dogs. Another where... > > Each of these databases has a name / address component (fields) which are > standardized (by me) so > that the name / address field names are identical. I then have stored > procedures which build name / > address tables, append the name / address components out of the central > table into a temp table in > sorted order, grab just 2 million record chunks, and BCP the records out to > files on disk for > address validation. There is a similar set of stored procedures where I > pull the resulting > validated address fields back in to SQL Server into the same database and > perform other processing > inside of the database. > > Because I have standardized the field names, I can use dynamic SQL in > stored procedures to pass in > the name of the database and a table or view name for the export piece and > the import piece as well. > There are probably 5-10 stored procedures which perform the export process > as well as a like > number which performs the import. > > In fact I used to have "hard coded" stored procedures in each database > which did this stuff, but > maintenance was a nightmare. If I needed to change anything I had to go > into each database and > modify the stored procedures on a case by case basis. I now have a > "master" database where I store > my stored procedures and I can simply pass in the parameters to the stored > procedure to tell it what > database / view to use. One place to edit, one place to go to make > modifications. > > So that gives an overview of some of the ways that I use dynamic SQL. I am > looking for the "Why I > shouldn't" and what my alternatives are, or comments on when and why > dynamic SQL is useful (as I > find it useful) and how to go about making the resulting dynamic SQL > safe(r) to use. > > Thanks, > -- > John W. Colby > www.ColbyConsulting.com > > > ------------------------------ > > > The contents of this e-mail message and its attachments are covered by the > Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended > solely for the addressee(s) hereof. If you are not the named recipient, or > the employee or agent responsible for delivering the message to the intended > recipient, or if this message has been addressed to you in error, you are > directed not to read, disclose, reproduce, distribute, disseminate or > otherwise use this transmission. If you have received this communication in > error, please notify us immediately by return e-mail or by telephone, > 530-893-3520, and delete and/or destroy all copies of the message > immediately. > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >