I'm new at this DTS job thing, so I'm hoping someone can help me out. Here's a diagram with the explanation of the procedure below it.
Current DB Design contains a BillingDetail table that has an individual record for each bill sent and each bill paid based on a combined key of CustID and TraceCode. Our user wants us to combine these individual record lines into one line reading with a sum(sent) and a sum(paid) as two separate fields, but on the same line. The Trace Code for a sent & a paid bill can be the same, hence the keys that I am combining the sums on.
Since there are other records in BillingDetail that have nothing to do with bills paid and bills, I pulled each “set” of info into a separate view (see diagram) then did a left outer join from Sent to Paid (there will always be sent bills but not always paid bills) to recombine them into a new Bill Sum view.
First question, is this the most efficient way to combine these bill records?
My next task is to pull in data from a DISB view which contains records of vendors paid based on what the customers have paid. VDISB’s TraceCode is based on the same TraceCode that was used for the CustID Bill Sent and BillPaid records. Again, the view is a summary of detail records from DisbDetail which can have multiple vendors/payments. Our user only wants the total paid out that is connected to a Customer paying a certain bill. I did a left outer join from Bill Sum view to DISB view to combine the records into one record with all fields listed as view Trans.
Now I have to pull Bill Sum, DISB and Trans into a new database as actual tables. I currently am using a DTS job to drop and recreate these tables in the new DB, but I have just learned that there are going to be potentially millions of records that will be transferred to the new DB, which makes this method time & resource consuming at best.
In order to append records to these tables rather than drop & recreate these tables, I’m going to have to do checks on the existing records in BillSum table to update the paid amounts and dates (since payments are always made after bills are sent) as well as do an insert on the records that don’t exist. I’ll have to do the same thing on the Disb table for four fields in it. Then, I have to do it all over again for the Trans table.
This leads to my second question. Obviously, the drop & recreate DTS job is not the best answer, even though it is the simplest. Does anyone have any suggestions on how I can handle this better?
I’m wondering if I can divide the job up into separate jobs (it only has to happen once a day). Do the BillSum table transfer as Job1, then do the Disb table transfer as Job2 and then do the Trans table as Job3. Or maybe I should create stored procedures to do the update and schedule them? The major problem I’m running into is this is going to be happening on a production server (after I test it extensively in a lab, of course) that runs 24/7 and has no real downtime. Things slow down in the evening & overnight, but users are still always connecting. I need a method of doing this that uses the least resources possible while still doing everything correctly.
Can anyone give me suggestions? I would be eternally grateful. Or if you feel my current method is valid, just let me know. Thanks!
Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!
Code:
|BillingDetail |
|has Multi-records w/Combined Key|
/ \
/ \
|BillsSent view| |BillsPaid View|
\ L.O. Join /
\ /
|Bill SUM View| |DISB View|
\ /
\ L.O. Join /
\ /
| Trans View |Current DB Design contains a BillingDetail table that has an individual record for each bill sent and each bill paid based on a combined key of CustID and TraceCode. Our user wants us to combine these individual record lines into one line reading with a sum(sent) and a sum(paid) as two separate fields, but on the same line. The Trace Code for a sent & a paid bill can be the same, hence the keys that I am combining the sums on.
Since there are other records in BillingDetail that have nothing to do with bills paid and bills, I pulled each “set” of info into a separate view (see diagram) then did a left outer join from Sent to Paid (there will always be sent bills but not always paid bills) to recombine them into a new Bill Sum view.
First question, is this the most efficient way to combine these bill records?
My next task is to pull in data from a DISB view which contains records of vendors paid based on what the customers have paid. VDISB’s TraceCode is based on the same TraceCode that was used for the CustID Bill Sent and BillPaid records. Again, the view is a summary of detail records from DisbDetail which can have multiple vendors/payments. Our user only wants the total paid out that is connected to a Customer paying a certain bill. I did a left outer join from Bill Sum view to DISB view to combine the records into one record with all fields listed as view Trans.
Now I have to pull Bill Sum, DISB and Trans into a new database as actual tables. I currently am using a DTS job to drop and recreate these tables in the new DB, but I have just learned that there are going to be potentially millions of records that will be transferred to the new DB, which makes this method time & resource consuming at best.
In order to append records to these tables rather than drop & recreate these tables, I’m going to have to do checks on the existing records in BillSum table to update the paid amounts and dates (since payments are always made after bills are sent) as well as do an insert on the records that don’t exist. I’ll have to do the same thing on the Disb table for four fields in it. Then, I have to do it all over again for the Trans table.
This leads to my second question. Obviously, the drop & recreate DTS job is not the best answer, even though it is the simplest. Does anyone have any suggestions on how I can handle this better?
I’m wondering if I can divide the job up into separate jobs (it only has to happen once a day). Do the BillSum table transfer as Job1, then do the Disb table transfer as Job2 and then do the Trans table as Job3. Or maybe I should create stored procedures to do the update and schedule them? The major problem I’m running into is this is going to be happening on a production server (after I test it extensively in a lab, of course) that runs 24/7 and has no real downtime. Things slow down in the evening & overnight, but users are still always connecting. I need a method of doing this that uses the least resources possible while still doing everything correctly.
Can anyone give me suggestions? I would be eternally grateful. Or if you feel my current method is valid, just let me know. Thanks!
Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!