David Lewis
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.