One of the design issues we deal with is how long it takes to put invoices in the system.  First, let me lay out the scenario. 

Suppose your company processes 1000 orders with an average of 3 lines each a day. What Solomon will do is process 1000 invoices that day for each one of the orders. Additionally, you will receive or process payments for at least the same volume of transactions that day. That means you will have an additional 1000 account receivable transactions, 1000 additional tax AR transactions, 1000 additional freight transactions and according to the elaborate data collected in GAAP-compliant data, you will also have incoming AR and outgoing AR transactions in that table. So from 1000 orders you will generate about 12,000 AR transactions.   

What if you want to build a query that goes into that table structure and "gives" you an invoice, with freight and tax included, from tables that are not indexed by invoice numbers but by a multi-key index that includes Company ID, AR Batch Number, AR Doc ID, and AR Batch Sequence Number and a join to seven other tables to get all the related tax, misc charges, and freight information. 

Hopefully you can grasp how complex this is going to get... 

Currently there is not a good solution for this issue. At some large customers we have added indexes to Solomon tables to improve the performance of the invoices. This has helped in a big way—the SQL tuning wizard will tell you that there is a 50 % improvement. Just for the record, we have spent many days with both Plumbline and Scribe reviewing the design of the query and seeing how we could improve it.

The basic problem is a SQL/Solomon one, not a Scribe one. We have to organize the query so it returns the line items on an invoice that fit into a CRM invoice. That is because Solomon does not organize the AR transaction table by invoice line items on the invoice, it is organized by AR transaction batches. This means, from the invoice line item view point, they occur in a seemingly unnatural order in the table. Currently 28% of the cost for the query comes from trying to find the invoice line items in the AR transaction table. Because this table is not organized or indexed in such a way to easily pick out the specific AR transactions that we need for invoices, the best we are going to get is to add indexes on the tables in question. 

So splitting the Header creation in one DTS and the Line item creation in another DTS is not going to make the process more efficient. It will, in-fact, make the process for getting invoices in more complex and cause more SQL overhead.

We are currently looking at using SQL views, and CRM Iframes that allow you to look at AR history and transaction history in real time, and not using DTS’s to move the data back and forth. We think that is our most promising route.