September 3, 2003 at 12:03 pm
We are designing a database that will primarily store a ton of transactions into roughly ten different tables that are all linked via foreign keys to one main table. The foreign key is a GUID.
The data in question is coming from an IVR system (Interactive Voice R-something or other)and consists of records of buttons pushed in a phone call.
We are trying to decide on how to handle the inserts and have the following ideas:
1 - a seperate stored procedure called by the application to insert a record in each table. The pain about this one is that we have to pass the GUID from the main table back to the application for the inserts to the other tables and rely on the application to do the inserts in the correct order.
2 - One stored procedure which is passed the contents of an entire phone call. The procedure would take each tables record as a delimited list, parse it and do the inserts in the proper order.
3 - Have a SP that takes the entire phone record and dumps it into a staging table with columns corresponding to the tables in which they will be inserted. Then have a scheduled job kick off what is essentially two. It reads a record, parses and inserts it to the other tables and deletes it fromt he staging table. This should allow for a larger load against the server. When it's busy the staging table will just grow larger and the processing will be able to catch up when it's slow.
I am concerned at the server being able to handle the load as seven IVR servers will be feeding a single database server 24/7. Get this... The number of calls per year is expected to be something like 30 Million That works out to be an average of phone call per second.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
September 3, 2003 at 12:08 pm
I would take a stab at the staging table. We currently run a process that receives over 1 million entries per day. The database is able to keep up if you have the luxury of dealing with the input in sets of like entries. If you are dealing with them one at a time, I would propose you mark entries for deletion and have a process kick of periodically and delete them in sets from the staging table.
Guarddata-
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply