David at sierranevada.com
Thu Mar 27 11:15:27 CDT 2008
OK, I think I understand this a little better, but I get the feeling that copying the result set to a new DATABASE is overkill -- why not just to a new table within the current db? Creating databases is a fairly drastic solution, in my opinion. There is a lot of overhead to them, all sorts of views, procedures, roles, etc associated, even when they are not explicitly used. I think it will unnecessarily bog down your server. Tables are much easier to manage. Just my opinion. D. Lewis ------------------------------ Message: 5 Date: Thu, 27 Mar 2008 11:54:19 -0400 From: "jwcolby" <jwcolby at colbyconsulting.com> Subject: Re: [dba-SQLServer] dba-SQLServer Digest, Vol 61, Issue 38 To: "'Discussion concerning MS SQL Server'" <dba-sqlserver at databaseadvisors.com> Message-ID: <000601c89022$d1f2d7a0$0201a8c0 at M90> Content-Type: text/plain; charset="us-ascii" David, There are several reasons for saving the results. First, I take the addresses and run them through Address validation to find anyone who has moved. Once I do that, there are now people in the original list that are no longer in the zips specified. So I have to get rid of anyone not in the zips specified. Second, the lists potentially contain "duplicate" records. Often times the same person answered questions in several different questionnaires and thus got their name in the db multiple times but with completely different data fields filled in. Also, there may be several members of the same household. The client wants ONE piece of mail per address, so I have to DEDUPE the result set. And finally the client asks for lets say 100K records out of 2.4 million. He wants me to MARK those records as shipped so that he can come back next month and ask for another 100K records. His clients do "test runs" to see what the response is. If the response is good, they come back for more, but each time they come back they want new records. In fact, if the time between orders is long enough, I have to go do the Address Validation, remove moved people thing all over again. I could in fact store just the PKs of the records shipped to handle that part, but I have found a date field to be a good flag of shipped and also tells me WHEN those records were shipped. It is for these reasons that I copy a template each time I fill a NEW order. I store the WHERE views that do the data selection, I do the deduping, and I store flags of records shipped, all inside of the new copy of the template. I name the database with the order name and I can always get back to it. I take the order databases offline after a few weeks (to keep the database list less busy), but can always find them again if the client comes back asking for more addresses. I am in fact trying to build a "business" database that stores all of MY business stuff having to so with this. The clients the order goes to, the order specifications, the directories and databases that the stuff is stored in, how long it took to do stuff (for billing) and all that. However that is the "little database" that I mentioned as an aside, not the one that I get the data from to fill the orders. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David Lewis Sent: Thursday, March 27, 2008 11:02 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] dba-SQLServer Digest, Vol 61, Issue 38 Hi John: A few thoughts unrelated to the copy database issue: I'm not sure I follow everything you do, but it sounds like you receive raw data, scrub it, then use it as a basis of reports (run queries, send the results to the client...). It also sounds like you physically save the recordset results of those queries, and that the underlying base table does not change. If that is the case, why not save only the queries you run, rather than the rows they return? Create the queries as either views, or better yet build views and call them from stored procedures that can accept where clauses, topN parameters, etc.? That approach would mean you don't have to manage tables, or entire databases, and instead only manage views and stored procedures. That, by the way, is a use of sql server that is more in line with how it was designed. One approach might be to have some base views, and a table that might look like: tblOrders Customer RequestDate BaseView WhereClause TopClause Etc. Some sample rows might be: Abc Company 3/1/08 Column1, Column2 FROM dbo.vwSomeBaseView WHERE BLAH = 'Blah' AND BLAHBLAH= ' BlahB%' Top 3000 Abc Company 3/12/08 Column1, Column2, Column3 FROM dbo.vwSomeOtherBaseView WHERE Humbug = 'Blah' AND Yada= ' BlahB%' Top 10000 Using this approach, you only need to build a smaller number of views, you keep a record of who asked for what, when, and you can recreate the actual query very simply. A stored procedure to do that might look like: CREATE PROCEDURE usp_GetResultSet @Customer VARCHAR(50) , at Date DATETIME AS DECLARE @strSQL VARCHAR(500) DECLARE @Top VARCHAR(50) SELECT @strSQL= 'SELECT ' + ISNULL(TopClause,'') + BaseView + WhereClause FROM tblOrders WHERE Customer=@Customer AND RequestDate=@Date PRINT @strSQL --just for debugging... EXEC(@strSQL) Calling this procedure as usp_GetResultSet 'Abc Company','3/12/08' would give you: SELECT Top 10000 Column1, Column2, Column3 FROM dbo.vwSomeOtherBaseView WHERE Humbug = 'Blah' AND Yada= ' BlahB%' Voila, no messy storing of new tables, etc. I dunno, maybe that helps keep things tidier? 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.
More information about the dba-SQLServer mailing list